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')
)
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. ¯\_(ツ)_/¯
@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?
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
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
)
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
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
I was just awarded the Tester badge, but I only have 2 Bug Reporter badges. The text of the Tester badge says this:
Reported 10 bugs that were liked by the Discourse team
I just checked: One of my bug reports has 10 
likes, but not even those are exclusively by Discourse Team members.
Something seems to be wrong with the badge… 
There are several old bugs that were deleted as not of future historical interest, but the total isn’t 10.
Thanks, I fixed the queries and updated @sam’s post.
@sam 이 코드를 어디서 실행해야 이 배지가 부여되나요? 그리고 버그 보고를 위한 카테고리를 새로 만들어야 하나요?
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
이 배지는 메타에 특화된 것입니다.
아, 디스코스에서 사용할 수 없나요?
네, 가능합니다. 하지만 카테고리 이름과 그룹 이름은 유효한 이름으로 변경해야 합니다.
알겠습니다. 카테고리 트릭을 숨기는 것과 비슷하지만, 여기서 sam 코드를 어디서 실행해야 하는지 파악하지 못했습니다. 관리자 패널에서 실행해야 하나요, 아니면 컨테이너 안에서 rails c를 실행해야 하나요?
코드를 추가할 위치를 찾지 못하고 계실 수 있습니다. 기본적으로 배지 SQL 편집이 비활성화되어 있기 때문입니다. 자세한 내용은 아래 링크를 참고하세요:
따라서 다음과 같은 과정을 거치시는 것이 좋겠습니다:
그리고 아직 하지 않으셨다면, 게시하신 SQL의 값에 따라 다음을 수행하세요:
쿼리에 대한 개선이 될 수 있습니다:slight_smile:
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;