Hello discourse’s gorues, I need your help with a new badge.
- Grant a badge if user has created at least one topic in the “foo” category AND already posted 1000 post anywhere. Note that this will (should) only count posts that are public (not in categories that are restricted or private messages) or otherwise unrestricted (checkbox on edit category ignoring badges).
So I took two ideas, but I do not know how to merge them
I take the idea from this “generic version” from a Badge for 1000 posts:
SELECT user_id, 0 post_id, current_timestamp granted_at FROM badge_posts WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) ) GROUP BY user_id HAVING count(*) > 1000
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 WHERE category_id = ( SELECT id FROM categories WHERE name ilike 'foo' ) AND p.post_number = 1 GROUP BY p.user_id
Extra comment, this badge does not targets posts.
I hope you can help me