Search all my favorites of all my accounts

Question

Seems to be a rather simple thing, but I was unable to find it:

I know that months ago, I did favorite a post about rsnapshot/rsync, yet I am unable to find it easily. I am aware of infavorites:mine, however, that one only works (as it seems) within the currently "active" account/Stack Exchange website.

I want to search the favorited questions of all my Stack Exchange accounts for tags/phrases etc.

Is there any way to do this? (Rather than changing accounts and executing n search queries for n accounts?)

Answer 1

With this SEDE query I found four possible candidates:

• Mirror rsnapshot backup directory (Stack Overflow)
• Proper way to set up rsnapshot over ssh (Unix Stack Exchange)
• Backing up rsnapshot directory with rsync copies entires backup directory (Server Fault)
• Btrfs copy snapshot with rsync (Super User)

Please keep in mind that SEDE is updated only once a week, on Sunday morning. For reference, here is the complete query:

-- result table, don't rename and keep the site column
create table #results (site nvarchar(250), id int, title nvarchar(250),
  creationdate datetime, score int);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + ''', q.id, q.title, q.creationdate, q.score
from ' + quotename(name) + '.dbo.posts as q
inner join ' + quotename(name) + '.dbo.votes as v on v.postid = q.id
  and v.votetypeid = 5 -- Favorite
inner join ' + quotename(name) + '.dbo.users as u on v.userid = u.id
where u.accountid = ' + CAST(##AccountId:int## AS nvarchar) + '
  and q.body LIKE ''%' + ##keyword:string## + '%'''
from sys.databases
where database_id > 5

-- AccountId: The global account ID of the user, which you can find in the URL of their network profile. Visit this link: http://stackexchange.com/users/current to see your own ID, or use the AutoFiller: https://stackapps.com/questions/8136/sede-user-id-autofiller

-- execute it
exec (@sql)

-- show results
select site,
  LOWER('https://' +
    IIF(PATINDEX('%.Mathoverflow%', site) > 0,
    IIF(PATINDEX('%.Meta', site) > 0, 'meta.mathoverflow.net', 'mathoverflow.net'),
      IIF(PATINDEX('%.Ubuntu%', site) > 0,
      IIF(PATINDEX('%.Meta', site) > 0, 'meta.askubuntu.com', 'askubuntu.com'),
        IIF(PATINDEX('StackExchange.%', site) > 0,
          CASE SUBSTRING(site, 15, 200)
          WHEN 'Audio' THEN 'video'
          WHEN 'Audio.Meta' THEN 'video.meta'
          WHEN 'Beer' THEN 'alcohol'
          WHEN 'Beer.Meta' THEN 'alcohol.meta'
          WHEN 'CogSci' THEN 'psychology'
          WHEN 'CogSci.Meta' THEN 'psychology.meta'
          WHEN 'Garage' THEN 'mechanics'
          WHEN 'Garage.Meta' THEN 'mechanics.meta'
          WHEN 'Moderators' THEN 'communitybuilding'
          WHEN 'Moderators.Meta' THEN 'communitybuilding.meta'
          WHEN 'Photography' THEN 'photo'
          WHEN 'Photography.Meta' THEN 'photo.meta'
          WHEN 'Programmers' THEN 'softwareengineering'
          WHEN 'Programmers.Meta' THEN 'softwareengineering.meta'
          WHEN 'Vegetarian' THEN 'vegetarianism'
          WHEN 'Vegetarian.Meta' THEN 'vegetarianism.meta'
          WHEN 'Writers' THEN 'writing'
          WHEN 'Writers.Meta' THEN 'writing.meta'
          ELSE SUBSTRING(site, 15, 200)
          END + '.stackexchange.com',
          IIF(PATINDEX('StackOverflow.%', site) > 0,
            CASE SUBSTRING(site, 15, 200)
            WHEN 'Br' THEN 'pt'
            WHEN 'Br.Meta' THEN 'pt.meta'
            ELSE SUBSTRING(site, 15, 200)
            END + '.stackoverflow.com',
            IIF(PATINDEX('%.Meta', site) > 0,
              'meta.' + SUBSTRING(site, 0, PATINDEX('%.Meta', site)) + '.com',
              site + '.com'
            )
          )
        )
      )
      )) + '/q/' + CAST(id AS nvarchar) + '|' + title,
      creationdate,
      score
from #results
order by score desc

drop table #results