Receive a badge for a post into a topic containing specific tag

I’d like to assign a badge when my members reply to a topic with the “welcoming” tag
How can I do this?

That’s exactly what I want to do too.

Where are tags in the database?

I’ve poked in data explorer and can’t find “tag” in it anywhere. badge-query-request-received-x-likes-in-topic-containing-specific-tag asks the same question, but the answer worked another way.

Why badges for tags?

Others say that badges for posting is ripe for abuse, but I have students create a topic with a certain tag to indicate that they have completed an assignment. I want to create a badge so that they can see that they have “turned in” the work, and then another to indicate that it was “good” (presumably with a “like” until I get a you-did-it-right plugin written).

Since tags are in the discourse-tagging plugin, its data is stored in the X_custom_fields tables.

Badge for topic in CATEGORY-NAME with tag TAG-NAME

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 topic_custom_fields tcf on t.id = tcf.topic_id
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'CATEGORY-NAME'
) AND p.post_number = 1
AND tcf.name LIKE 'tags' and tcf.value LIKE 'TAG-NAME'
and (:backfill OR ( p.id IN (:post_ids) ))
GROUP BY p.user_id

How to get it to run on the posts that are already there.

  1. Visit https://HOSTNAME/sidekiq/scheduler

  2. Find Jobs::BadgeGrant

  3. Click trigger button

P.S. How to select across multiple categories?

without error more than one row returned? Like this:

...
WHERE category_id = ANY (
  SELECT id FROM categories WHERE name ilike '%completions%'
...
2 Likes

Just a note that since Tagging support is now part of discourse, the way to create a badge for a post with a given tag has changed.

The code now should look like this:

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 topic_tags tt on t.id = tt.topic_id
JOIN tags on tags.id = tt.tag_id
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'CATEGORY-NAME'
) AND p.post_number = 1
AND tags.name LIKE 'TAG-NAME'
and (:backfill OR ( p.id IN (:post_ids) ))
GROUP BY p.user_id

Changing all of your Badge Queries is left as an exercise to the admin. :slight_smile:

(But I’ve got dozens of badges to change, so I’ll probably have a script that will fix at least mine Real Soon Now. If you’re interested, let me know and I’ll endeavor to make it useful for others.)

4 Likes

If anyone’s interested, I’ve got a Python script that will get all of the badges, look for “tcf.value” in the query and replace the tcf.value queries with those using the new tables.

Side note that this query will only return the latest post that qualifies. If you have a badge that can be awarded multiple times, use the following version:

SELECT 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 topic_tags tt on t.id = tt.topic_id
JOIN tags on tags.id = tt.tag_id
AND tags.name LIKE 'featured'
and (:backfill OR ( p.id IN (:post_ids) ))
AND p.post_number = 1
1 Like