데이터 탐색기 쿼리: 답변이 없는 게시물 찾기

커뮤니티 구축 데이터 및 보고
1

다음 주제를 참고했습니다: How to find topics without a reply from someone other than the topic owner?

이 쿼리를 작성했지만 제 경우 문제가 발생합니다.

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "t"
LINE 31:    AND t.category_id = ANY ('{48,23}'::int[])

쿼리 코드는 다음과 같습니다:

-- [params]
-- int :months_ago = 1

WITH query_period as (
    SELECT
        date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
        date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
)

SELECT 
p.created_at,
p.topic_id,
p.id as post_id,
p.like_count,
p.post_number,
p.reply_count
FROM posts p
LEFT JOIN post_search_data psd ON psd.post_id = p.id
RIGHT JOIN query_period qp
    ON p.created_at >= qp.period_start
        AND p.created_at <= qp.period_end
WHERE 
      reply_count = 0
      AND post_number != 0
	  AND t.category_id = ANY ('{48,23}'::int[])

두 가지 질문이 있습니다:

  1. Categories 테이블을 어떻게 정의해야 이 오류를 해결할 수 있을까요?
  2. 기간을 현재 날짜의 하루 후부터 시작하려면 어떻게 해야 하나요?
2

posts 테이블에 category_id 열이 없어서 발생하는 오류입니다. 동작하려면 topics 테이블과 조인(join)을 수행해야 합니다.

다음과 같이:
LEFT JOIN topics t ON t.id = p.topic_id

필요한 데이터를 자세히 알려주시면 쿼리를 조정해 볼 수 있습니다.
찾으시는 것은 48번과 23번 카테고리에 속하는 모든 게시물(posts)이지, 주제(topics)가 아닌가요? 삭제된 게시물과 주제는 결과에서 무시해야 하나요?

CURRENT DAY()는 오늘 날짜를 반환합니다. 검색하려는 기간은 무엇인가요?

3

답변해 주셔서 감사합니다!

맞습니다

맞습니다 (토픽은 쿼리에 포함되지 않아야 합니다)/

(현재 시간 - 24시간) 타임스탬프부터 1개월

4

삭제된 토픽에 연결된 게시물도 무시되었습니다.

쿼리에서 고려된 기간: period_start: 2021-01-14 / period_end: 2021-02-14

-- [params]
-- int :months_ago = 1

WITH query_period as (
    SELECT
        DATE_TRUNC('day', CURRENT_DATE - INTERVAL '1 day') - INTERVAL ':months_ago months' AS period_start,
        (CURRENT_DATE - INTERVAL '1 day')  AS period_end
)

SELECT 
    p.created_at,
    p.topic_id,
    p.id AS post_id,
    p.like_count,
    p.post_number,
    p.reply_count
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.reply_count = 0
    AND p.post_number > 1
    AND t.category_id IN (48, 23)
    AND p.deleted_at IS NULL
    AND t.deleted_at IS NULL
    AND p.created_at >= (SELECT period_start FROM query_period)
    AND p.created_at <= (SELECT period_end FROM query_period)
5

좋아요! 코드가 작동하고 있습니다.

하지만 테스트해 본 결과, 제외되어야 할 사용자의 게시글도 결과에 포함되어 있었습니다. 저희 포럼에서는 특정 그룹의 멤버들이 모더레이션 기능을 수행합니다.

쿼리에서 특정 그룹에 속한 사용자의 게시글을 제외할 수 있을까요?

토픽 페이지의 .json 파일을 확인해 보니, 다음 사용자 정보만 가시적인 형태로 추적되고 있습니다:

  • “moderator”
  • “admin”
  • “staff”
  • primary_group_name (모더레이터가 서로 다른 그룹에 속할 수 있기 때문에 저희 포럼에서는 설정되어 있지 않음)