Using Reactions to grant badges

Data & reporting
1

That sounds like an interesting way of achieving this, I would be interested in learning more if you ever want to share more details!

3 Likes
2

Of course. :slight_smile:

Basically, in its simplest form, you choose a Reaction which indicates ‘I think this deserves a badge’ which your members can use to nominate posts, and the query picks up which posts have picked up that Reaction. You can then either grant the badge manually from the list the query gives you, bulk grant if you fully trust the list, or even set up the badge’s custom SQL for the system to do it automagically. :magic_wand:

You can also apply some extra criteria to the query to make it more targeted. For instance, ‘a post needs x number of this Reaction’, or ‘only that Reaction given by TL2+ counts’

It also works with custom emojis as well if there wasn’t a current one that you thought was suitable.

2 Likes
3

Thanks! I searched around a bit but couldn’t find a query that quite matches this, would you be willing to share the query? I’ve never created one myself but feel I need to learn this!

4

I have one that I was playing with, though I put it to one side to look at it with fresh eyes and haven’t got back to it yet, so it may need some work. It’s to award a badge based on getting at least 10 posts with the :chefs_kiss: reaction given by a member of the ‘data-team’ group for posts in the data & reporting category:

SELECT p.user_id, MIN(p.created_at) granted_at
FROM badge_posts p
  JOIN topic_tags tt ON tt.topic_id = p.topic_id
WHERE tt.tag_id = 615
  AND EXISTS (
         SELECT 1
            FROM discourse_reactions_reactions dr 
             JOIN discourse_reactions_reaction_users dru ON dr.id = dru.reaction_id
             JOIN group_users gu ON gu.user_id = dru.user_id
            WHERE dr.post_id = p.id
              AND dr.reaction_value = 'chefs_kiss'
              AND gu.group_id = 175
       )
 GROUP BY p.user_id
HAVING COUNT(*) >= 10

Though I wasn’t pleased with using the MIN as it gave the wrong awarded date.

There’s a lot of criteria to it, and it’s automatic, which I think pushes it to the more complicated end. I think a simpler one could be much more achievable without too much effort.

1 Like
5

Thanks that helped! This should probably be moved to a new topic since we’re derailing. But here’s the query I came up with (with the help of ChatGPT) that simply returns the users who gave/received the reaction, how many reactions, the date of reaction, and the url to the specific post. The only thing I couldn’t seem to do is make the URL to the post clickable, it seems that’s limited to topic URLs.

WITH reaction_pairs AS (
    SELECT 
      p.user_id AS received_reaction_user_id, 
      dru.user_id AS gave_reaction_user_id, 
      dr.post_id,
      t.id AS topic_id,
      t.slug AS topic_slug,
      p.post_number,
      dru.created_at AS reaction_date
    FROM 
      discourse_reactions_reactions dr
      JOIN discourse_reactions_reaction_users dru ON dr.id = dru.reaction_id
      JOIN posts p ON dr.post_id = p.id
      JOIN topics t ON p.topic_id = t.id
    WHERE 
      dr.reaction_value = 'Your_Specific_Reaction_Here'
)
SELECT 
  ur1.id AS user_id, 
  ur2.username AS "User Who Gave Reaction",
  'Your_Community_URL' || 't/' || rp.topic_slug || '/' || rp.topic_id || '/' || rp.post_number AS "Post URL",
  rp.reaction_date AS "Reaction Date",
  COUNT(*) AS "Number of Reactions"
FROM 
  reaction_pairs rp
  JOIN users ur1 ON rp.received_reaction_user_id = ur1.id
  JOIN users ur2 ON rp.gave_reaction_user_id = ur2.id
GROUP BY 
  ur1.id, 
  ur2.username, 
  rp.topic_slug, 
  rp.topic_id, 
  rp.post_number, 
  rp.reaction_date
ORDER BY 
  "Number of Reactions" DESC, "Reaction Date" DESC
2 Likes
6

To get this bit to do its magic you append _url to the column name, so:

'Your_Community_URL' || 't/' || rp.topic_slug || '/' || rp.topic_id || '/' || rp.post_number AS post_url
4 Likes