# What is the bug reporter badge?
**URL:** https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987
**Category:** Data & reporting
**Tags:** sql-triggered-badge
**Created:** [24 يوليو 2014، 8:58ص UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987 "2014-07-24T08:58:01Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [24 يوليو 2014، 8:58ص UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/1 "2014-07-24T08:58:01Z")
You may or may have not noticed this new badge:
[https://meta.discourse.org/badges/114/bug-reporter](https://meta.discourse.org/badges/114/bug-reporter)
What I find super exciting about it is that there are **zero** plugins needed, I crafted it using our badges UI. Not only does it grant the badge daily, it will also grant it after one of the team “likes” a bug.
It runs the following badge query:
```plaintext
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
JOIN post_actions pa ON pa.post_id = p.id AND
post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND
pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'discourse' )
)
WHERE category_id = (
SELECT id FROM categories WHERE name ilike 'bug'
) AND p.post_number = 1
GROUP BY p.user_id
```
The UI is still a tiny bit rough, but I find it super duper cool that the badge system is this extensible. Will knock up a silver version of this badge tomorrow and call it tester 🐛
Thank you all for reporting bugs so diligently.
---
### Author:  [@downey](https://meta.discourse.org/u/downey)
#### Post date: [18 أغسطس 2014، 7:48م UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/2 "2014-08-18T19:48:07Z")
Just to clarify how this works for more lay-people (please correct me if I’m wrong):
**If a user in the group named** `discourse` **likes the first post in a topic in category** `bug` **then the badge is issued.**
Correct?
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [18 أغسطس 2014، 9:08م UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/3 "2014-08-18T21:08:02Z")
yes
---
### Author:  [@JP\_](https://meta.discourse.org/u/JP_)
#### Post date: [15 يناير 2015، 4:03م UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/4 "2015-01-15T16:03:58Z")
Is there a way to make this work in categories that aren’t publicly visible?
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [15 يناير 2015، 7:24م UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/5 "2015-01-15T19:24:52Z")
You would need to select from `posts` instead of `badge_posts` and basically reimplement the filters that view gives to avoid data leaks.
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [30 أبريل 2015، 8:11ص UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/6 "2015-04-30T08:11:06Z")
> [@sam](#):
>
> Will knock up a silver version of this badge tomorrow and call it tester
Could you please post this query? 🙂
Can I select more teams? Like ‘discourse’ or ‘admins’ or ‘dev\_team’ 😄
Last question, I can’t use markdown into Badge Description, how do you have created the link to discourse team?
---
### Author:  [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [5 أغسطس 2015، 11:39ص UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/7 "2015-08-05T11:39:54Z")
Let me resurrect this thread again: @sam, could you post the query, please? This is interesting for a lot of different use cases, and my SQL has gotten quite rusty 😉
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [5 أغسطس 2015، 12:11م UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/8 "2015-08-05T12:11:07Z")
did you read the first post ⬆
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [5 أغسطس 2015، 12:13م UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/9 "2015-08-05T12:13:16Z")
It works like a charm already used on my forum
---
### Author:  [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [5 أغسطس 2015، 12:25م UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/10 "2015-08-05T12:25:10Z")
Yes, I did! I was referring to the silver version (requiring a specific number of likes), although my reply for some reason isn’t tagged as a reply to that specific post. Sorry for the confusion.
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [5 أغسطس 2015، 7:01م UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/11 "2015-08-05T19:01:46Z")
Just add `HAVING count(*) > 9` to the end. HAVING is like WHERE, but applies after the GROUP BY.
---
### Author:  [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [12 أغسطس 2015، 9:46ص UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/12 "2015-08-12T09:46:14Z")
Wouldn’t that count posts that have been liked multiple times too often, since `post_actions` are part of the `JOIN`?
---
### Author:  [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [12 أغسطس 2015، 10:26ص UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/13 "2015-08-12T10:26:15Z")
I tried to build my own version:
```plaintext
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
SELECT id FROM categories WHERE name ilike ''
) AND (
SELECT count(*)
FROM post_actions pa
WHERE pa.post_id = p.id
AND post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike '' )
)
) >=
AND p.post_number = 1
AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >=
```
Does anyone see any issue with that?
---
### Author:  [@Stephen](https://meta.discourse.org/u/Stephen)
#### Post date: [28 أغسطس 2015، 2:56م UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/14 "2015-08-28T14:56:42Z")
I’m trying to produce a variation too, but I’m concerned with replies that are liked, not the base post. Did you ever figure yours out?
---
### Author:  [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [28 أغسطس 2015، 3:10م UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/15 "2015-08-28T15:10:04Z")
My untested hypothesis is that the only thing here that is specific to base posts is this:
```plaintext
AND p.post_number = 1
```
Depending on what you want, deleting this clause or making this `p.post_number > 1` should work. Again, this is untested, so please be sure to run a plausibility test and report back 😉
---
### Author:  [@Er00](https://meta.discourse.org/u/Er00)
#### Post date: [13 أكتوبر 2015، 11:07ص UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/16 "2015-10-13T11:07:17Z")
I’m trying to use this but getting no badges to be assigned when I run it
```mysql
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
JOIN post_actions pa ON pa.post_id = p.id AND
post_action_type_id = (
SELECT id FROM post_action_types WHERE name_key = 'like'
) AND
pa.user_id IN (
SELECT gu.user_id
FROM group_users gu
WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'Radiant%' )
)
WHERE category_id = (
SELECT id FROM categories WHERE name ilike '[[:<:]]bugs[[:>:]]'
) AND p.post_number = 1
GROUP BY p.user_id
```
Anyone got any ideas? I’ve double checked and there are definitely threads in which the OP has been liked by a Radiant\_Worlds\_Staff member - [http://forum.uk.skysaga.com/t/quests-traders-etc-havent-reset-after-1am/7028/3](http://forum.uk.skysaga.com/t/quests-traders-etc-havent-reset-after-1am/7028/3) for example has been liked by me and I am in that group.
---
### Author:  [@john\_mardlin](https://meta.discourse.org/u/john_mardlin)
#### Post date: [29 أكتوبر 2015، 8:18م UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/17 "2015-10-29T20:18:32Z")
Noob Question 😖
How would I extend this to apply to all categories within and including a [parent category](https://community.coinbase.com/c/developers)?
I was able to match a single category by setting this line to
```
SELECT id FROM categories WHERE name ilike '%developers%'
```
or
SELECT id FROM categories WHERE name ilike ‘%oauth%’
but couldn’t extend it with alternation ie. `'%oauth%|%developers%'`
I’m also worried that my monkeying around might create something really inefficient.
---
### Author:  [@Talha\_Cay](https://meta.discourse.org/u/Talha_Cay)
#### Post date: [30 أكتوبر 2015، 9:49ص UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/18 "2015-10-30T09:49:55Z")
Hello , thank you for information
---
### Author:  [@simonk](https://meta.discourse.org/u/simonk)
#### Post date: [30 أكتوبر 2015، 11:54ص UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/19 "2015-10-30T11:54:32Z")
> [@john\_mardlin](#):
>
> SELECT id FROM categories WHERE name ilike ‘%oauth%’
>
> but couldn’t extend it with alternation ie. ‘%oauth%|%developers%’
You want something like:
```
SELECT id FROM categories
WHERE name ilike '%oauth%'
OR name ilike '%developers%'
```
If you’ve got the exact category names, you could also use:
```
SELECT id FROM categories
WHERE name IN ('OAuth', 'Developers')
```
…but note that the names must match exactly (including case).
---
### Author:  [@john\_mardlin](https://meta.discourse.org/u/john_mardlin)
#### Post date: [30 أكتوبر 2015، 4:37م UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/20 "2015-10-30T16:37:32Z")
Thanks very much for this @simonk
Unfortunately both are giving me this:
`ERROR: more than one row returned by a subquery used as an expression`
[Next page](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987.md?page=2)