What cool badge queries have you come up with?

I used this query…

DISTINCT ON (p.user_id)
p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 6671 AND -- 6671 is the "Introduce yourself" topic
  (:backfill OR p.id IN (:post_ids) )

With the following settings


Here’s a badge query for someone who replies to a post in a category (id=19). The post_number > 1 makes sure that it’s a reply, not the first post.

p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = 19 AND p.post_number > 1
AND (:backfill OR p.id IN (:post_ids) )

Hi, is this still the way to add custom badges? I can’t see a place to add SQL code when I’m creating one. thanks!

badge sql is disabled via site settings out-of-the-box cause often people make very bad sql choices.


Ok, so if i would use some of the SQL ones offered here, would it still be possible? Have you considered adding an easier way to add badges, like a “if this then that” option? if a user gets more than 20 likes in 1 day, award this badge. Just an idea :slight_smile:

If you are self hosted you can re-enable the SQL queries using rails console, see


Should be enjoying Christmas!

This code give every user who logs in between ‘Day of Year’ = 358 and 2 days into the new year a badge. Word it as you like!


SELECT distinct(user_id), CURRENT_DATE as granted_at  FROM user_visits WHERE
date_part('doy', visited_at) >= 358
date_part('doy', visited_at) <= 2

What I’d like it to do is award the badge every day they visit between the two dates. Help please!


I think if you add another field like the “visited_at” it woks.

Because you’re using just user_id and current_timestamp, and they are distinct just in user_id, because current_timestamp would be the same every row.
If you add visited_at they could be distinct, and them one badge for every day.

But maybe you already solve this :wink:

Edit1: Actually, I dont think this query need the distinct

1 Like

Building on from what @Rafael_Lima_Vasconce suggested, I created the following as an example “Easter 2019” badge.

I have absolutely no experience with SQL besides just modifying numerical values on some of the cool badge queries that have been posted here, but I don’t see any indication that this shouldn’t work (assuming you’re still trying to find a solution).

If you wanted people to receive the badge multiple times, I believe just enabling that tick option when setting up your badge should do so. Otherwise, it should only give them the badge the first time they visit during the specified time-span.


Is it possible to make a badge for people who select a particular answer on a poll?

I’m using a custom SSO solution, is it possible to pass badges as the SSO payload? I couldn’t find anything in the sso guide for it.

We have a specific type of accolade we give our users on our platform, and we want that to reflect on our Discourse forum as well. It’s basically a boolean value, and I can pass it to the payload on login if that is allowed. Otherwise is it possible to make a group and if you’re part of that special group that you have a badge?

Thanks in advance!

1 Like

No, this isn’t possible. You can grant a custom badge through the API though. See How to grant a custom badge through the API for details about that.

You can create a group that adds a group flair to its members’ avatars. This is how the Discourse Team flair is displayed on this forum. To automatically grant a badge, instead of an avatar flair, to members of a specific group, you could either write a badge query, or you could grant the badge through the API.


In case Simon’s idea isn’t clear, you can pass the external badge as a group and then use a custom badge query in discourse to assign a badge to members of that group. (group flair is lots easier if that’ll work for you)

1 Like

Has anyone come up with a SQL statement or could give me an idea of whether the following is possible in awarding a badge?

  1. Responded to a poll with a specific number of answers (e.g. What things have you done to reduce your use of plastics? and given 5 out of 10 responses allow allowing them to fulfill the criteria)
  2. And have responded to the topic with an answer (in addition to 1) stating what they will pledge to do going forward
  3. And in addition to 1 & 2 have received 5 likes for their response in 2.

I know it’s complicated by would be cool if someone’s done something like this and could share their experience. Thanks in advance.

1 Like

I’m trying to execute:

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

but I’m getting an error on :backfill. Can anyone tell me why?

What would I need to change on this to exclude correctly the “likes” part. I just want to reward the badge is someone posts a topic in a specific category. Full stop.

Hi I’m trying to make a Badge Query that gives a badge when someone replies to an UNLISTED post.

Sadly it seems the regular Query just ignores “UNLISTED” posts for some reason and doesn’t give the badges.

Here’s the Query in Question, it’s the same as above:

DISTINCT ON (p.user_id)
p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 81 AND -- 81 is the topic I want
  (:backfill OR p.id IN (:post_ids) )

And here’s the Error I get:

No Badges to be assigned.

Unique  (cost=25.87..25.88 rows=2 width=16)
  ->  Sort  (cost=25.87..25.87 rows=2 width=16)
        Sort Key: p.user_id
        ->  Hash Join  (cost=6.73..25.86 rows=2 width=16)
              Hash Cond: (t.category_id = c.id)
              ->  Nested Loop  (cost=4.32..23.44 rows=4 width=20)
                    ->  Index Scan using topics_pkey on topics t  (cost=0.14..8.16 rows=1 width=8)
                          Index Cond: (id = 81)
                          Filter: ((deleted_at IS NULL) AND visible)
                    ->  Bitmap Heap Scan on posts p  (cost=4.18..15.24 rows=4 width=20)
                          Recheck Cond: (topic_id = 81)
                          Filter: ((deleted_at IS NULL) AND (post_type = ANY ('{1,2,3}'::integer[])))
                          ->  Bitmap Index Scan on index_posts_on_topic_id_and_sort_order  (cost=0.00..4.17 rows=4 width=0)
                                Index Cond: (topic_id = 81)
              ->  Hash  (cost=2.21..2.21 rows=16 width=4)
                    ->  Seq Scan on categories c  (cost=0.00..2.21 rows=16 width=4)
                          Filter: (allow_badges AND (NOT read_restricted))

How can I modify my query to NOT ignore Unlisted posts?

Thank you very much for your time.

1 Like

Try changing

FROM badge_posts p


FROM posts p

The badge_posts table doesn’t include unlisted posts, or posts that have been created in private categories.


Hi everyone!

I wanted to ask a hand on a query. I wanted to create a badge granted when someone replies at least once on at least topics.

So far, using different sql queries in this thread I have the easy part : 50 replies

SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts  
WHERE post_number > 1 
AND (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) >= 50

But I lack the knowledge to add the trigger that the replies need to be into 50 or more different topics.

If anyone has a clue, I’ll take it


1 Like

We run a site that is stimulating people to be more sustainable. I want to award badges that award savings in; carbon, water and waste.

I want to make it so that we award one badge for each of these but multiple times. For example 100 KGs Saving in Carbon would be a good metric for a badge.

What we want to do is make it so if someone writes a Topic with a particular tag (let’s say for this example carbon_saving) they are awarded the 100 KGs Saving in Carbon badge. I can find a query for that.

The same topic may record a saving of 400 KGs of Carbon Savings, and we could write a query to find that in the content. But could we award the badge 4 times?

I can’t find a way to do that. Has anyone any ideas?

1 Like