Badges predicated on getting a series of other badges?

Data & reporting
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?

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.

2 Likes
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
)
;
12 Likes
4

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

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.

5 Likes
12

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.