이 기능을 조금 더 확장해서 ‘처음 방문’ 또는 ‘재방문’ 알림이 게시될 때 특정 사용자(또는 그룹)에게 알림을 보낼 수 있는 방법이 있을까요?
저희 포럼은 규모가 크고 모든 게시글을 읽지 않는 ‘환영위원회’ 역할을 맡고 있는 분이 있습니다. 이 분에게는 이 알림을 따로 받을 수 있으면 좋겠습니다.
질문에 직접 답변해 드리겠습니다. 가장 최근 첫 게시글을 작성한 사용자를 가져오기 위한 Data Explorer 쿼리는 다음과 같습니다:
-- [params]
-- date :start_date
SELECT u.id AS user_id, p.id AS post_id, p.created_at
FROM users u
JOIN user_stats us
ON u.id = us.user_id
JOIN posts p
ON u.id = p.user_id
WHERE p.created_at = us.first_post_created_at
AND us.first_post_created_at BETWEEN :start_date::date AND NOW()
ORDER BY us.first_post_created_at desc
이것은 @tshenry 님의 이 쿼리를 약간 수정한 것입니다:
예를 들어, 한 주 또는 한 달 전으로 거슬러 올라가는 설정 시간을 두면 더 개선될 수 있을 것 같습니다. 하지만 NOW() - 7 또는 이와 유사한 표현이 어떻게 작동하는지 파악하지 못해 저에게는 불가능했습니다.
또한, @tshenry 님의 다른 쿼리처럼 PM(개인 메시지)을 제외하는 기능이 있다면 좋겠지만, 저는 SQL 초보자라 그 방법을 알아내는 데 상당한 시간이 걸릴 것 같습니다.
아래와 같은 구문을 사용해야 할 것 같습니다:
AND us.first_post_created_at > NOW() - INTERVAL '7 DAYS'
PM을 제외하려면 topics 테이블에 조인(외래 키 posts.topic_id를 따름)하고 archetype 컬럼을 확인해야 합니다. WHERE 절 앞에 다음을 추가하세요:
JOIN topics t ON p.topic_id = t.id
…그리고 ORDER BY 앞에 다음을 추가하세요:
AND t.archetype = 'regular'
게시물 알림은 실제로 post_custom_fields 테이블에 저장되므로, 다음과 같이 더 정확한 게시물 목록을 가져올 수 있습니다:
-- [params]
-- int :days_ago = 7
SELECT p.created_at,
p.id AS post_id,
p.user_id,
pcf.value AS "알림 유형"
FROM post_custom_fields pcf
INNER JOIN posts p ON pcf.post_id = p.id
INNER JOIN topics t ON p.topic_id = t.id
WHERE pcf.name = 'notice_type'
AND p.created_at > NOW() - INTERVAL ':days_ago days'
AND t.archetype = 'regular'
ORDER BY p.created_at ASC
정말 훌륭합니다! 감사합니다. 훨씬 낫네요.