Badges predicated on getting a series of other badges?


(Michael Downey) #1

Story: As an admin, I’d like to create a badge that is automatically granted if (and only if) a user has received a certain list of other badges, so that we can create a list of accomplishments/tasks in our community, and a title/role when certain of those items are achieved.

Is this possible within the current system and SQL, or does there need to be some notion of “sub-badges” to accomplish something like this?


What cool badge queries have you come up with?
(cpradio) #2

Fairly certain you can do this with the custom SQL abilities, you’d simply have the query return users that have badges X, Y and Z.


(Kane York) #3

Proof of concept: Award a badge to anyone with badges 106, 107, and 108.

SELECT user_id, CURRENT_TIMESTAMP granted_at, NULL post_id
FROM user_badges pb
WHERE badge_id = 106
AND EXISTS (
  SELECT 1 FROM user_badges ib
  WHERE pb.user_id = ib.user_id
  AND ib.badge_id = 107
)
AND EXISTS (
  SELECT 1 FROM user_badges ib
  WHERE pb.user_id = ib.user_id
  AND ib.badge_id = 108
)
;

(Michael Downey) #4

Cool. I assumed this would be OK, but didn’t want to build up something unsustainable. :smile:


(Kane York) #5

You can always check how unsustainable your badges are getting by clicking on “Preview with query plan”. However, large numbers really aren’t all that bad because they only run once a day.

Some tips on interpreting the output:

->  Index Scan using posts_pkey on posts p  (cost=0.29..4.69 rows=1 width=8)

The cost tells you the estimated time until the first result, and the estimated time until the last result. So the query planner is estimating that the index scan here will start up 0.29 meaningless time units after it’s first asked for a row, and finish 4.69 meaningless time units after it’s first asked for a row. Approximately.

The planner also estimates that only 1 row will be returned, and it will be 8 bytes.

->  Hash  (cost=2510.78..2510.78 rows=5867 width=4)
      ->  Hash Join  (cost=3.58..2510.78 rows=5867 width=4)

Another example. Here, there’s a Hash Join going on that will take 3.58 time units until its first of 5867 rows, and take 2510 time untils until it finishes. Around that, there’s a Hash operation that starts and ends at the same time that the Hash Join ends.


(oaktree) #6

On my discourse site I’m trying to award badges based on having a certain number of one other badge. I’ve seen some stuff that could help (this post being the most relevant) but I don’t want to screw up my database.


(Jeff Atwood) #7

From a data perspective this is straightforward, count of active user badges grouped by user id where that count is greater than threshold.


(Minh Binh) #8

Cool. I assumed this would be OK, but didn’t want to build up something unsustainable. - Yes!


(oaktree) #9

I don’t want to accidentally re-award badges every time the query runs though…

Any way you could show me what this looks like please?


(Jeff Atwood) #10

We can only dedicate time to paying hosted customers right now.