Enabling Badge SQL entails security and performance risks so it is not available by default.
For more information, see: Enable Badge SQL
Here are some common badge queries which you can use to create your own awesome badge!
Grant a badge if user has created at least one topic in the “foo”* category
*(“foo” is the category name, not the category slug)
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
Grant a badge if user has created 5 topics in the “foo”* category
*(“foo” is the category name, not the category slug)
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
HAVING count(*) >= 5
Grant a badge if user has replied to at least one topic
SELECT
DISTINCT ON (p.user_id)
p.user_id, min(p.created_at) granted_at, p.id post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
GROUP BY p.user_id, p.id
ORDER BY p.user_id, p.id
Grant a badge if user has replied to 10 topics
SELECT
DISTINCT ON (p.user_id)
p.user_id, min(p.created_at) granted_at, p.id post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
GROUP BY p.user_id, p.id
HAVING count(*) >= 10
ORDER BY p.user_id, p.id
Grant a badge if user has replied to 10 topics in a specific category (add the category ID)
SELECT
DISTINCT ON (p.user_id)
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 t.category_id = ... -- [! Add the category ID]
GROUP BY p.user_id
HAVING count(*) > 9
If you want to add more categories, use this line instead:
WHERE t.category_id IN (13, 14, 15, 17, 18, 20, 21) -- [! Use your category ID numbers instead]
Grant a badge if user has created 500 posts
SELECT user_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(*) >= 500
Grant a badge to all the members of the group “foobar”
SELECT user_id, created_at granted_at
FROM group_users
WHERE group_id = (
SELECT g.id FROM groups g WHERE g.name = 'foobar'
)
Grant a badge to the first 100 users of the forum
SELECT id AS user_id, created_at AS granted_at
FROM users WHERE id > 0
ORDER BY created_at ASC LIMIT 100
Grant a badge for users who have already earned more than n
badges
SELECT user_id, current_timestamp granted_at
FROM user_badges
GROUP BY user_id
HAVING count(*) >= ... -- [! Add the number of badges]