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
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
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) )
6 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 
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 
Edit1: Actually, I dont think this query need the distinct
1 Like