What is this bug reporter badge?

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

3 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

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')
)
1 Like

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?

1 Like

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
11 Likes

I was just awarded the Tester badge, but I only have 2 Bug Reporter badges. The text of the Tester badge says this:

grafik

Reported 10 bugs that were liked by the Discourse team

I just checked: One of my bug reports has 10 :heart: likes, but not even those are exclusively by Discourse Team members.

Something seems to be wrong with the badge… :thinking:

2 Likes

There are several old bugs that were deleted as not of future historical interest, but the total isn’t 10.

2 Likes

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

7 Likes

@sam where should i run this code to have this badge assign, and should we create a category for bug report ?

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

This badge is specific to meta.

Ahhh, we cant use it in discourse ?

1 Like

Yes, we can. But category and group names must be changed to valid ones.

2 Likes

Understood, like hiding the category trick, but here i didn’t recogniz where to run sam code; from admin panel or inside container rails c ?

1 Like

You might not be finding where you can add the code because editing badge SQL editing is now disabled by default. See here:

So I think the process would be to:

  1. Enable badge SQL editing (instructions in the linked topic)
  2. Create a new badge in Admin > Badges (or edit an existing badge if you’ve already created it)
  3. Add your SQL to the badge and save
  4. Disable badge SQL editing

Then if you haven’t already done so, going by the values in the SQL you posted:

  1. Create a category called “bug”
  2. Create a user group called “team”
  3. Add the users that you want to be able to accept bug reports to “team”
3 Likes