21

I mean to get back them, really I do. So, how can I view all my unaccepted questions across the Stack Exchange network?

Searching for user:me hasaccepted:0 works for a single site, but the stackexchange.com search doesn't work the same way (it searches for the text). Is there any way to do this?

4

You can use SEDE to achieve what you want by leveraging a cross-database query script. The following query does that for user Nate Kerkhofs.

The query requires an accountid and that one can be found by visiting your network account on StackExchange.com and then take the id from the url. Yours is 439169, mine is 281857 and that of user void.pointer is 52844

---- accountid: Account on stackexchange.com ! "In the url on stackexchange.com"

-- create url from dbname
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL 
    DROP PROCEDURE #siteurl;
GO

-- create url based on dbname
create procedure #siteurl
   @dbname nvarchar(250),
   @res nvarchar(250) OUT
   as
   begin
      declare @pos integer
      declare @rec nvarchar(250)
      set @res = @dbname
      set @pos = CHARINDEX('.', @dbname)
      if (@pos > 0 ) 
      begin
         set @rec = substring(@dbname, @pos+1, len(@dbname))
         exec #siteurl @rec,  @rec output
         set @res = @rec 
                  + '.' 
                  + substring(@dbname, 0, @pos) 
      end
   end;
go

declare @accountid int = ##accountid:int##  -- 93484

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE

declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_questions ( site nvarchar(250)
                            , title nvarchar(250)
                            , qid int);

open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
    set @sql = N'use '+ QUOTENAME(@db_c_name) +';
declare @url nvarchar(250)
exec #siteurl ''' + @db_c_name  + ''', @url output
declare @userid int = null
select @userid = id 
from users where accountid =  '+ cast(@accountid as nvarchar) + '
if @userid is not null 
begin
  insert into #all_questions
  select @url
       , title
       , id
  from posts p
  where owneruserid = @userid
  and p.posttypeid = 1 --Q
  and answercount>0
  and acceptedAnswerid is null
end;'

    exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

select replace(site,'.StackExchange','') as [site]
     , 'http://' 
     + site 
     + '.com/'
     + 'q'
     + '/'
     + cast(qid as nvarchar)
     + '|'
     + title
from #all_questions
order by 1 asc

drop table #all_questions

The outcome of the query loos like this:

nate kerkhofs unaccepted questions

Keep in mind that SEDE is only refreshed once a week, on sunday.

  • Using this method, can I sort oldest (on top) to newest (on bottom)? – void.pointer Dec 27 '16 at 18:33
  • Only if you bring in creationdate from the per site query to the #results table @void.pointer – rene Dec 27 '16 at 18:38
  • I put in my user ID and I don't get posts that I own in the results. I'm grabbing the number in the URI seen on the profile page. Is this correct? – void.pointer Dec 27 '16 at 18:41
  • @void.pointer the id found here? You need your networkprofile id – rene Dec 27 '16 at 19:35
  • Is it possible to make a version that automatically uses the user ID for whatever user is currently running the search? Similar to how you can include user:me in a search. – Steven M. Vascellaro May 31 '17 at 21:58
  • 1
    @StevenVascellaro that used to work with ##UserID:int## but a change in the emailhash rendered that functionality useless for most if not all users: see meta.stackexchange.com/questions/209605/… so I never tried it since. – rene Jun 1 '17 at 6:34
  • The first line of your script has a typo. ---- accountid: Account on stackexchnage.com – Steven M. Vascellaro Jun 22 '17 at 15:59
  • @StevenVascellaro all my scripts have that typo so I can easily find plagiarism ... or maybe I find that a plausible explanation for my screw up. Anyhow, now fixed. – rene Jun 22 '17 at 16:05
  • The query returns an incorrect domain for questions on sites that have a unique URL name. (i.e. script returns http://stackoverflow.meta.com/ instead of http://meta.stackoverflow.com/) – Steven M. Vascellaro Sep 28 '17 at 15:57
2

Here's some Python code that will run the query on each Stack Exchange site on which you have an account. For me it opens the search on each site in a separate tab (not a separate window), though that may depend on your browser's configuration. You'll need to plug your own username and id into http://stackexchange.com/users/3370439/kuzzooroo?tab=accounts. You can change the days=60 code if you want to change how far back in time this looks. Hat tip to @psubsee2003's answer on my near-duplicate question for constructing a good model query string.

# This code requires the Beautiful Soup library, which doesn't come with Python.
# Depending on your setup you'll need to run something like `pip install beautifulsoup4`
# to get the library.
import datetime
import re
import webbrowser
import urllib2
from bs4 import BeautifulSoup
from urlparse import urlparse

today = datetime.datetime.today().date()
old_day = today - datetime.timedelta(days=60)
date_str = "{}..{}".format(old_day, today)

soup = BeautifulSoup(urllib2.urlopen("http://stackexchange.com/users/3370439/kuzzooroo?tab=accounts"))
sites = set()
for tag_a in soup("a"):
    pr = urlparse(tag_a["href"])
    if re.match("/users/[0-9]", pr.path) and '' != pr.netloc:
        sites.add(pr.netloc)
for site in sites:
    url = "http://{}/search?q=user%3Ame+hasaccepted%3Ano+answers%3A1+lastactive%3A{}+".format(site, date_str)
    webbrowser.open(url)
  • failing a site-side feature this is great. accepting untested. does this include meta sites? – david.libremone Feb 24 '14 at 8:16
  • 1
    Yes, it does get meta sites. – kuzzooroo Feb 24 '14 at 12:44
  • 1
    BTW, this code requires the Beautiful Soup library, which doesn't come with Python. Depending on your setup you'll need to run something like pip install beautifulsoup4 to get the library. – kuzzooroo Feb 27 '14 at 14:52
  • @kuzzooroo You should add that last comment to the answer. – gerrit Feb 27 '14 at 22:56
  • @gerrit, I added a comment at the top of the code. Is that what you had in mind? – kuzzooroo Feb 28 '14 at 2:12
  • @kuzzooroo Good. – gerrit Feb 28 '14 at 4:42
  • I would not expect the average user on every site to have the technical skill to implement this answer. (Consider cooking.SE). May I ask that people throw their support behind this feature request here? – Travis Bemrose Apr 13 '14 at 23:29
  • I agree with @TravisBemrose in that a python snippet is not really easy to implement. I have worked with Python before, and even then, getting it to work is not simple: installing Python, changing your path, turning this into a snippet, finding and inserting your user ID... It's better than nothing, but it's far from usable. – Nzall Jun 30 '15 at 15:57

You must log in to answer this question.

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