What cool badge queries have you come up with?

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

Try changing

FROM badge_posts p

to

FROM posts p

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

6 Likes

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

Thanks!

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

AFAIK, you should be able to do all or nearly all that you’ve asked. I don’t have specific query examples for you because I don’t have data explorer running right now. Instead, here’s pointers to related topics.

Query tags on posts

Query poll options selected by a user

Assign badges multiple times
You’ll see that there is a checkbox for Can be granted multiple times on the Badge Query (SQL) page. But, AFAIK, automatic assignment of badges requires the query to target posts - someone more knowledgeable will know for certain:

Assign badges based on other badges already assigned.
So you can create higher-level 400kg badges based on lower-level 100kg badges that have already been assigned. But you may not be able to assign multiple higher-level badges because they wouldn’t be based on a query that targets posts.

Personally, I would prefer to just assign one badge at each level because most users will be more interested in their highest level, and who else is at that level, rather than the numbers of low-level badges they have. However, if you wanted to display progress towards higher levels then the number of lower-level badges will surface that information.

FYI, I only replied because I noticed that this could be your second question unanswered, i.e. your question last year.

You might get a better response by putting your forum scenarios into a separate topic. I’ve noticed that questions not related to actual specific query examples get less attention in this topic. This make sense because I’m not expecting to find new scenario questions in a #howto topic about example badge queries.

Also, I also wonder if you meant to say “I can’t find a query for that”:

1 Like

I don’t believe so. You could make another 400 tag and badge.

1 Like

Really helpful - thanks!

Cheers Jay. This is helpful as it determines a way in which I may gamify.

I could make it a lot simpler and get users to use tags:

100kgCO2Saving
200kgCO2Saving
300kgCO2Saving etc

And then when they’ve got 3 or more likes of their topic they get awarded the badge. That would give the community control over supporting some of this stuff.

3 Likes

I can imagine SQL that would count the number of each of those tags and do the appropriate arithmetic to come up with a total and then have badges for 1000, 5000, and so on. It might be the kind of thing you can get someone to come up with for fun, but it probably won’t be me. :wink:

3 Likes

Hey guys

We run monthly photocontest (all in one single category) including a final poll of 5 user accounts to choose the monthly winner.

Is there any way to set up an automatic badge query for that? I already tested mentioning users in the poll options successfully.

2 Likes