# Badge toegewezen op basis van meerdere badge-toewijzingen
**URL:** https://meta.discourse.org/t/assigned-a-badge-based-on-multiple-badge-assignment/221251
**Category:** Data & reporting
**Tags:** sql-triggered-badge
**Created:** [17 maart 2022 om 16:31 UTC](https://meta.discourse.org/t/assigned-a-badge-based-on-multiple-badge-assignment/221251 "2022-03-17T16:31:49Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [17 maart 2022 om 16:31 UTC](https://meta.discourse.org/t/assigned-a-badge-based-on-multiple-badge-assignment/221251/1 "2022-03-17T16:31:49Z")
Hi folks
I’d like to assign a badge based on how many time a user obtain a specific badge
- Alessio obtained at least 10 times foo badge - \> Cool\_foo badge earned
- Luca obtained at least 15 times foo badge → Super\_foo badge earned
Is it possible with an SQL query?
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [18 maart 2022 om 12:12 UTC](https://meta.discourse.org/t/assigned-a-badge-based-on-multiple-badge-assignment/221251/2 "2022-03-18T12:12:04Z")
This is possible. 👍
Are you granting them manually (using the [data explorer](https://meta.discourse.org/t/32566?silent=true) + bulk award) or triggering them by SQL?
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [18 maart 2022 om 14:07 UTC](https://meta.discourse.org/t/assigned-a-badge-based-on-multiple-badge-assignment/221251/3 "2022-03-18T14:07:55Z")
Both. Most of them are manual but I’m trying to automatize everything
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [18 maart 2022 om 14:25 UTC](https://meta.discourse.org/t/assigned-a-badge-based-on-multiple-badge-assignment/221251/4 "2022-03-18T14:25:27Z")
For a custom triggered one, I think you should be able to steal the Campaigner query and tweak it to work for badges instead of invites.
This one is for having 5 ‘Badge 108’, and would be set to update daily.
```plaintext
SELECT u.id user_id, current_timestamp granted_at
FROM users u
WHERE u.id IN (
SELECT ub.user_id
FROM user_badges ub
WHERE ub.badge_id = 108
GROUP BY ub.user_id
HAVING COUNT(*) >= 4
) AND u.active AND u.silenced_till IS NULL AND u.id > 0 AND
(:backfill OR u.id IN (:user_ids) )
```
I’ve tested it briefly on my test site and it seems to work okay (I trigger the Badge Grant sidekiq job to speed up the wait). But hopefully that at least gives you a starting point to build from. 🙂
There are also a few different examples in here you can use for inspiration, if you’ve not found it yet, [Some common badge queries](https://meta.discourse.org/t/some-common-badge-queries-idea/31859) 👍
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [18 maart 2022 om 16:27 UTC](https://meta.discourse.org/t/assigned-a-badge-based-on-multiple-badge-assignment/221251/5 "2022-03-18T16:27:03Z")
Thank you but I’ve an error in my installation
```plaintext
> ActiveRecord::PreparedStatementInvalid: missing value for :backfill in /*
> * DataExplorer Query
> * Query: /admin/plugins/explorer?id=13
> * Started by: alefattorini
> */
> WITH query AS (
> SELECT u.id user_id, current_timestamp granted_at
> FROM users u
> WHERE u.id IN (
> SELECT ub.user_id
> FROM user_badges ub
> WHERE ub.badge_id = 102
> GROUP BY ub.user_id
> HAVING COUNT(*) >= 1
> ) AND u.active AND u.silenced_till IS NULL AND u.id > 0 AND
> (:backfill OR u.id IN (:user_ids) )
> ) SELECT * FROM query
> LIMIT 1000
```
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [18 maart 2022 om 16:31 UTC](https://meta.discourse.org/t/assigned-a-badge-based-on-multiple-badge-assignment/221251/6 "2022-03-18T16:31:26Z")
Looks like removing
` (:backfill OR u.id IN (:user_ids) )`
works well too
Is it mandatory?
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [18 maart 2022 om 16:41 UTC](https://meta.discourse.org/t/assigned-a-badge-based-on-multiple-badge-assignment/221251/7 "2022-03-18T16:41:56Z")
It looks like you ran that in the [data explorer](https://meta.discourse.org/t/32566?silent=true). For the custom SQL badges you would need to turn on the hidden site setting and add the query to the badge page:
> [@Creating triggered custom badge queries](https://meta.discourse.org/t/triggered-custom-badge-queries/19336):
>
> bookmark This guide explains how to create triggered custom badge queries in Discourse, including the types of badges, constraints for triggered badges, and an example query. person_raising_hand Required user level: Administrator warning This feature is disabled by default. To enable it, follow [this guide](https://meta.discourse.org/t/badge-sql-can-no-longer-be-edited-by-default/47894). When defining badges in Discourse, you’ll encounter a “Trigger” option with the following choices: Update daily When a user acts on post When a user edits or creates a post When…
However, not everyone can do that depending on their hosting package.
But a [data explorer](https://meta.discourse.org/t/32566?silent=true) one is more than possible too if you can’t have the custom SQL triggered ones. 👍 I think you’d have to make some choices about how often you ran it, and how the Badges would be awarded from the results (manually one by one, or through the bulk award), but it’s definitely do-able.
Something simple may be better, though you perhaps want to add a date range parameter too?
```plaintext
WITH foo AS (SELECT ub.user_id,count(ub.user_id), max(ub.granted_at) granted_at
FROM user_badges ub
WHERE ub.badge_id=108
GROUP BY ub.user_id)
SELECT user_id, granted_at
FROM foo
WHERE count >4
ORDER BY granted_at DESC
```
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [18 maart 2022 om 17:00 UTC](https://meta.discourse.org/t/assigned-a-badge-based-on-multiple-badge-assignment/221251/8 "2022-03-18T17:00:20Z")
> [@JammyDodger](#):
>
> But a [data explorer](https://meta.discourse.org/t/32566?silent=true) one is more than possible too if you can’t have the custom SQL triggered ones. 👍 I think you’d have to make some choices about how often you ran it, and how the Badges would be awarded from the results (manually one by one, or through the bulk award), but it’s definitely do-able.
I have this enabled. It woks like a charm 😃 thank you
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [18 maart 2022 om 18:24 UTC](https://meta.discourse.org/t/assigned-a-badge-based-on-multiple-badge-assignment/221251/9 "2022-03-18T18:24:02Z")
That’s great. 🙂 I find copying the existing badge queries and having a tweak quite useful to give me an idea of where to start. I’m sure they can then be refined further as well if you encounter any issues with one. 👍
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [17 april 2022 om 18:24 UTC](https://meta.discourse.org/t/assigned-a-badge-based-on-multiple-badge-assignment/221251/10 "2022-04-17T18:24:05Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.