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.
(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.
Is there a database diagram anywhere? I know how to write SQL, I just don’t want to query in the dark.
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
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
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) )
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.
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
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!
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!