Some common badge queries idea

Here are some common badge queries which you can use to create your own awesome badge!

  • Grant a badge if user has created at least one topic in the “foo” category
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'foo'
) AND p.post_number = 1
GROUP BY p.user_id
  • Grant a badge if user has created 5 topics in the “foo” category
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'foo'
) AND p.post_number = 1
GROUP BY p.user_id
HAVING count(*) > 4
  • Grant a badge if user has replied to at least one topic
SELECT
DISTINCT ON (p.user_id)
p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
GROUP BY p.user_id
  • Grant a badge if user has replied to 10 topics
SELECT
DISTINCT ON (p.user_id)
p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
GROUP BY p.user_id
HAVING count(*) > 9
  • Grant a badge if user has created 500 posts
SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts  
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) > 499
  • Grant a badge to all the members of the group “foobar”
SELECT user_id, created_at granted_at, NULL post_id
FROM group_users
WHERE group_id = (
  SELECT g.id FROM groups g WHERE g.name = 'foobar'
)
  • Grant a badge to the first 100 users of the forum
SELECT id AS user_id, created_at AS granted_at, NULL AS post_id
FROM users WHERE id > 0
ORDER BY created_at ASC LIMIT 100

I have wiki’d this post, so feel free to add more awesome badge queries here!

21 Likes

Great topic, I already implemented many cool badges taken from this dicussion
I’ll edit your first topic soon!

2 Likes

A while ago I experimented with this as a POC .

I definitely like the sub-query using g.name over finding the group id “manually”

I went to copy what I came up with, but can’t because Admin -> Badges is giving me a
controller “name use model.name” error

In any case, my query needed “backfill” in it to be happy.

I notice none of the example queries have “backfill” in them.

Has something recently changed here?

You need to set trigger to daily to get rid of that.

2 Likes

Any chance of helping how to restrict one of the queries based on created_date for account?

May have found a solution by looking at the anniversary badge. Here’s an example for 3 months… Now I just need to figure out how to merge this with one of the ones from above.

Anyone care to help on how to combine multiple queries as part of a badge requirement? We’re looking to provide custom titles automatically to our most active and loyal members based on a number of criteria.

SELECT u.id AS user_id, MIN(u.created_at + interval '3 MONTHS') AS granted_at
  FROM users u
  JOIN posts p ON p.user_id = u.id
 WHERE u.id > 0
   AND u.active
   AND NOT u.silenced
   AND u.created_at + interval '3 months' < now()
   AND p.deleted_at IS NULL
   AND NOT p.hidden
   AND p.created_at + interval '3 months' > now()
   AND (:backfill OR u.id IN (:user_ids))
 GROUP BY u.id
 HAVING COUNT(p.id) > 0

You can request as mandatory other badges assignment combining multiple criteria.

Couple of quick questions about this if I may.

This post is a good few years old, is this badge SQL still valid?

Secondly, should I use the full ‘Category Name’ including its spaces, or the shorter ‘Category Slug’ including its dashes? :thinking:

Why don’t you simply try it? There is also a preview to see who will be assigned the badge… :wink:

6 Likes

I am was too afraid of breaking my precious Discourse :blush:

Yes, of course :man_facepalming:t2:

To answer my own questions for completeness:

Yes.

The full Category Name is required. I tried the /slug/, it didn’t work. The full category name does work. e.g.:

47

Thanks @Dax :+1:t2:

6 Likes

How can we modify these sql queries to also specify category id?

  • Grant a badge if user has replied to 10 topics
SELECT
DISTINCT ON (p.user_id)
p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
GROUP BY p.user_id
HAVING count(*) > 9
  • Grant a badge if user has created 500 posts
SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts  
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) > 499

Basically I want WHERE category_id = ( SELECT id FROM categories WHERE name ilike 'foo' to be inserted as a conditiion.

1 Like

Just insert t.category_id = ... in the first one, and that plus the JOIN topics t ON t.id = p.topic_id line for the second one.

2 Likes

Ok let’s assume category_id = 2.

  • Grant a badge if user has replied to 10 topics
SELECT
DISTINCT ON (p.user_id)
p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id, t.category_id=2
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
GROUP BY p.user_id
HAVING count(*) > 9
  • Grant a badge if user has created 500 posts
SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts  
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) AND t.category_id =2 )
JOIN topics t ON t.id = p.topic_id
GROUP BY user_id 
HAVING count(*) > 499

@riking Thanks, but where exactly am I inserting these? :sweat_smile: I inserted where I think you mean above. I’m sorry, but I’m new at this so I have no idea. Can you please post the exact code? :blush:

1 Like

Please see the below announcement for information on how to start using these. Badge SQL can no longer be edited by default

1 Like

@riking I already read that announcement. My question was not how to enable the badge queries, but how to write that specific one that uses category as a condition. Can you please help confirm if the 2 queries I wrote above are correct based on the advice you gave?

1 Like

Ah, sorry. I was assuming some existing knowledge of SQL and what order the clauses go in.

SELECT
DISTINCT ON (p.user_id)
p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id = ... -- [! Added]
GROUP BY p.user_id
HAVING count(*) > 9
SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts
JOIN topics t ON t.id = p.topic_id -- [! Added]
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
  AND t.category_id = ... -- [! Added]
GROUP BY user_id 
HAVING count(*) > 499
2 Likes

Thank you! I noticed that the AND t.category_id = is outside the parenthesis of the WHERE condition in the line before. Is that intentional?

1 Like

Yep, you want it to apply regardless of why the query’s running (the :user_ids or :post_ids).

3 Likes

@riking
Hi Kane. I did use the queries you advised, however I am getting this error in my log:

image

Backtrace
rack-mini-profiler-2.0.4/lib/patches/db/pg.rb:110:in `exec'

rack-mini-profiler-2.0.4/lib/patches/db/pg.rb:110:in `async_exec'

mini_sql-0.3/lib/mini_sql/postgres/connection.rb:201:in `run'

mini_sql-0.3/lib/mini_sql/postgres/connection.rb:91:in `query'

(eval):5:in `query'

/var/www/discourse/app/services/badge_granter.rb:371:in `backfill'

/var/www/discourse/app/jobs/scheduled/badge_grant.rb:17:in `block in execute'

activerecord-6.0.3.2/lib/active_record/relation/batches.rb:70:in `block (2 levels) in find_each'

activerecord-6.0.3.2/lib/active_record/relation/batches.rb:70:in `each'

activerecord-6.0.3.2/lib/active_record/relation/batches.rb:70:in `block in find_each'

activerecord-6.0.3.2/lib/active_record/relation/batches.rb:136:in `block in find_in_batches'

activerecord-6.0.3.2/lib/active_record/relation/batches.rb:238:in `block in in_batches'

activerecord-6.0.3.2/lib/active_record/relation/batches.rb:222:in `loop'

activerecord-6.0.3.2/lib/active_record/relation/batches.rb:222:in `in_batches'

activerecord-6.0.3.2/lib/active_record/relation/batches.rb:135:in `find_in_batches'

activerecord-6.0.3.2/lib/active_record/relation/batches.rb:69:in `find_each'

/var/www/discourse/app/jobs/scheduled/badge_grant.rb:15:in `execute'

/var/www/discourse/app/jobs/base.rb:232:in `block (2 levels) in perform'

rails_multisite-2.3.0/lib/rails_multisite/connection_management.rb:68:in `with_connection'

/var/www/discourse/app/jobs/base.rb:221:in `block in perform'

/var/www/discourse/app/jobs/base.rb:217:in `each'

/var/www/discourse/app/jobs/base.rb:217:in `perform'

/var/www/discourse/app/jobs/base.rb:279:in `perform'

mini_scheduler-0.12.2/lib/mini_scheduler/manager.rb:86:in `process_queue'

mini_scheduler-0.12.2/lib/mini_scheduler/manager.rb:36:in `block (2 levels) in initialize'

This is the exact query I used which awards a badge based on the number of posts in a category:

SELECT user_id, 0 post_id, current_timestamp granted_at 
FROM badge_posts
JOIN topics t ON t.id = p.topic_id -- [! Added]
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
  AND t.category_id = 5
GROUP BY user_id 
HAVING count(*) > 499

Is the trigger supposed to be “After the post is processed”?

2 Likes

Oops, FROM badge_posts as p

3 Likes