Alterar o sistema para o selo de Bug Reporter?

Contribuir Feedback do site
,
10

Acabei de alterar a consulta (bronze) do Bug Reporter de

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

para esta

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)
    )
)

e as consultas prata e ouro de

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"

para

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"
1 curtida