You may or may have not noticed this new badge: https://meta.discourse.org/badges/114/bug-reporter 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 :bug: Thank you all for reporting bugs so diligently.

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 :wink:

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 '[[: :]]' ) AND p.post_number = 1 GROUP BY p.user_id Anyone got any ideas? I’ve double checked and there are definitely threads in which the OP has been liked by a Radiant_Worlds_Staff member - http://forum.uk.skysaga.com/t/quests-traders-etc-havent-reset-after-1am/7028/3 for example has been liked by me and I am in that group.

Noob Question :confounded: How would I extend this to apply to all categories within and including a parent category? I was able to match a single category by setting this line to SELECT id FROM categories WHERE name ilike '%developers%' or SELECT id FROM categories WHERE name ilike ‘%oauth%’ but couldn’t extend it with alternation ie. '%oauth%|%developers%' I’m also worried that my monkeying around might create something really inefficient.

Hello , thank you for information

[image] john_mardlin: SELECT id FROM categories WHERE name ilike ‘%oauth%’ but couldn’t extend it with alternation ie. ‘%oauth%|%developers%’ You want something like: SELECT id FROM categories WHERE name ilike '%oauth%' OR name ilike '%developers%' If you’ve got the exact category names, you could also use: SELECT id FROM categories WHERE name IN ('OAuth', 'Developers') …but note that the names must match exactly (including case).

Thanks very much for this @simonk Unfortunately both are giving me this: ERROR: more than one row returned by a subquery used as an expression

Oh, sorry, I didn’t look at the context the query was being used in. You’ve currently got something like this: WHERE category_id = ( SELECT id FROM categories WHERE name IN ('OAuth', 'Developers') ) Since the inner query is returning multiple rows, you need to change the outer WHERE clause to: WHERE category_id IN ( SELECT id FROM categories WHERE name IN ('OAuth', 'Developers') )

Success, thanks! Would this be too much to run whenever a user acts on a post? Could it easily be modified to award a badge when a post is ‘marked as a solution’ rather than liked? edit: Actually, that’s giving me: ‘Contract violation: Query triggers on posts, but does not reference the ‘:post_ids’ array’ I can probably live with update daily though. ¯\_(ツ)_/¯

@sam, we’re looking on implementing a similar badge over at Stonehearth. Can you share the current SQL used, as yours is 2+ years old, and there are numerous different versions in this topic?

Bug Reporter SELECT distinct p.user_id, p.created_at granted_at, 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 'team' ) ) WHERE category_id = ( SELECT id FROM categories WHERE name ilike 'bug' ) AND p.post_number = 1 Tech Support SELECT id user_id, current_timestamp granted_at FROM users WHERE id IN ( SELECT p1.user_id FROM post_custom_fields pc JOIN badge_posts p1 ON p1.id = pc.post_id JOIN topics t1 ON p1.topic_id = t1.id WHERE p1.user_id <> t1.user_id AND name = 'is_accepted_answer' AND p1.user_id IN ( SELECT user_id FROM posts WHERE :backfill OR p1.id IN (:post_ids) ) GROUP BY p1.user_id HAVING COUNT(*) > 9 ) Tester SELECT p.user_id, MIN(p.created_at) granted_at FROM posts p JOIN topics t ON t.id = p.topic_id WHERE t.category_id = (SELECT id FROM categories WHERE name ILIKE 'bug') AND p.post_number = 1 AND EXISTS ( SELECT 1 FROM post_actions pa WHERE pa.post_id = p.id AND pa.post_action_type_id = (SELECT id FROM post_action_types WHERE name_key = 'like') AND pa.user_id IN (SELECT user_id FROM group_users WHERE group_id = (SELECT id FROM groups WHERE name ILIKE 'team')) ) GROUP BY p.user_id HAVING COUNT(*) >= 10 Senior Tester SELECT p.user_id, MIN(p.created_at) granted_at FROM posts p JOIN topics t ON t.id = p.topic_id WHERE t.category_id = (SELECT id FROM categories WHERE name ILIKE 'bug') AND p.post_number = 1 AND EXISTS ( SELECT 1 FROM post_actions pa WHERE pa.post_id = p.id AND pa.post_action_type_id = (SELECT id FROM post_action_types WHERE name_key = 'like') AND pa.user_id IN (SELECT user_id FROM group_users WHERE group_id = (SELECT id FROM groups WHERE name ILIKE 'team')) ) GROUP BY p.user_id HAVING COUNT(*) >= 20

What is the bug reporter badge?

Community Building Data & reporting

zogstrip (Régis HANOL) June 11, 2018, 10:23am 27

Thanks, I fixed the queries and updated @sam’s post.

Topic		Replies	Views
Badge for posts with Likes from a specific group Data & reporting sql-triggered-badge	22	1146	August 27, 2023
Change system for Bug Reporter badge? Site feedback badges , sql-triggered-badge	12	547	February 24, 2026
Badges for specific category Feature	1	1324	June 13, 2018
The default badge queries Data & reporting reference , sql-triggered-badge	1	1124	August 27, 2023
Badges for posting Data & reporting sql-triggered-badge	4	577	August 27, 2024

What is the bug reporter badge?

Related topics