I’m looking for something similar, a badge awarded when replying to posts in a particular category.
SELECT p.user_id user_id, min(p.id) post_id, min(p.created_at) granted_at FROM badge_posts p JOIN topics t ON p.topic_id = t.id WHERE t.category_id = (SELECT id FROM categories WHERE slug = 'introductions') GROUP BY p.user_id
Crikey, if this is what I think it is, how easy is it to adapt to specify the number of posts for silver/gold badges?
SELECT p.user_id user_id, min(p.id) post_id, min(p.created_at) granted_at FROM badge_posts p JOIN topics t ON p.topic_id = t.id WHERE t.category_id = (SELECT id FROM categories WHERE slug = 'introductions') GROUP BY p.user_id HAVING COUNT (*) > 10
Would it be that simple?
Turns out it is. Awesome, thanks @riking!
Update: throws an error on any category name which isn’t unique.
ObOT: I note that
<ins> has been CSS’d on here, but not
It’s just terrible contrast vs the quote.
Hey guys, there are any chance to have a badge for a suspended user? (and keep that badge after the suspended period expired)
“Mark of Cain” ?
I would strongly recommend against such a badge and in fact not work on writing it for you, but it is very doable.
Badges are meant to be a positive thing, if you start using them for negative stuff you send some really weird messages out there
That’s true. The thing is that we have a community where, in more than a year we have exactly two suspended users. So they are in very exclusive club, and when we joked about this, this idea came up: why we don’t give them a badge?
If it’s that rare, and if you really want to do it, simply assign them manually at /admin/users/USERNAME/badges.
I’ll attest to the fact that ‘negative’ badges aren’t really a good idea on most forums, having created a few myself and seen the fallout of one of them…
So you would have no problem if you were awarded a “made an unpopular suggestion” badge here at meta?
I vote it’s called ‘king of bad ideas’, can be awarded more than once, and I get at least two.
If you want your query to not be dependent on system field labels such as
user_field_2, you can do the following:
SELECT cf.user_id user_id, cf.updated_at granted_at FROM user_custom_fields cf INNER JOIN user_fields uf ON uf.id = (0 || regexp_replace(cf.name, 'user_field_', ''))::integer AND uf.name = 'FriendlyFieldName' WHERE LENGTH(cf.value) > 0
It seems like you’d want to check for a length value greater than zero, just in case you have some single-digit UID GitHub graybeards. And if you are granting a badge multiple times, you’ll want to add
GROUP BY cf.user_id to be safe.
We have a dev, a staging, and a live site, and the custom user fields we manually add don’t always have the same user_field_n values from site to site.
I want to create a badge for those who help in the forums, so I want to give it to the people who have replied X times to ANY thread inside the category ‘Help’. Of course this has to avoid replies in your own topic.
Please any help would be great! I have no idea how to deal with this kind of databases (I’m new to discourse )
I need something similar. For now I use this code, trigger daily:
And this is the problem:
Users that start topics inside particular category must be excluded from count.
We have a little number of users that write a lot of posts in a single topic only to reply a single question
I’m interested into it as well, how to avoid such problem?
Hi ! Sorry if my question is silly but just to verify, is this how to highlight the top poster of the month? I really need that functionality on our site to reward the top poster with a sub to our channel… Thank you!!
SELECT user_id, granted_at FROM user_badges WHERE badge_id = 3FROM LastMonth, TotalUsers WHERE row_number = 1
Don’t remember who share SQL for “top poster”, sorry. This is the code shared somewhere here on Meta (I saved it in a topic on my forum)
Take a look here:
WITH LastMonth AS ( /* Count eligible posts from last month */ SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*) FROM badge_posts bp JOIN users u on u.id=bp.user_id WHERE bp.created_at > CURRENT_DATE - INTERVAL '1 month' GROUP BY u.username, bp.user_id HAVING count(*) > 1 ORDER BY count(*) DESC, bp.user_id ), TotalUsers AS ( /* Total relevant users that have posted in the last month */ SELECT max(row_number) from LastMonth ) SELECT username, user_id, row_number, (row_number*100/TotalUsers.max), count, CURRENT_DATE granted_at FROM LastMonth, TotalUsers WHERE row_number >= TotalUsers.max *.10 AND /* 10% - change this*/ row_number < TotalUsers.max *.25 /* 25% - change this */
Saw your code, may I ask if do you want a ‘top poster’ badge only for TL3?
Sorry to clarify, i’m really really new at this, I just want to know what code I should paste to show 1 member with the most new site-wide posts of the month. As a bonus I’d love to EXCLUDE members of a certain trust level.
I’d greatly appreciate any help.
I think you’d just add a line with
LIMIT 1 at the end of something like the Top X% posters query above. Maybe I’m missing something though…