Отчет панели управления - заметки пользователей

[SaraDev (Sara Devlaeminck)]

Это 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-запроса

Этот отчёт извлекает указанные заметки из таблицы plugin_store_rows, где они хранятся в формате JSON плагином user_notes, и представляет их в удобочитаемом виде.

Запрос выполняется в несколько этапов:

• Параметры:
  • В начале запроса определяются два параметра: :start_date и :end_date, указывающие временной период для отчёта. Оба параметра даты принимают формат ГГГГ-ММ-ДД.
• Общее табличное выражение (CTE) — user_notes: Запрос начинается с CTE под названием user_notes, которое извлекает и преобразует соответствующие данные из таблицы plugin_store_rows. Эта таблица хранит различные данные плагинов в формате ключ-значение, где ключ для заметок о пользователях начинается с префикса notes:, за которым следует ID пользователя. CTE выполняет следующие операции:
  • Фильтрует строки, где plugin_name равен 'user_notes', гарантируя, что выбираются только данные о заметках пользователей.
  • Использует функцию json_array_elements в LATERAL-соединении для развёртывания JSON-массива, хранящегося в столбце value, на отдельные JSON-объекты, каждый из которых представляет одну заметку.
  • Извлекает ID пользователя из ключа, удаляя префикс notes: и приводя результат к типу integer.
  • Извлекает дату создания заметки, исходное содержание заметки и ID пользователя, создавшего заметку, из JSON-объекта.
• Основной запрос:
  • Выполняет соединение CTE user_notes с таблицей 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
…	…	…	…