# Badges predicated on getting a series of other badges?

**URL:** <https://meta.discourse.org/t/badges-predicated-on-getting-a-series-of-other-badges/22471>\
**Category:** Data & reporting\
**Tags:** sql-triggered-badge\
**Created:** [2014年十一月24日 17:36 UTC](https://meta.discourse.org/t/badges-predicated-on-getting-a-series-of-other-badges/22471 "2014-11-24T17:36:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![downey](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/downey/32/166878_2.png) [@downey](https://meta.discourse.org/u/downey)\
**Post date:** [2014年十一月24日 17:36 UTC](https://meta.discourse.org/t/badges-predicated-on-getting-a-series-of-other-badges/22471/1 "2014-11-24T17:36:00Z")

</div>

**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?

---

<div class="post-metadata">

**Author:** ![cpradio](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/cpradio/32/4970_2.png) [@cpradio](https://meta.discourse.org/u/cpradio)\
**Post date:** [2014年十一月24日 18:31 UTC](https://meta.discourse.org/t/badges-predicated-on-getting-a-series-of-other-badges/22471/2 "2014-11-24T18:31:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![riking](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/riking/32/170938_2.png) [@riking](https://meta.discourse.org/u/riking)\
**Post date:** [2014年十一月24日 18:45 UTC](https://meta.discourse.org/t/badges-predicated-on-getting-a-series-of-other-badges/22471/3 "2014-11-24T18:45:10Z")

</div>

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

```plaintext
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
)
;

```

---

<div class="post-metadata">

**Author:** ![downey](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/downey/32/166878_2.png) [@downey](https://meta.discourse.org/u/downey)\
**Post date:** [2014年十一月24日 18:47 UTC](https://meta.discourse.org/t/badges-predicated-on-getting-a-series-of-other-badges/22471/4 "2014-11-24T18:47:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![riking](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/riking/32/170938_2.png) [@riking](https://meta.discourse.org/u/riking)\
**Post date:** [2014年十一月24日 18:55 UTC](https://meta.discourse.org/t/badges-predicated-on-getting-a-series-of-other-badges/22471/5 "2014-11-24T18:55:58Z")

</div>

> [@downey](#):
>
> something unsustainable

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:

```plaintext
-> 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.

---

<div class="post-metadata">

**Author:** ![system](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/system/32/443519_2.png) [@system](https://meta.discourse.org/u/system)\
**Post date:** [2023年五月23日 13:46 UTC](https://meta.discourse.org/t/badges-predicated-on-getting-a-series-of-other-badges/22471/12 "2023-05-23T13:46:30Z")

</div>

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