고정된 주제 수에 따른 배지 / 배지 SQL?

제품 지원
1

우리 커뮤니티는 고정된 주제와 전역 고정된 주제 중심으로 운영됩니다. 불행히도 사용자 프로필 페이지에는 이에 대한 통계가 전혀 제공되지 않으며, 데이터베이스 어디에도 고정/전역 고정이 체계적으로 저장되어 있지 않은 것으로 보입니다.

이러한 상황을 고려하여, 최소한 ‘첫 고정’, ‘10회 고정’, ‘100회 고정’ 및 전역 고정 주제에 대해 배지를 부여할 수 있는 방법을 찾고 있습니다.

이러한 유형의 배지를 위한 배지 SQL 쿼리에 대해 도움을 주실 수 있으신가요?

제 출발점:

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.unpinned IS NOT NULL
GROUP BY p.user_id
HAVING count(*) > 5

하지만 이 쿼리는 다음과 같은 오류를 반환합니다:

ERROR: column t.unpinned does not exist
LINE 6: WHERE t.unpinned IS NOT NULL

제가 아는 한, 한때 고정되었던 주제와 함께 유지되는 다른 데이터 포인트가 없으므로 unpinned을 사용해야 하는 것 같습니다. 아니면 여기서 post_actions를 사용할 수 있을까요?

아니면 "pinned … ago"를 포함하는 스태프/시스템 사용자의 주제 내 첫 번째 게시물을 검색하여 기준으로 삼을 수도 있을까요? action_code => pinned.disabled

image
image388×150 1.9 KB

업데이트: 이 쿼리가 어떻게 작동하는 것 같습니다…

SELECT t.user_id, current_timestamp granted_at
FROM badge_posts p
INNER JOIN topics t on t.id = p.topic_id
WHERE p.action_code ilike 'pinned.disabled'
GROUP BY t.user_id
HAVING count(*) > 0

하지만 이것이 최선의 접근 방식인지 확신이 서지 않으며, 게시물이 여러 번 고정되고 해제될 수 있으므로… 이 쿼리가 각 topic.id당 pinned.disabled를 하나만 포착하는지 확실하지 않습니다.

배지 SQL 전문가의 도움이 있다면 감사하겠습니다.

또는 향후 “좋아요” 등과 같이 고정 상태를 데이터베이스에 체계적으로 저장하고, 사용자 프로필의 Stats 섹션 및 그 아래에 “GOT PINNED” 링크와 함께 표시하는 것도 고려해 볼 수 있습니다. 주제가 고정되면 좋아요에 비해 훨씬 더 큰 인정으로 간주되어야 하므로, 이를 반영해야 하기 때문입니다.

2

업데이트:

결국 직접 해결했습니다. 최종 배지 쿼리는 다음과 같습니다:

WITH unpinned AS ( /* 적어도 하나의 pinned.disabled가 있는 자격 있는 토픽 게시물을 가져옵니다 */
	SELECT t.user_id, t.id
	FROM badge_posts p
	INNER JOIN topics t on t.id = p.topic_id
	WHERE p.action_code ilike 'pinned.disabled'
	GROUP BY t.id, t.user_id
	HAVING count(*) > 0
)
SELECT user_id, count(*), current_timestamp granted_at 
FROM unpinned
GROUP BY user_id
HAVING count(*) > 0

마지막 count는 과거에 고정(pin)되었던 토픽의 수로 변경되어야 합니다. 예를 들어 >0은 최소 1개의 고정된 토픽, >10은 최소 10개의 과거 고정된 토픽을 의미합니다.

Discourse 데이터베이스에는 고정 이력에 대한 다른 데이터 포인트가 없기 때문에 pinned.disabled 우회 방법을 사용해야 했습니다. 또한, 사용 가능한 데이터가 부족하여 일반 고정과 전역 고정(global pin)을 이 쿼리로 구분하는 것도 불가능합니다.

개인적으로 향후 이 부분은 변경되어야 한다고 생각합니다. 참여도(engagement) 통계에서 전역 고정 > 일반 고정 > 댓글 > 좋아요 > 조회 순서가 우선되어야 한다고 보지만, 현재는 전혀 반영되지 않고 있습니다.