What cool badge queries have you come up with?

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

I just found this little gem here. I wanted to create my own custom badge queries for number of posts in specific categories. How can one modify this to also include the condition of specific category ids so only posts in a specific category will count?

Should be a modification of the first query here where badges are awarded based on creating topics in a specific category. The difference is we award based on number of posts in a specific category.

1 Like

Sorry for the basic question but I’m not a big SQL guy …

When I implement this:
1 top poster
0 top 5% posters
2 top 10% posters
5 top 25% posters

I guess my question is, how can I not have any top 5% posters?

Thanks

1 Like

Hi, maybe someone can help me, I would like to create something like this badge described below…

I already “created” a new badge called “active helper” - that was easy, just cannot do the code.

is this possible?

  1. This badge is only rewarded if you are level 2
  2. And, if you have visited 15 times in the last 30 days. [example: tl3 time period = 30, tl3 requires days visited = 15]
  3. And, Sent an invite to a friend at least once.
  4. And, Replied on a set topic once in the last 30 days

I’m trying to identify/reward folks who invite new people & frequently help new people.

1 Like

We want to encourage users to write more private messages about contentious or personal feedback on posts (and do it in a civil and friendly way…), rather than posting it public and possibly steering the conversation off-topic. Here’s the badge:

The query:

SELECT p.user_id, current_timestamp AS granted_at
FROM posts AS p
JOIN topics t on t.id = p.topic_id
WHERE t.archetype = 'private_message'
AND t.title LIKE 'Your post in "%"'
AND p.post_number = 1
AND p.like_count >= 1
AND (:backfill OR p.user_id IN (:user_ids))
GROUP BY p.user_id
HAVING count(*) >=1

I didn’t find a specific trigger for flag messages, so the query uses the default title “Your post in …”. I’d say the badge is easy to game or cheat in several ways, but it would still achieve it’s goal just by giving more visiblity to this feature and communicating that this is regarded positive action for the community.

3 Likes

Going to enable it tomorrow - I’ve checked the replies and there was one about first x amount of forum users but it was deleted - how would I approach creating this?

1 Like

I’m wondering which page you were referring to (assuming this info is still up to date)? I’m attempting to find user_field_n, but examining page source for Admin > Customize > User Fields, as well as in user profiles, turns up nothing.

1 Like

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