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

Support
1

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

2

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

4

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%'
...
3 Likes
5

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

5 Likes
6

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.

8

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