# Badge query for each time posting on a new topic in a category?
**URL:** https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325
**Category:** Data & reporting
**Tags:** sql-triggered-badge
**Created:** [March 28, 2024, 11:45am UTC](https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325 "2024-03-28T11:45:14Z")
**Posts on this page:** 11
**Page:** 1
### Author:  [@Shauny](https://meta.discourse.org/u/Shauny)
#### Post date: [March 28, 2024, 11:45am UTC](https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325/1 "2024-03-28T11:45:14Z")
I have a badge that my members can earn multiple times, it is a “Book Club” badge - they are awarded it for discussing the book of the month.
Each month, a new topic is created about the next book, and everyone replying should get the badge - this is currently done manually and is a real pain.
I was wondering if someone could help me craft a Badge SQL to fix this.
The user should receive maximum of one badge per topic, the first time they post in that topic. It’s the same badge every time, which is earned multiple times.
Is that possible? Thanks!
---
### Author:  [@Shauny](https://meta.discourse.org/u/Shauny)
#### Post date: [April 4, 2024, 12:15pm UTC](https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325/2 "2024-04-04T12:15:27Z")
Can anyone help with this? Or is there a guide anywhere about how to craft Badge SQL?
---
### Author:  [@thoka](https://meta.discourse.org/u/thoka)
#### Post date: [April 4, 2024, 3:18pm UTC](https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325/3 "2024-04-04T15:18:45Z")
Did you read through [Topics tagged sql-triggered-badge](https://meta.discourse.org/tag/sql-triggered-badge)?
---
### Author:  [@Shauny](https://meta.discourse.org/u/Shauny)
#### Post date: [April 4, 2024, 5:11pm UTC](https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325/4 "2024-04-04T17:11:34Z")
Yes I’ve read all of those.
It’s a bit daunting to just try something out as a badly formed SQL query on a live site could go very wrong.
Is there a test mode or anything?
---
### Author:  [@thoka](https://meta.discourse.org/u/thoka)
#### Post date: [April 4, 2024, 5:20pm UTC](https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325/5 "2024-04-04T17:20:06Z")
> [@Shauny](#):
>
> Is there a test mode or anything?
I use a [staging server](https://meta.discourse.org/t/set-up-a-staging-server/225951) for experiments.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [April 19, 2024, 4:57am UTC](https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325/6 "2024-04-19T04:57:22Z")
Did you get any further with this?
How are the book club topics defined? Are they in a separate category, or grouped by a tag?
---
### Author:  [@Shauny](https://meta.discourse.org/u/Shauny)
#### Post date: [April 19, 2024, 7:30am UTC](https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325/7 "2024-04-19T07:30:53Z")
No further. I’m stumped by the multiple granting factor.
All the topics are in the same category. That category is locked down so only staff can create posts there, so it’s safe to auto grant to any replies in the category.
But I want it to:
- Grant a badge for the first reply by each person in each topic, but only once per topic
- A user can get multiple of the same badge each time they post a reply to a different topic of the same category
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [April 20, 2024, 2:36pm UTC](https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325/8 "2024-04-20T14:36:11Z")
I think something like this could do it:
```sql
WITH book_club_first_posts AS (
SELECT
p.topic_id,
p.user_id,
MIN(p.id) AS post_id
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id = 5 -- replace with the category_id of your book club
AND p.deleted_at ISNULL
AND t.deleted_at ISNULL
AND p.post_type = 1
AND p.post_number <> 1
AND p.user_id > 0
GROUP BY p.topic_id, p.user_id
)
SELECT bcfp.user_id, bcfp.post_id, p.created_at granted_at
FROM book_club_first_posts bcfp
JOIN posts p ON p.id = bcfp.post_id
WHERE (:backfill OR p.id IN (:post_ids))
```
---
### Author:  [@Shauny](https://meta.discourse.org/u/Shauny)
#### Post date: [April 20, 2024, 8:40pm UTC](https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325/9 "2024-04-20T20:40:21Z")
I’ll try it soon thanks.
---
### Author:  [@Shauny](https://meta.discourse.org/u/Shauny)
#### Post date: [April 21, 2024, 8:10am UTC](https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325/10 "2024-04-21T08:10:01Z")
Works great, thank you!!
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [May 21, 2024, 8:10am UTC](https://meta.discourse.org/t/badge-query-for-each-time-posting-on-a-new-topic-in-a-category/301325/11 "2024-05-21T08:10:29Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.