19

I think there should be a single place to view all your badges from the whole Stack Exchange network.

For example, if I want to find out:

  • which gold badges I have in total across all sites*
  • which sites I have achieved badge X on (or sites I haven't achieved it on, maybe, if it's a common one)
  • my total number of badges

or anything like that, at the moment I have to view my profiles on every site individually to find this out. For example, "do I have badge X on Meta?" "do I have it on StackOverflow?" "do I have it on ELU?" etc. etc. I can't be the only one who would be interested in seeing my network badges all in one place.

To be clear, I'm not looking for the numbers I have on each site, but the specific badges viewable in a single place, similar to what you can see on an individual site.

The network profile seems like a great place to do it:

suggestion

Let me know if this already exists and if so I'll retag from to .

*For me personally it's currently none, but I don't think that invalidates my point.

  • Something like this perhaps? stackexchange.com/users/3803973/starsplusplus?tab=accounts – Robert Longson Apr 29 '15 at 10:06
  • No, that just gives the hard numbers. (1g/4s/16b etc.) Admittedly it does invalidate my first bullet point, which was poorly worded. Now edited. – starsplusplus Apr 29 '15 at 10:08
  • @ShadowWizard You didn't like my pun? :( It's always a good idea to say why you're editing. – starsplusplus Apr 29 '15 at 10:32
  • What pun, @starsplusplus? It was totally wrong and misleading so I corrected it. Feel free to rollback and I'll close as "Unclear what you're asking". – Shadow The Princess Wizard Apr 29 '15 at 10:33
  • Anyway, what you want is possible via SEDE - cross network query is possible, I'll contact someone who can pull this off. :) – Shadow The Princess Wizard Apr 29 '15 at 10:34
  • @ShadowWizard Could you be more specific about how it was "totally wrong and misleading"? I'm talking about viewing badges together abstracted from context. That's pretty much the definition of meta, no? I can see how that could be confused with "meta sites" or "Meta Stack Exchange", but that's why I clarified in the first paragraph. I think saying it was "wrong" is a bit of a stretch. – starsplusplus Apr 29 '15 at 10:37
  • The word "meta" is already in use and means either a per-site child meta, or this site aka MSE. The term "Meta badges" means "badges on meta" and you can't just say "hey it means something else" out of the blue - it's highly confusing. – Shadow The Princess Wizard Apr 29 '15 at 10:38
  • @ShadowWizard The word "meta" is already in use as part of the English language, and has been around far longer than StackExchange. I would agree with you if "meta badges" were an established phrase, but it isn't. By your logic, I could never ask for programming help if I had a stack overflow. – starsplusplus Apr 29 '15 at 10:44
  • 2
    Like I said, feel free to rollback. I won't go into edit war on this one, but might start new feature request with clear wording since I do like the idea. For the record, I'm not against fun or pun - using them myself sometimes - but not when it conflicts with existing terms and/or becoming just confusing to most readers. – Shadow The Princess Wizard Apr 29 '15 at 10:45
  • @ShadowWizard Nah, I'm not contesting the edit, just the way you worded your reasons :P I've made another edit; I think it's clearer than what either of us wrote the first time. – starsplusplus Apr 29 '15 at 10:49
  • Yup - well done and let's hope something would be done. :) – Shadow The Princess Wizard Apr 29 '15 at 10:50
11

This query will use the data available in SEDE to return the complete list of all your badges networkwide.

The SEDE Query

To run it you need your 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 3803973, mine is 281857.

-- 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 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_badges ( site nvarchar(250)
                         , id int
                         , name nvarchar(40)
                         , [date] datetime    
                         , [class] int
                         , tagbased bit
                         , userid 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
               insert into #all_badges 
               select @url
                   , b.id
                   , b.name
                   , b.date
                   , b.class
                   , b.tagbased
                   , b.userid
              from badges b
              inner join users u on u.id = b.userid
              where u.accountid = '+ cast(@accountid as nvarchar) + ';'
    exec (@sql)
   fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;

/* this is generated
*/
create table #b (dbid int, tagname nvarchar(50), link nvarchar(500));
set nocount on
insert into #b values(80,'Altruist','222/altruist');
insert into #b values(80,'Analytical','1306/analytical');
/* skipped 21710 lines */    
insert into #b values(288,'Constable','85/constable');
insert into #b values(288,'Sheriff','86/sheriff');
insert into #b values(288,'Illuminator','92/illuminator');
set nocount off   

select replace(site,'.StackExchange','') as [site]
     ,
     case when #b.link is not null then 
     'http://' 
     + site 
     + '.com/'
     + 'help/badges/'
     + #b.link
     + '?userid='
     + cast(userid as nvarchar)
     + '|'+
     name else
     name 
     end as [badge name or badge tag]
     , [date]
     , case 
       when class = 1 then 'Gold'
       when class = 2 then 'Slver'
       when class = 3 then 'Bronze'
       end as Class
     , case
       when tagbased = 1 then 'tag'
       else null
       end
from #all_badges
left outer join #b on #b.dbid = #all_badges.dbid and
                 tagname = name
order by replace(site,'.StackExchange','') asc
, [date] desc

drop table #all_badges

Output

In the output is included columns for the site, the badge link (if available), date the badge is awarded and an indicator if it is a tag badge. The result for me looks like this:

badges on network

Badge link

The url to a badge on a site looks like this:

http://anime.stackexchange.com/help/badges/9/autobiographer?userid=12801

If you go looking for that 9 in SEDE you won't find it. I opened a feature request to see if it could be added. It was suggested by Shog9 to try the API and that is what I did, without abandoning this query completely. On to version 2 that needs...

JavaScript to get an insert script

The following javascript uses the StackAPI to fetch all NAMED badges for all sites. If you would include the Tag badges as well a server will die. Notice that the script uses a dictionary to translate a sitename to a database id. This is needed to keep the insert script on a reasonable size. My first attempts failed dramatically due to the time the server needed to insert and allocate space for all the data. I had to exclude the Japanese and rusian sites because they broke...my script, the API and/or SEDE, I simply gave up.

var dbs = [
    /* generated with SEDE
       http://data.stackexchange.com/stackoverflow/query/309683/javascript-array-with-databases-that-an-account-has?accountid=281857&opt.textResults=true
    */
//{ site: 'meta.ru.stackoverflow', dbid: 6},
//{ site: 'ru.stackoverflow', dbid: 7},
{ site: 'meta.woodworking', dbid: 8},
{ site: 'musicfans', dbid: 9},
{ site: 'meta.engineering', dbid: 10},
];

(function () {
    "use strict";

    var api = 'https://api.stackexchange.com',
        version = '2.2',
        key = 'bring your own key',
        bo = 1,
        calls = [],
        running = false,
        handleAll,
        remaining = 300,
        error_id;

    function safeApiGet(url, cb) {
        var call;
        if (url !== undefined && cb !== undefined && error_id === undefined) {
            calls.push({ url: url, callback: cb });
        }
        if (!running && remaining > 0 && error_id === undefined && calls.length > 0) {
            call = calls.shift();
                    running = true;
            window.setTimeout(function () {
                $.get(call.url + '&key=' + key, function (data) {
                    error_id = data.error_id;
                    if (error_id !== undefined) {
                        console.log('%d %s', error_id, data.error_message);
                        // clear the queue, prepare to stop
                        calls = [];
                    }
                    bo = data.backoff || 0.01;
                    remaining = data.quota_remaining;
                    call.callback(data);
                }).done(function () {running = false; });
            }, bo * 1000);
        }
    }

    function findDatabaseId(site) {
        var id = 0,
            i;
        for (i = 0; i < dbs.length; i = i + 1) {
            if (dbs[i].site === site) {
                id = dbs[i].dbid;
            }
        }
        if (id === 0) {
            console.log('-- %s not in dbs', site);
        }
        return id;
    }

    function handleSite(site, dbid) {
        safeApiGet(api + '/2.2/badges/name?pagesize=100&order=desc&sort=rank&site=' + site + '&filter=!*JxbBNqy.P75laP_', function (badges) {
            var j,
                pos,
                link,
                search = '/badges/';
            for (j = 0; j < badges.items.length; j = j + 1) {
                link = badges.items[j].link;
                pos = link.indexOf(search);
                link = link.substr(pos + search.length);
                console.log("insert into #b values(%d,'%s','%s');", dbid, badges.items[j].name, link);
            }
        });
    }

    function handleSites(sites) {
        var i,
            site,
            dbid;
        for (i = 0; i < sites.items.length; i = i + 1) {
            site = sites.items[i].api_site_parameter;
            console.log('-- %s', site);
            dbid = findDatabaseId(site);
            if (dbid > 0) {
                handleSite(site, dbid);
            }
        }
    }

    safeApiGet(api + '/2.2/sites?pagesize=500&filter=!6P-nEDMV8tZyf', handleSites);
    handleAll = window.setInterval(function () {
        if (calls.length > 0 && remaining > 1) {
            safeApiGet();
        } else {
            window.clearInterval(handleAll);
            if (calls.length > 0) {
                console.log('%d remaining urls', calls.length);
            }
        }
    }, 2000);
}());

If you run this in your Chrome Dev Console you can save the log that consists of over 21000 insert statements.

From there it is easy. Include the statements in the script. Join the all_badges and b table on database_id and badgename and you'll get just enough info to construct a badge url that brings you on the specific badge for you on that site.

Final

Don't run the javascript without requesting a key. Replace the 'bring your own key' string with the key found when you registered the app. This is to guarantee you have more api-quota.

If the site list changes (sites added) the badges script needs to be rerun. I noticed that some sites don't link their named badge. That is due to a mismatch between database name and api_site_parameter name. I call it an annoyance and leave it for others to fix.

Keep in mind that the data in SEDE is only update once a week, on Monday. So your most recent badges might not be on this list. For those you can fallback to Monica's answer.

3

Partial answer only:

This doesn't give you the grouping you get on a per-site profile (e.g. Nice Answer x 37 or whatever), and it's limited to the last 100 (not ideal), but there's this:

screen shot from network profile

Network profile -> activity -> badges.

  • It also gives only most recent 100 badges. – user259867 Apr 29 '15 at 17:24
  • @2mkgz oh, hmm -- that's not ideal. Thanks; I hadn't noticed. – Monica Cellio Apr 29 '15 at 17:25
  • Better than nothing though, nice! :) – Shadow The Princess Wizard Apr 29 '15 at 18:53
1

I've taken rene's query, and adapted it so that it reports only the tag badges - here's the query.

As usual, you'll need your stackexchange user ID. Go to Stackexchange.com, click on your username in the top bar, and you'll find your user ID in the address bar, which will look something like:

http://stackexchange.com/users/339125/energynumbers

That number (in my case 339125), between "users" and your username, is your user ID.

And here's a typical output: enter image description here

You must log in to answer this question.

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