This discussion centers around the creation and customization of the “Bug Reporter” badge on Discourse, a SQL-triggered badge that awards users for reporting bugs that receive likes from designated team members (e.g., ‘discourse’ or ‘team’ groups). sam originally shared the badge query, which uses the badge_posts table and checks for likes from specific groups in the ‘bug’ category. Users like downey and alefattorini sought clarifications and enhancements, such as extending the badge to multiple groups or non-public categories. riking and others offered SQL improvements, including using HAVING COUNT(*) for silver/gold tiers like “Tester” and “Senior Tester”. fefrei shared an alternative query targeting specific like counts, while john_mardlin asked about applying the badge to parent categories, resolved by simonk using IN clauses. sam later posted updated queries for “Bug Reporter”, “Tech Support”, “Tester”, and “Senior Tester” badges. Issues arose around badge logic (e.g., Stefan_Fairphone receiving “Tester” with only 2 bugs), prompting zogstrip to fix the queries. New users like f1r4s asked where to run the SQL, with Simon_Manning explaining that badge SQL editing must be enabled in Admin > Badges, and categories/groups must be created. amira.bedhiafi proposed a more readable CTE-based version of the query. The badge system is extensible and requires no plugins, though SQL editing is disabled by default for security.
What I find super exciting about it is that there are zero plugins needed, I crafted it using our badges UI. Not only does it grant the badge daily, it will also grant it after one of the team “likes” a bug.
It runs the following badge query:
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
JOIN post_actions pa ON pa.post_id = p.id AND
post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND
pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'discourse' )
)
WHERE category_id = (
SELECT id FROM categories WHERE name ilike 'bug'
) AND p.post_number = 1
GROUP BY p.user_id
The UI is still a tiny bit rough, but I find it super duper cool that the badge system is this extensible. Will knock up a silver version of this badge tomorrow and call it tester
Could you please post this query?
Can I select more teams? Like ‘discourse’ or ‘admins’ or ‘dev_team’
Last question, I can’t use markdown into Badge Description, how do you have created the link to discourse team?
Let me resurrect this thread again: @sam, could you post the query, please? This is interesting for a lot of different use cases, and my SQL has gotten quite rusty
Yes, I did! I was referring to the silver version (requiring a specific number of likes), although my reply for some reason isn’t tagged as a reply to that specific post. Sorry for the confusion.
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
SELECT id FROM categories WHERE name ilike '<CATEGORY_NAME>'
) AND (
SELECT count(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike '<TEAM_NAME>' )
)
) >= <LIKE_COUNT>
AND p.post_number = 1
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST_COUNT>
My untested hypothesis is that the only thing here that is specific to base posts is this:
AND p.post_number = 1
Depending on what you want, deleting this clause or making this p.post_number > 1 should work. Again, this is untested, so please be sure to run a plausibility test and report back
I’m trying to use this but getting no badges to be assigned when I run it
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
JOIN post_actions pa ON pa.post_id = p.id AND
post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND
pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'Radiant%' )
)
WHERE category_id = (
SELECT id FROM categories WHERE name ilike '[[:<:]]bugs[[:>:]]'
) AND p.post_number = 1
GROUP BY p.user_id
