What is the bug reporter badge?

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.

22 Likes

Just to clarify how this works for more lay-people (please correct me if I’m wrong):

If a user in the group named discourse likes the first post in a topic in category bug then the badge is issued.

Correct?

3 Likes

yes

3 Likes

Is there a way to make this work in categories that aren’t publicly visible?

1 Like

You would need to select from posts instead of badge_posts and basically reimplement the filters that view gives to avoid data leaks.

3 Likes

Could you please post this query? :slight_smile:
Can I select more teams? Like ‘discourse’ or ‘admins’ or ‘dev_team’ :smile:
Last question, I can’t use markdown into Badge Description, how do you have created the link to discourse team?

1 Like

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

1 Like

did you read the first post :arrow_up:

2 Likes

It works like a charm already used on my forum

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.

1 Like

Just add HAVING count(*) > 9 to the end. HAVING is like WHERE, but applies after the GROUP BY.

4 Likes

Wouldn’t that count posts that have been liked multiple times too often, since post_actions are part of the JOIN?

1 Like

I tried to build my own version:

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>

Does anyone see any issue with that?

I’m trying to produce a variation too, but I’m concerned with replies that are liked, not the base post. Did you ever figure yours out?

2 Likes

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:

2 Likes

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

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.

1 Like

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.

1 Like

Hello , thank you for information

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).

4 Likes

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