Some common badge queries idea


(Arpit Jalan) #1

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!


What are Badges?
New extensibility sub-category: Badge Queries
[PAID] - Points & Awards Plugin
New badge, grant if post in category AND posted x posts anywhere
Default Custom Group
Default Custom Group
(Alessio Fattorini) #2

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


(Mittineague) #3

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?


(Kane York) #4

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


(Charles Walter) #5

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


(Charles Walter) #6

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

(Alessio Fattorini) #7

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