What cool badge queries have you come up with?

I’m attempting to use this code, and I’m getting the following response: ERROR: invalid input syntax for type integer: "0show_quick_messages", referring to the user settings for the Quick Messages plugin, I’m guessing (even though it’s now disabled).

I’d be grateful for any suggestions about how to avoid or bypass this error.

1 Like

You’re right! I am not immediately finding the custom user field id now myself. You might try with a data explorer query.

1 Like

Thanks—I’m not familiar with those, but I’ll look it up here.

I also found @OnceWas’s post that would allow for looking up the custom user field by name, but ran into an error that I don’t know how to circumvent.

1 Like

ah - the answer is to add .json to the URL and you will find the ID there. e.g.

/admin/customize/user_fields.json

hat tip to @simon who explains it in this post: Querying user_custom_fields

sometimes spending a little time searching existing topics on meta will turn up the answer to your questions. Many people have come before you who share your goals.

4 Likes

You’re right. And thanks for the tip!

2 Likes

Here’s a simple badge granting query which awards a badge to users who have created a new Topic in a specific Category. We use it to reward users who have contributed to a Knowledge Base category.

SELECT DISTINCT ON (t.user_id) t.user_id, t.created_at granted_at
FROM topics t
WHERE t.category_id = 110 <-- change this to your category id
3 Likes

Awesome list. If anyone interested I’ve created on my community an extra badge " Community Hero" for users having 30+ already granted badges. More info here.

1 Like

Hey guys!

I’m trying some badge queries out here :slight_smile: Some are working fine and others I’m still trying out.

Anyone konw how could I create a query that would grant a badge to a user that has X views in topics created in a specific category? I was able to build this query, but for a topic (User got at least X views in a topic in a specific category). The code is:

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 'Technology'
) AND p.post_number = 1
AND views >= 10
and (:backfill OR ( p.id IN (:post_ids) ))
GROUP BY p.user_id

But instead of only one topic, I would like to grant the badge for those who get, for example, 500 views in the sum of views from all the topics that person created in the category Y. Anyone knows how could I achieve this? :smiley:

1 Like

Just tried to achieve this creating queries on Metabase, but couldn’t find a solution. Will keep trying out :slight_smile:

2 Likes

Maybe this is a solution (further tests coming :D)

    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 'Technology'
) AND p.post_number = 1
and (:backfill OR ( p.id IN (:post_ids) ))
GROUP BY p.user_id
HAVING SUM (views) > XXXXXXXXXXX
3 Likes

Hi guys, I managed to test this query up there and it worked! Now, I was wondering if anyone can help me with another challenge. This is the query:

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 'Arts'
) AND p.post_number = 1
and (:backfill OR ( p.id IN (:post_ids) ))
GROUP BY p.user_id
HAVING SUM (views) >= 100

On this example: The user who creates stories in the Arts category and those stories reach 100 views, will receive the badge. The problem is that:
I will need to change this logic over time, because the number of views will increase.
This badge requires 100 views today, but can require 1000 views 6 months from now.

The problem is that when I change the query, it runs again every day and the users who already received it would lose it.

And this would be a disaster, imagine that you reached (now at the beginning of the platform), 10 views and won a Level 2 Badge in Technology. But some months later the platform grows and now Level 2 requires no more 10 views, but 100. You would lose the badge.

There is a way I could prevent this to happen?

1 Like

That query is broken; here is an updated version for getting those on Mailing List Mode:

SELECT user_id, current_timestamp granted_at
FROM user_options
WHERE mailing_list_mode = true
AND (:backfill OR user_id IN (:user_ids))
5 Likes

What does this forum use to create this badge trigger?

That one looks interesting for something I would like to use on my own Discourse forum :slight_smile:

1 Like

This is what we use

SELECT distinct p.user_id, p.created_at granted_at, p.id post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
JOIN post_actions pa ON pa.post_id = p.id AND 
      post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
       ) AND 
       pa.user_id IN (
           SELECT gu.user_id
           FROM group_users gu
           WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'team' ) 
       )
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'bug'
) AND p.post_number = 1

Change team to the group you want to use and change bug to the category you want to use it on.

4 Likes

How to exclude rights to a custom badge if a higher tier has already been granted? The objective is to grant only the highest tier a particular user is eligible for…

Let’s say I have 3 badges:

Tier 1: 5 Likes Given, 5 Received
Tier 2: 100 Likes Given, 100 Received
Tier 3: 250 Likes Given, 250 Received

Code below is for Tier 2. Other tiers have nothing different apart from the number ‘100’

SELECT us.user_id, current_timestamp AS granted_at 
FROM user_stats AS us
WHERE us.likes_received >= 100 
AND us.likes_given >= 100
AND (:backfill OR us.user_id IN (:user_ids))
1 Like

In the /admin/badges/BADGE-ID settings, you simply tick the option for Run revocation query daily. The badge will be removed if they no longer meet the SQL criteria.

image

You might also want to make them Update Daily too so that the granting / revoking happen at around the same time (midnight local time for your instance I think).

3 Likes

Thanks, Nathan. I’ll have a look.

1 Like

Would this also need an extra line in the badge SQL to balance it out? Something like:

SELECT us.user_id, current_timestamp AS granted_at 
FROM user_stats AS us
WHERE us.likes_received >= 100 
AND us.likes_given >= 100
AND us.likes_given < 250
AND (:backfill OR us.user_id IN (:user_ids))

(possibly use a BETWEEN or similar, though I haven’t tested that yet :slight_smile:)

And then when the query runs it will give the badge for 100 Likes the first time, ignore it for 101 through 249, and then revoke at 250 (where the next badge will pick up from).


Update: I’ve had a little practice play with BETWEEN, and something like this seems to catch all the right people in the sample test:

SELECT us.user_id
FROM user_stats AS us
WHERE us.likes_received BETWEEN 100 AND 249 
ORDER BY us.likes_received DESC

So something like this should work if converted into a triggered badge:

SELECT us.user_id, current_timestamp AS granted_at 
FROM user_stats AS us
WHERE us.likes_received BETWEEN 100 AND 249
AND us.likes_given BETWEEN 100 AND 249
AND (:backfill OR us.user_id IN (:user_ids))
3 Likes

This is almost perfect. One rare event may occur: User over-delivers in ‘given’ while lagging in ‘received’ (or vice-versa), so the badge could be lost before the next is granted (revocation routine).

Looked around and discovered one interesting property: something.badge_id = 123 where ‘something’ is the user-defined variable and ‘123’ the ID of another badge. I’ll give a try and supplement the original SQLs of Tier 1 and Tier 2 with disqualifying checks against the higher tier badges.

2 Likes

That’s a very interesting point which I hadn’t considered. :slightly_smiling_face: The dual nature does make it a little more prickly. :thinking:

I like the idea of building in a check against the other badges though, that could be useful.

1 Like