사용자 노트 대시보드 보고서의 SQL 버전입니다.
이 보고서는 Discourse User Notes 플러그인이 활성화되어 있어야 합니다.
이 대시보드 보고서는 특정 날짜 범위 내에서 스태프 사용자가 생성한 사용자 노트를 나열합니다. 사용자 노트는 중재자 또는 관리자가 사용자 프로필에 추가하는 주석이나 코멘트로, 사용자의 행동, 문제 또는 중요한 정보를 추적하는 데 주로 사용됩니다.
-- [params]
-- date :start_date = 2024-01-07
-- date :end_date = 2024-02-08
WITH user_notes AS (
SELECT
REPLACE(key, 'notes:', '')::int AS user_id,
notes.value->>'created_at' AS created_at,
notes.value->>'raw' AS user_note,
notes.value->>'created_by' AS created_by
FROM plugin_store_rows,
LATERAL json_array_elements(value::json) notes
WHERE plugin_name = 'user_notes'
ORDER BY 2 DESC
)
SELECT
un.user_id,
un.created_by AS moderator_user_id,
un.created_at::date,
un.user_note as html$user_note
FROM user_notes un
JOIN users u ON u.id = un.user_id
WHERE un.created_at BETWEEN :start_date AND :end_date
ORDER BY un.created_at ASC
SQL 쿼리 설명
이 보고서는 user_notes 플러그인이 JSON 형식으로 저장한 노트를 plugin_store_rows 테이블에서 추출하여 쉽게 이해할 수 있는 형태로 제시합니다.
쿼리는 여러 단계로 작동합니다:
- 매개변수:
- 쿼리는 보고서의 기간을 지정하기 위해
:start_date와:end_date라는 두 개의 매개변수를 정의하여 시작합니다. 두 날짜 매개변수 모두YYYY-MM-DD형식을 지원합니다.
- 쿼리는 보고서의 기간을 지정하기 위해
- 공용 테이블 표현식(CTE) -
user_notes: 쿼리는plugin_store_rows테이블에서 관련 데이터를 추출하고 변환하는user_notes라는 이름의 CTE로 시작합니다. 이 테이블은 다양한 플러그인 데이터를 키-값 형식으로 저장하며, 사용자 노트의 키는notes:접두사와 사용자 ID로 구성됩니다. CTE는 다음 작업을 수행합니다:plugin_name이'user_notes'인 행을 필터링하여 사용자 노트 데이터만 선택되도록 합니다.- LATERAL 조인에서
json_array_elements함수를 사용하여value열에 저장된 JSON 배열을 개별 JSON 객체로 확장하며, 각 객체는 하나의 노트를 나타냅니다. notes:접두사를 제거하고 결과를 정수로 캐스팅하여 키에서 사용자 ID를 추출합니다.- JSON 객체에서 노트 생성 날짜, 원본 노트 내용, 노트를 생성한 사용자 ID를 추출합니다.
- 메인 쿼리:
user_notesCTE를users테이블과 조인하여 기존 사용자에 대한 노트만 포함되도록 합니다.created_at날짜를 기반으로 필터링하여 지정된 날짜 범위(:start_date부터:end_date까지) 내에 있는 노트만 포함합니다.- 사용자 ID, 중재자 사용자 ID(노트 생성자), 노트 생성 날짜, 노트 내용을 선택합니다.
- 노트를 시간 순서로 제시하기 위해 노트 생성 날짜를 오름차순으로 정렬합니다.
결과 예시
| user | moderator_user | created_at | user_note |
|---|---|---|---|
| user_1 | staff_user_2 | 2024-01-10 | HTML 포맷이 포함된 사용자 노트 예시 |
| user_3 | staff_user_4 | 2024-01-14 | user_3에 대한 예시 노트 |
| … | … | … | … |