What cool badge queries have you come up with?

I’ve hence realized that the backfill is just for when the badge query is running and not to use that wile using the data explorer. BUT I tried setting up a badge using the query above and to run the query after a post processed and then had one of my teammates test it and it didn’t give them the badge for posting in the introduction topic.

Alright, so my company wants to focus on the amount of activity individuals spend in each category. Below is the query I came up with. If I set this for the badge query will everyone that fits the qualification get the badge?

select sum(posttotal),userid

from

((select count(p.id) as posttotal ,p.user_id as userid, t.category_id
from posts p
join topics t on p.topic_id = t.id
where p.user_id > 0
and t.category_id = 10
and t.user_id > 0
and p.deleted_by_id is NULL
and t.deleted_by_id is NULL
group by t.category_id, p.user_id)

union

(select count(l.id) as posttotal, l.user_id as userid, t.category_id
from post_actions l
join posts p on l.post_id = p.id
join topics t on p.topic_id = t.id
where l.user_id > 0
and p.user_id > 0
and t.user_id > 0
and t.category_id = 10
and l.post_action_type_id = 2
and p.deleted_by_id is NULL
and l.deleted_by_id is NULL
and t.deleted_by_id is NULL
group by t.category_id, l.user_id))
as q1
group by userid

having sum(posttotal) > 10

With the new SQL rules, how would you make a badge for introducing yourself on a topic?

I used this query…

SELECT
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
59%20PM

2 Likes

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.

SELECT DISTINCT ON (p.user_id)
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) )
4 Likes

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.

4 Likes

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

6 Likes

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!

SQL:

SELECT distinct(user_id), CURRENT_DATE as granted_at  FROM user_visits WHERE
date_part('doy', visited_at) >= 358
OR
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!

2 Likes

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.

4 Likes

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.

3 Likes

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:

SELECT
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