What cool badge queries have you come up with?

Can’t seem to get this to work,
I’ve added;

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 = 172 AND
(:backfill OR p.id IN (:post_ids) )

But running the query isn’t bringing anyone back :’(

Did you change the topic ID? the 172

1 Like

Yeah, I think 172 is the topic number.

where exactly do you go to upload this sql code?? noob here

Go to a badge admin screen e.g. /admin/badges/10

There’s a window in the admin screen like this:

Unless you’re on a site that we host in which case you won’t see it by default.

1 Like

See Badge SQL can no longer be edited by default

(edit: oh. The original post links there too, so perhaps @HAWK’s reply was what you needed)

Can this be configured to be Category specific instead of Topic specific?

Yes. You can issue a badge for whatever SQL you can write that doesn’t crash your server.

1 Like

Is there a database diagram anywhere? I know how to write SQL, I just don’t want to query in the dark.

2 Likes

This topic and the Data Explorer Plugin are your guides.

3 Likes

I keep getting the following error when running these queries. Any help appreciated.

ActiveRecord::PreparedStatementInvalid: missing value for :backfill in /*

  • DataExplorer Query
  • Query: /admin/plugins/explorer?id=3
  • Started by: KingM
  • :xxdummy
    */

Can you post the query that you are trying to run?

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 = 112 AND -- my personal introduction topic
(:backfill OR p.id IN (:post_ids) )

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