Badge for posts with Likes from a specific group

Community Building Data & reporting
4

Hi @Firepup650 :slight_smile: 이걸 시도해 보세요. 제 인스턴스에서는 잘 작동했습니다.

<CATEGORY NAME> = 대소문자를 구분하는 카테고리 이름 (슬러그가 아님)
<GROUP> = 그룹 이름 (예: Staff, Trust_level_0)
<MINIMUM LIKE COUNT> = 설정하려는 최소 좋아요 수
<POST COUNT THRESHOLD> = 최소 게시글 수

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 t.category_id = (
        SELECT id FROM categories WHERE name ilike '<CATEGORY NAME>'
    ) 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 '<GROUP NAME>' ) 
            )
    ) >= <MINIMUM LIKE COUNT>
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>

여러 카테고리를 처리하려면 이렇게 할 수 있습니다:

<CATEGORY NAMES> = 대소문자를 구분하는 카테고리 이름들
<GROUP> = 그룹 이름 (예: Staff, Trust_level_0)
<MINIMUM LIKE COUNT> = 설정하려는 최소 좋아요 수
<POST COUNT THRESHOLD> = 최소 게시글 수

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 t.category_id IN (
        SELECT id FROM categories WHERE name ILIKE ANY (ARRAY['<CATEGORY NAME 1>', '<CATEGORY NAME 2>', '<CATEGORY NAME 3>'])
    ) 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 '<GROUP>' ) 
            )
    ) >= <MINIMUM LIKE COUNT>
    AND p.user_id >= 0
GROUP BY p.user_id
HAVING count(*) >= <POST COUNT THRESHOLD>