23

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?

0

2 Answers 2

5

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.

9
  • Using this method, can I sort oldest (on top) to newest (on bottom)? Commented Dec 27, 2016 at 18:33
  • Only if you bring in creationdate from the per site query to the #results table @void.pointer
    – rene
    Commented Dec 27, 2016 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? Commented Dec 27, 2016 at 18:41
  • @void.pointer the id found here? You need your networkprofile id
    – rene
    Commented Dec 27, 2016 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.
    – Stevoisiak
    Commented May 31, 2017 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
    Commented Jun 1, 2017 at 6:34
  • The first line of your script has a typo. ---- accountid: Account on stackexchnage.com
    – Stevoisiak
    Commented Jun 22, 2017 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
    Commented Jun 22, 2017 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/)
    – Stevoisiak
    Commented Sep 28, 2017 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)
8
  • failing a site-side feature this is great. accepting untested. does this include meta sites?
    – lofidevops
    Commented Feb 24, 2014 at 8:16
  • 1
    Yes, it does get meta sites.
    – kuzzooroo
    Commented Feb 24, 2014 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
    Commented Feb 27, 2014 at 14:52
  • @kuzzooroo You should add that last comment to the answer.
    – gerrit
    Commented Feb 27, 2014 at 22:56
  • @gerrit, I added a comment at the top of the code. Is that what you had in mind?
    – kuzzooroo
    Commented Feb 28, 2014 at 2:12
  • @kuzzooroo Good.
    – gerrit
    Commented Feb 28, 2014 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? Commented Apr 13, 2014 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
    Commented Jun 30, 2015 at 15:57

You must log in to answer this question.

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