3

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?)

3

With this SEDE query I found four possible candidates:

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
  • This is great! And would have taken me hours to figure out the proper query (compared to the time it would have taken me to execute n queries against all accounts). Thanks a ton! I did find the post I was looking for :) – quaylar Dec 29 '18 at 14:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .