New badge, grant if post in category AND posted x posts anywhere


#1

Hello discourse’s gorues, I need your help with a new badge.

I need:

  • 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 :frowning:

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

And “Grant a badge if user has created at least one topic in the “foo” category”:

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 :thumbsup: