# 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:** [2014年七月24日 08:58 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987 "2014-07-24T08:58:01Z")
**Posts on this page:** 14
**Page:** 2
### Author:  [@simonk](https://meta.discourse.org/u/simonk)
#### Post date: [2015年十月30日 16:55 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/21 "2015-10-30T16:55:22Z")
Oh, sorry, I didn’t look at the context the query was being used in. You’ve currently got something like this:
```
WHERE category_id = (
SELECT id FROM categories
WHERE name IN ('OAuth', 'Developers')
)
```
Since the inner query is returning multiple rows, you need to change the outer `WHERE` clause to:
```
WHERE category_id IN (
SELECT id FROM categories
WHERE name IN ('OAuth', 'Developers')
)
```
---
### Author:  [@john\_mardlin](https://meta.discourse.org/u/john_mardlin)
#### Post date: [2015年十月30日 18:07 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/22 "2015-10-30T18:07:15Z")
Success, thanks!
Would this be too much to run whenever a user acts on a post?
Could it easily be modified to award a badge when a post is ‘marked as a solution’ rather than liked?
edit: Actually, that’s giving me: ‘Contract violation: Query triggers on posts, but does not reference the ‘:post\_ids’ array’
I can probably live with update daily though. ¯\_(ツ)\_/¯
---
### Author:  [@jomaxro](https://meta.discourse.org/u/jomaxro)
#### Post date: [2016年十月11日 03:22 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/23 "2016-10-11T03:22:25Z")
@sam, we’re looking on implementing a similar badge over at Stonehearth. Can you share the current SQL used, as yours is 2+ years old, and there are numerous different versions in this topic?
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [2016年十月30日 23:02 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/24 "2016-10-30T23:02:42Z")
### Bug Reporter
```plaintext
SELECT distinct p.user_id, p.created_at granted_at, 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 'team' )
)
WHERE category_id = (
SELECT id FROM categories WHERE name ilike 'bug'
) AND p.post_number = 1
```
### Tech Support
```plaintext
SELECT id user_id, current_timestamp granted_at
FROM users
WHERE id IN (
SELECT p1.user_id
FROM post_custom_fields pc
JOIN badge_posts p1 ON p1.id = pc.post_id
JOIN topics t1 ON p1.topic_id = t1.id
WHERE p1.user_id <> t1.user_id AND
name = 'is_accepted_answer' AND
p1.user_id IN (
SELECT user_id
FROM posts
WHERE :backfill OR p1.id IN (:post_ids)
)
GROUP BY p1.user_id
HAVING COUNT(*) > 9
)
```
### Tester
```plaintext
SELECT p.user_id, MIN(p.created_at) granted_at
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id = (SELECT id FROM categories WHERE name ILIKE 'bug')
AND p.post_number = 1
AND EXISTS (
SELECT 1
FROM post_actions pa
WHERE pa.post_id = p.id
AND pa.post_action_type_id = (SELECT id FROM post_action_types WHERE name_key = 'like')
AND pa.user_id IN (SELECT user_id FROM group_users WHERE group_id = (SELECT id FROM groups WHERE name ILIKE 'team'))
)
GROUP BY p.user_id
HAVING COUNT(*) >= 10
```
### Senior Tester
```plaintext
SELECT p.user_id, MIN(p.created_at) granted_at
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id = (SELECT id FROM categories WHERE name ILIKE 'bug')
AND p.post_number = 1
AND EXISTS (
SELECT 1
FROM post_actions pa
WHERE pa.post_id = p.id
AND pa.post_action_type_id = (SELECT id FROM post_action_types WHERE name_key = 'like')
AND pa.user_id IN (SELECT user_id FROM group_users WHERE group_id = (SELECT id FROM groups WHERE name ILIKE 'team'))
)
GROUP BY p.user_id
HAVING COUNT(*) >= 20
```
---
### Author:  [@Stefan\_Fairphone](https://meta.discourse.org/u/Stefan_Fairphone)
#### Post date: [2018年六月10日 10:02 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/25 "2018-06-10T10:02:58Z")
I was just awarded the Tester badge, but I only have [2 Bug Reporter](https://meta.discourse.org/badges/114/bug-reporter?username=stefan_fairphone) badges. The text of the [Tester](https://meta.discourse.org/badges/116/tester?username=stefan_fairphone) badge says this:
> Reported 10 bugs that were liked by the [Discourse team](https://meta.discourse.org/groups/team)
I just checked: [One of my bug reports](https://meta.discourse.org/t/moving-whisper-posts-to-a-new-topic-creates-a-broken-topic/78386) has 10 ❤ likes, but not even those are exclusively by Discourse Team members.
Something seems to be wrong with the badge… 🤔
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [2018年六月10日 10:10 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/26 "2018-06-10T10:10:30Z")
There are several old bugs that were deleted as not of future historical interest, but the total isn’t 10.
---
### Author:  [@zogstrip](https://meta.discourse.org/u/zogstrip)
#### Post date: [2018年六月11日 10:23 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/27 "2018-06-11T10:23:53Z")
Thanks, I fixed the queries and updated @sam’s post.
---
### Author:  [@f1r4s](https://meta.discourse.org/u/f1r4s)
#### Post date: [2022年二月8日 22:48 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/28 "2022-02-08T22:48:01Z")
@sam 我应该在哪里运行这段代码才能获得这个徽章，我们是否应该为 bug 报告创建一个类别？
```plaintext
SELECT p.user_id, MIN(p.created_at) granted_at
FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id = (SELECT id FROM categories WHERE name ILIKE 'bug')
AND p.post_number = 1
AND EXISTS (
SELECT 1
FROM post_actions pa
WHERE pa.post_id = p.id
AND pa.post_action_type_id = (SELECT id FROM post_action_types WHERE name_key = 'like')
AND pa.user_id IN (SELECT user_id FROM group_users WHERE group_id = (SELECT id FROM groups WHERE name ILIKE 'team'))
)
GROUP BY p.user_id
HAVING COUNT(*) >= 20
```
---
### Author:  [@tobiaseigen](https://meta.discourse.org/u/tobiaseigen)
#### Post date: [2022年二月8日 22:57 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/29 "2022-02-08T22:57:49Z")
此徽章特定于 Meta。
---
### Author:  [@f1r4s](https://meta.discourse.org/u/f1r4s)
#### Post date: [2022年二月8日 22:59 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/30 "2022-02-08T22:59:07Z")
啊，我们不能在 discourse 中使用它？
---
### Author:  [@Jagster](https://meta.discourse.org/u/Jagster)
#### Post date: [2022年二月8日 23:02 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/31 "2022-02-08T23:02:50Z")
可以，但类别和组名必须更改为有效的名称。
---
### Author:  [@f1r4s](https://meta.discourse.org/u/f1r4s)
#### Post date: [2022年二月8日 23:05 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/32 "2022-02-08T23:05:04Z")
明白了，就像隐藏分类技巧一样，但我没有识别出在哪里运行 sam 代码；是从管理面板还是在容器 rails c 中？
---
### Author:  [@Simon\_Manning](https://meta.discourse.org/u/Simon_Manning)
#### Post date: [2022年二月8日 23:21 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/33 "2022-02-08T23:21:45Z")
您可能找不到添加代码的位置，因为徽章 SQL 编辑现在默认是禁用的。请参阅：
> [@Enable Badge SQL](https://meta.discourse.org/t/badge-sql-can-no-longer-be-edited-by-default/47894):
>
> bookmark This guide explains how to enable Badge SQL in Discourse and discusses the associated security and performance risks. person_raising_hand Required user level: Administrator warning Enabling Badge SQL can pose security and performance risks. To maintain security and performance integrity, as of Discourse 1.6, the ability for administrators to edit badge SQL directly is disabled by default. The two primary concerns around enabling Badge SQL are: Security: Allowing direct SQL…
因此，我认为过程将是：
1. 启用徽章 SQL 编辑（说明请参阅链接的主题）
2. 在“管理”\>“徽章”中创建新徽章（如果您已创建，则编辑现有徽章）
3. 将您的 SQL 添加到徽章并保存
4. 禁用徽章 SQL 编辑
然后，如果您还没有这样做，根据您发布的 SQL 中的值：
1. 创建一个名为“bug”的类别
2. 创建一个名为“team”的用户组
3. 将您希望能够接受错误报告的用户添加到“team”
---
### Author:  [@amira.bedhiafi](https://meta.discourse.org/u/amira.bedhiafi)
#### Post date: [2023年九月17日 12:33 UTC](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987/34 "2023-09-17T12:33:55Z")
这可能是对查询的一个改进 🙂
```plaintext
WITH LikeActionTypes AS (
SELECT id
FROM post_action_types
WHERE name_key = 'like'
),
DiscourseGroup AS (
SELECT id
FROM groups
WHERE name ILIKE 'discourse'
),
BugCategory AS (
SELECT id
FROM categories
WHERE name ILIKE 'bug'
)
SELECT
p.user_id,
MIN(p.created_at) AS granted_at,
MIN(p.id) AS post_id
FROM badge_posts AS p
LEFT JOIN topics AS t ON t.id = p.topic_id
LEFT JOIN post_actions AS pa ON pa.post_id = p.id
LEFT JOIN LikeActionTypes AS lat ON pa.post_action_type_id = lat.id
LEFT JOIN group_users AS gu ON pa.user_id = gu.user_id
LEFT JOIN DiscourseGroup AS dg ON gu.group_id = dg.id
LEFT JOIN BugCategory AS bc ON t.category_id = bc.id
WHERE p.post_number = 1
GROUP BY p.user_id;
```
[上一頁](https://meta.discourse.org/t/what-is-the-bug-reporter-badge/17987.md?page=1)