Need help with an SQL badge query


(Carey B) #1

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.

Thanks so much.


(Carey B) #2

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?


(Jeff Atwood) #3

It is a feature, ask @sam about it.


[PAID] Allow badges to be granted in restricted categories
(Carey B) #4

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