# 更改用于Bug Reporter徽章的系统？

**URL:** <https://meta.discourse.org/t/change-system-for-bug-reporter-badge/359421>\
**Category:** Site feedback\
**Tags:** badges, sql-triggered-badge\
**Created:** [2025年三月29日 11:34 UTC](https://meta.discourse.org/t/change-system-for-bug-reporter-badge/359421 "2025-03-29T11:34:15Z")\
**Posts on this page:** 1\
**Showing post:** 10

<div class="post-metadata">

**Author:** ![zogstrip](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/zogstrip/32/512781_2.png) [@zogstrip](https://meta.discourse.org/u/zogstrip)\
**Post date:** [2026年二月24日 11:44 UTC](https://meta.discourse.org/t/change-system-for-bug-reporter-badge/359421/10 "2026-02-24T11:44:06Z")

</div>

刚刚将（青铜）`Bug Reporter` 查询从

```sql
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

```

更改为

```sql
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
WHERE t.category_id = 1 -- bug
AND p.post_number = 1
AND (
    -- team member liked the OP
    EXISTS (
        SELECT 1 
        FROM post_actions pa
        WHERE pa.post_id = p.id
        AND pa.post_action_type_id = 2 -- like
        AND pa.user_id IN (SELECT gu.user_id FROM group_users gu WHERE gu.group_id = 47)
    )
    OR
    -- team member posted a github.com/discourse link in the topic
    EXISTS (
        SELECT 1 
        FROM topic_links tl
        WHERE tl.topic_id = t.id
        AND tl.url LIKE '%github.com/discourse/%'
        AND NOT tl.reflection
        AND tl.user_id IN (SELECT gu.user_id FROM group_users gu WHERE gu.group_id = 47)
    )
)

```

并将银牌和金牌查询从

```sql
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 -- OR 25 for "gold"

```

更改为

```sql
SELECT p.user_id, MIN(p.created_at) granted_at
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id = 1 -- bug
 AND p.post_number = 1
 AND (
     -- team member liked the OP
     EXISTS (
         SELECT 1
           FROM post_actions pa
          WHERE pa.post_id = p.id
            AND pa.post_action_type_id = 2 -- like
            AND pa.user_id IN (SELECT gu.user_id FROM group_users gu WHERE gu.group_id = 47)
     )
     OR
     -- team member posted a github.com/discourse link in the topic
     EXISTS (
         SELECT 1
           FROM topic_links tl
          WHERE tl.topic_id = t.id
            AND tl.url LIKE '%github.com/discourse/%'
            AND NOT tl.reflection
            AND tl.user_id IN (SELECT gu.user_id FROM group_users gu WHERE gu.group_id = 47)
     )
 )
GROUP BY p.user_id
HAVING COUNT(*) >= 10 -- or 25 for "gold"

```

---

_[View the full topic](https://meta.discourse.org/t/change-system-for-bug-reporter-badge/359421)._