What cool badge queries have you come up with?

badge
(cosmo) #157

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

#158

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

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

image
image.png762×510 26.4 KB

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

(Jay Pfaffman) #160

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)

(Mathias King) #161

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

(Jay Pfaffman) #162

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

(Mathias King) #163

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

(Jay Pfaffman) #164

This topic and the Data Explorer Plugin are your guides.

(Mathias King) #165

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
    */
#166

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

(Mathias King) #167 
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) )
(Mathias King) #168

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.

(Mathias King) #169

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
(Timothy Vail) #170

With the new SQL rules, how would you make a badge for introducing yourself on a topic?

(Yaw Anokwa) #171

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

(Yaw Anokwa) #172

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) )
(El Foro Viajero) #173

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!

(Sam Saffron) #174

badge sql is disabled via site settings out-of-the-box cause often people make very bad sql choices.

(El Foro Viajero) #175

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 :slight_smile:

(Daniela) #176

If you are self hosted you can re-enable the SQL queries using rails console, see

(Alexander Wright) #177

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!