19

Today I came across a situation where I knew that I'd answered another similar question some time ago, but couldn't remember on which Stack Exchange site it was. I ended up doing multiple searches on several different Stack Exchange sites before giving up and just doing the research again - answering the question from scratch.

As such, I wonder if it would be possible to implement the user: me syntax on the Stack Exchange search mechanism.

If this had already been implemented then it would have given me a single place to try out the different sets of keywords that I thought might help me find my answer.

Note, this question goes one step further than How can I list all my questions and answers across all Stack Exchange sites? and I can see that there might be implementation issues with it due to issues raised here, but couldn't the OpenID for the Stack Exchange account be used to filter the questions and answers from other Stack Exchange sites?

2 Answers 2

9

It's possible (if you don't mind the results to be a bit stale, they're updated every Sunday morning) with a SEDE query. You'll need your network account ID, which you can find in the URL shown in your address bar when you visit https://stackexchange.com/users/current. For example, when I search for my own posts (account ID 6085540) and the word 'company', these are the results:

enter image description here

Of course, Google's search and Stack Exchange own (Elastic)Search are a bit smarter, as they can correct spelling errors, and search for plurals and multiple keywords.

For reference, here is the complete query (feel free to fork it if you want to play around):

-- result table, don't rename and keep the site column
create table #results (site nvarchar(250), type nvarchar(10), 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 + '''
     , ''Answer'', a.id, q.title, a.creationdate, a.score
from ' + quotename(name) + '.dbo.posts as a
inner join ' + quotename(name) + '.dbo.posts as q on a.parentid = q.id
inner join ' + quotename(name) + '.dbo.users as u on a.owneruserid = u.id
where u.accountid = ' + CAST(##AccountId:int## AS nvarchar) + '
  and a.body LIKE ''%' + ##keyword:string## + '%'''
  +
'union' +
N'
select ''' + name + '''
     , ''Question'', q.id, q.title, q.creationdate, q.score
from ' + quotename(name) + '.dbo.posts as q
inner join ' + quotename(name) + '.dbo.users as u on q.owneruserid = u.id
where u.accountid = ' + CAST(##AccountId:int## AS nvarchar) + '
  and q.posttypeid = 1
  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: https://stackexchange.com/users/current to see your own ID.

-- execute it
exec (@sql)

-- show results
select
       -- URL builder from Lamak http://stackoverflow.com/a/38129701
      'http://' 
      + case PARSENAME(site,1)
        when 'audio' then 'avp'
        else PARSENAME(site,1)
        end 
      + coalesce('.' 
        + case PARSENAME(site,2)
          when 'audio' then 'avp'
          else PARSENAME(site,2)
          end , '')
      + IIF(patindex('%.MathOverflow%',site) > 0
      , ''
      , coalesce('.' + PARSENAME(site,3), '')
      )
      + IIF(patindex('%.MathOverflow%',site) > 0
      , '.net'
      , '.com'
      ) 
      + (CASE type WHEN 'Answer' THEN '/a/' ELSE '/q/' END)
      + CAST(id AS nvarchar)
      + '|'
      + title,
      type,
      creationdate,
      score
from #results
order by score desc

drop table #results
1
3

I don't believe so. The Stack Exchange network-wide search uses the Google Custom Search engine. It's a standard web search based on what Google sees. I do not believe they have that type of access to filter out results based on the post author.

To get around that problem, I typically type in my search followed by my username, which is always embedded in the text (as part of the flair).

You must log in to answer this question.

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