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

Product Support
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

Pasted image401×356 17.1 KB

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%'
...