Отчет по панели управления: правки постов

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчёта панели управления по правкам постов.

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

-- [params]
-- date :start_date = 2023-12-16
-- date :end_date = 2024-01-16

SELECT
    pr.created_at::date as day,
    pr.post_id as post_id,
    pr.user_id AS editor_user_id,
    p.user_id AS author_user_id,
    p.edit_reason
FROM post_revisions pr
JOIN posts p ON p.id = pr.post_id
WHERE pr.created_at BETWEEN :start_date AND :end_date
AND pr.user_id > 0
AND pr.user_id <> p.user_id
ORDER BY pr.created_at ASC

Пояснение к SQL-запросу

• Объявление параметров:
  • Запрос принимает два параметра: :start_date и :end_date, которые определяют диапазон дат для отчёта. Оба параметра даты принимают формат ГГГГ-ММ-ДД.
• Выбор данных: Оператор SELECT указывает столбцы, которые будут отображены в итоговом отчёте. В их числе:
  • pr.created_at::date as day: Дата создания ревизии поста, приведённая к типу date для удаления части времени.
  • pr.post_id: ID отредактированного поста.
  • pr.user_id AS editor_user_id: ID пользователя, внесшего правку. Этот столбец имеет псевдоним editor_user_id, чтобы отличать его от автора оригинального поста.
  • p.user_id AS author_user_id: ID пользователя, написавшего оригинальный пост, с псевдонимом author_user_id.
  • p.edit_reason: Указанная причина правки, если она была предоставлена.
• Объединение таблиц: В предложении FROM используется оператор JOIN, который объединяет таблицу post_revisions (pr) с таблицей posts (p). Объединение выполняется при условии, что post_revisions.post_id совпадает с posts.id, связывая таким образом ревизию с данными оригинального поста.
• Фильтры: В предложении WHERE применяются несколько фильтров к данным:
  • pr.created_at BETWEEN :start_date AND :end_date: Включает только ревизии постов, созданные в диапазоне дат, указанном параметрами.
  • pr.user_id > 0: Гарантирует, что редактор — реальный пользователь, а не системная операция (user_id = 0 обычно указывает на системную операцию).
  • pr.user_id <> p.user_id: Исключает ревизии, внесённые оригинальным автором поста, фокусируясь только на правках, сделанных другими пользователями.
• Сортировка результатов: Предложение ORDER BY pr.created_at ASC сортирует результаты от старых к новым на основе временной метки создания каждой ревизии поста.

Пример результатов

day	post	editor_user	author_user	edit_reason
2023-12-20	post_abc	user1	user2	Пример причины правки
2023-12-21	post_dfg	user3	user4	Пример причины правки
2023-12-24	post_xyz	user5	user6	NULL