특정 카테고리에서 생성된 새 토픽의 수에 따라 배지를 발급하는 SQL 쿼리를 작성했습니다. 또한 해당 토픽의 첫 번째 게시글에 특정 구문(특정 URL)이 포함되어야 합니다.
쿼리는 동작하지만, 의도한 대로 완전히 작동하지 않습니다:
SELECT badge_posts.user_id, min(badge_posts.created_at) granted_at, MIN(badge_posts.id) AS post_id
FROM badge_posts
JOIN topics ON topics.id = badge_posts.topic_id
WHERE category_id = 17
AND badge_posts.post_number = 1
AND badge_posts.raw LIKE '%dronescene.co.uk%'
GROUP BY badge_posts.user_id
HAVING COUNT(*) > 49
ORDER BY post_id DESC
동, 은, 금, 플래티넘의 네 가지 배지를 사용할 수 있으며, 동배지에는 HAVING count(*) > 1, 은배지에는 HAVING count(*) > 10 등을 사용하고 있습니다. 배지들은 모두 자동으로 발급되며, 그 부분에는 문제가 없습니다.
문제는 모든 배지가 해당 카테고리에서 마지막 토픽 ID가 아닌, 첫 번째 토픽 ID에 대해 발급되고 있다는 것입니다.
예를 들어, 한 회원이 세 개의 배지를 가지고 있는데, 이 세 배지 모두 WHERE 조건에 맞는 가장 최신 토픽 ID가 아니라, WHERE 조건에 맞는 해당 회원이 만든 첫 번째 토픽에 대해 발급되었습니다.
제가 생각하기에, 그것은 이미 4가지 조건을 모두 충족하는 사람들을 대상으로 실행되고 있기 때문인 것 같습니다. 만약 새로 도입된다면, 사용자가 기준을 충족하는 즉시 해당 주제의 최신 날짜 기준으로 자동으로 부여될 것이라고 생각합니다.
‘레거시’ 실행의 경우, LIMIT를 추가해야 할 수도 있습니다?
수정: 한 쿼리로 사용자당 자격을 갖춘 첫 번째(예) 25개 기록을 반환하는 것은 제 현재 기술 수준을 벗어납니다.
그러나 여러 배지를 충족하는 사용자의 수에 따라, 데이터 탐색기(data-explorer) 쿼리의 정보를 사용하여 레거시 배지를 수동으로 부여할 수 있습니다? 그런 다음 위의 자동 배지 쿼리를 실행하면, 해당 배지들이 부여된 후에 올바른 게시물에 올바른 배지가 부여되어야 합니다.
다음과 같은 방식입니다:
-- [params]
-- int :user_id = 1
-- int :limit = 50
SELECT bp.user_id, bp.created_at, bp.id as post_id
FROM badge_posts bp
JOIN topics t ON t.id = bp.topic_id
WHERE bp.user_id = :user_id
AND t.category_id = 17
AND bp.post_number = 1
AND bp.raw LIKE '%dronescene.co.uk%'
ORDER BY bp.created_at ASC
LIMIT :limit
오타가 있을 수 있어 죄송합니다.
(브론즈는 MIN 버전으로, 플래티넘은 MAX 버전으로 처리할 수 있으므로, 10과 25에 해당하는 배지만 남은 것입니다)
