Bernstein seeks help modifying an SQL badge query to require new topics rather than just posts and to include subcategories (7, 8, 9) under category 6. They also encounter an issue where badges aren’t awarded for posts in categories restricted by trust levels. codinghorror clarifies this is a feature, and simon explains the issue stems from using the badge_posts table, which excludes posts from protected categories. Switching to the posts table resolves the problem, as confirmed by Bernstein.
I’m not well-versed in these queries but I have been using this (and others with different post requirements) query I got from the Discourse meta site to reward posting to a particular category.
SELECT P.user_id, MIN(P.created_at) granted_at, MAX(P.id) post_id
FROM badge_posts P JOIN topics T ON T.id = P.topic_id
WHERE T.category_id IN (SELECT id FROM categories WHERE id = 56 OR parent_category_id = 6)
AND P.user_id >= 0 GROUP BY P.user_id HAVING COUNT(*) >= 1
I was wondering how exactly I could change this to:
A) Require new TOPICS, not just posts in the category and
B) How I could include subcategories. So not just category 6, but also subcategories 7,8, and 9.
I finally realized why this code wasn’t working. I can’t award badges to categories that are limited to certain trust levels. I have a category that is only accessible/visible to trust level 2+
Badges won’t work for that group. Is that a bug or feature? Is there any way around it?
@sam, is there any way around this? The category is a corner of my forum designed to be earned by “leveling” up your trust level and contributing. I don’t mind if people who can’t access can see the badges earned from it. It’s meant to be something to work toward.