Wah this is perfect! Thanks PJH! Are there any resources I could look at to create more badges?
Not that I’ve found - everything I’ve done has been through trial and error, what passes for SQL that I’ve picked up over the years and mucking around with an offline restored-backup of the database in phppgadmin. And a bit of guesswork.
As a result I’ve created quite a few badges with fairly hairy queries on our site -
- “Made X posts. But not in certain topics”
- “Spent X consecutive days on the forum”
- “Top X% poster in the last month. Except certain topics”
- The above one to allow easier awarding of ‘manual’ badges.
Currently contemplating “Has had a post replied to X times” but that one’s proving problematic depending on how you query the database.
How about a badge awarded to posts that contain attachments? What sql query would accomplish that?
I run a game development community and showcasing your game is always a great way to harvest Likes. As far as grand total of Likes go, spreading your updates out is probably the way to go, but the Badge system won’t take much notice of you unless you save up for bigger updates (especially as long as the default threshold is 10).
Here’s a guy who’s getting a decent amount of attention for his good work, but no badges.
What about a default badge with a query along these lines:
30 likes accumulated over the span of 15 days
Could be one-time-award to keep things simple. Else one would have to do a reset of the count once it’s awarded (while possibly still counting on the old “score” as well in case there’s a 60-likes badge as well).
Topic Participant Extraordinaire
total of 30 likes spread over posts in the same topic
I read this post just today for the first time. Fantastic repo of custom badges for increase engaging and encourage gamification
##Badges for reading!
SELECT pt.user_id, count(*) c, CURRENT_DATE as granted_at FROM post_timings pt WHERE :backfill GROUP BY pt.user_id HAVING count(*) >= pow(3, 9) AND count(*) < pow(3, 10) /* Change the 9 and 10 (x and x+1) for the different badges */ ORDER BY c DESC
I highly recommend not running this query more frequently than daily - our forum’s only been running for 10 months and the table referenced here has 11,949,028 rows making this query rather time intensive (6 seconds per badge - most of our badge queries are sub-second.)
SELECT count(*), r.username Liked, r.id user_id, current_timestamp granted_at FROM post_actions pa INNER JOIN posts p on p.id=pa.post_id /* Get post details */ INNER JOIN users r on r.id=p.user_id /* The user who made the post that was liked */ WHERE pa.post_action_type_id=2 /* The user who liked the post */ AND pa.created_at > CURRENT_DATE - INTERVAL '15 DAYS' /* Change to suit */ GROUP BY Liked, r.id HAVING count(*) > 30 /* Change to suit */ ORDER BY count(*) DESC
I’d recommend running that once a day (certainly not every post edit/creation) - it takes 2 seconds to run on my backup instance, though I’m sure the query could be tweaked to make it quicker.
In which case you’ll want:
SELECT count(*), r.username Liked, r.id user_id, current_timestamp granted_at, p.topic_id FROM post_actions pa INNER JOIN posts p on p.id=pa.post_id INNER JOIN users r on r.id=p.user_id WHERE pa.post_action_type_id=2 GROUP BY Liked, r.id, p.topic_id HAVING count(*) > 30 ORDER BY count(*) DESC
10 seconds. Certainly no more frequently than once a day.
Depends on what about those posts you’re after. Basically a post with an attachment is generally one with a picture in, so something like:
SELECT user_id FROM posts WHERE raw ilike '%<img src="%"'
would probably be a good start.
Im planning to open a forum where there could be some parallel trade and promotion of it in other platforms.
Are there any kinf of “trust” badges? Like pro trader, or reliable vendor…
If there are not, maybe i could open a category for trading profiles, where anyone would have a selfthread where people could leave feedback, and give likes to the OP presentation post, and give badges if likes for that post > x.
You can create your own and award them manually.
Or you could probably use groups for this (/admin/groups/custom) which - at a guess - would be more useful for what you’re after.
Groups would allow you to create a category where group members could create topics, yet still allow non-group members to reply to existing topics (but not create their own.) Is that the sort of thing you’re thinking of?
Has anyone come up with any badges based on some value for custom user fields? If so, how can we get at those custom user fields with a SQL query?
Here’s a sample badge (there is surely better SQL to do this!) that we quickly hacked together to look for people who filled out an optional custom user field, in this example, to list their GitHub User ID:
SELECT cf.user_id user_id, cf.updated_at granted_at FROM user_custom_fields cf WHERE cf.name like 'user_field_2' AND LENGTH(cf.value) > 1
Note that the value of name field is actually user_field_1, user_field_2, etc., and not the name that you’ve assigned in the admin section.
Awesome badge. Thank you for sharing this!
I don’t know too much about database structure to create badge sql. I would like to create following badges which is not present in discourse (i think not only i interested in such badges). Please help me out to create following badges:
- user that made more than 100 comments in general
- user that made more than 100 comments in particular category
- user that made more than 100 comments with given at least 5 likes
- user that gave ‘likes’ more than 100 times in general
- user that received more than 100 ‘likes’ in general
- user that received more than 100 ‘likes’ in particular category
- user that created more than 50 topics in general
- user that created more than 50 topics in particular category
- badge for 5 most active users in particular category for a month
- badge for 5 most active users in general for a month
- badge for 5 most active users for particular trust level
- user that shared github gist and received like on comment
- user that wrote some code blocks in comment and received like on comment
I would gladly appreciate any help on any mentioned items. Many thanks,
I this - thanks for sharing! Works for me.
One thing I noticed: it’s not immediately apparent on the custom user field which user_field_n goes which which user field and it doesn’t start at 1! I found it by looking at the source of that page.
Do you know how to modify this query to display it only when a user has another badge already? I’d like to display a “Tech Ambassador” badge on my site to people but only to those users who have self-identified themselves as such and have also provided a bio.
I think you’d simply add the following:
AND EXISTS ( SELECT 1 FROM user_badges ib WHERE cf.user_id = ib.user_id AND ib.badge_id = 888 )
Where 888 is the badge ID number of the other prerequisite badge. See the following for details:
Well the name given should be in a table somewhere, though joining on it to create a dynamically generated field name may not make the query more self documenting (if that’s even possible in postgres)…
And you’ll have to be careful when editing (specifically adding and removing other) fields of course.
Agreed. I’m hopeful to find an answer related to that here:
How can you have obtained this?
Sure I’d already posted this query, but… this is the one for the top 10%-25% (I have comparable queries for 5%-10%(silver), and top 5%(gold) and top poster (again gold but CSS’d to be a brown spoon)).
Here’s a short version that most sites could probably use:
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 */
Our site has people who like gaming the system so, so our actual query (presented below) has a few extra clauses that
- Exclude particular topics (over and above those excluded normally from
badge_posts) such as topics created solely for the purpose of encouraging excessive posting
- Excludes particular ‘users’ (actually 'bots - they don’t get this badge)
- Excludes particularly short topics:
WITH exclusions AS ( /* Which other topics to exclude from counters */ SELECT user_id, id, topic_id, post_number FROM posts WHERE raw LIKE '%c09fa970-5a9a-11e4-8ed6-0800200c9a66%' AND user_id IN ( SELECT gu.user_id FROM group_users gu WHERE group_id IN( SELECT g.id FROM groups g WHERE g.name IN ('admins') ) ) ), 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 AND bp.user_id NOT IN ( /* ignore bots */ SELECT gu.user_id FROM group_users gu WHERE group_id IN( SELECT g.id FROM groups g WHERE g.name IN ('bots') ) ) WHERE topic_id NOT IN ( /* exclude short topics */ SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10 ) AND topic_id NOT IN ( /* Ineligible topics */ SELECT topic_id FROM exclusions ) AND 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 ( 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 *.1 AND row_number < TotalUsers.max *.25