Отчет по дашборду — флаги

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчёта по флагам из панели управления

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

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
-- null int :category_id = 0
-- boolean :include_subcategories = false

WITH RECURSIVE subcategories AS (
    SELECT id FROM categories WHERE id = :category_id
    UNION ALL
    SELECT c.id FROM categories c
    INNER JOIN subcategories sc ON c.parent_category_id = sc.id
)

SELECT
    DATE(r.created_at) AS flag_date,
    COUNT(r.id) AS flags_count
FROM reviewables r
LEFT JOIN categories c ON r.category_id = c.id
WHERE r.type = 'ReviewableFlaggedPost'
    AND r.created_at::DATE BETWEEN :start_date AND :end_date
    AND (:category_id = 0 OR (
        :include_subcategories
        AND r.category_id IN (SELECT id FROM subcategories)
        OR NOT :include_subcategories
        AND r.category_id = :category_id
    ))
GROUP BY flag_date
ORDER BY flag_date

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

SQL-запрос предназначен для подсчёта количества флагов (которые представлены записями типа ReviewableFlaggedPost в таблице reviewables), созданных в каждый день указанного диапазона дат и категории. Для опционального включения подкатегорий выбранной категории используется рекурсивное общее табличное выражение (CTE).

Параметры

• :start_date: Начало диапазона дат, за который нужно подсчитать флаги.
• :end_date: Конец диапазона дат, за который нужно подсчитать флаги.
• :category_id: ID категории, для которой нужно подсчитать флаги. Если установлено значение 0, фильтр по категории не применяется.
• :include_subcategories: Булево значение, определяющее, включать ли подкатегории указанной категории в подсчёт.

Разбор запроса

1. Рекурсивное CTE (subcategories):

• Это CTE начинается с выбора категории с ID, переданным через параметр :category_id.
• Затем оно рекурсивно выбирает все подкатегории этой категории, соединяя таблицу categories с CTE subcategories по полю parent_category_id.
• В результате получается список всех подкатегорий (включая исходную категорию), который будет использоваться для фильтрации.

2. Основной запрос:

• Запрос выбирает два столбца: дату создания флага (flag_date) и количество флагов (flags_count) за этот день.
• Он соединяет таблицу reviewables с таблицей categories, чтобы связать каждый флаг с его категорией.

3. Фильтры:

• Запрос фильтрует записи reviewables, оставляя только те, которые имеют тип ReviewableFlaggedPost, что соответствует помеченным постам.
• Он гарантирует, что дата создания флагов (created_at) попадает в указанный диапазон (:start_date — :end_date).
• Применяется условный фильтр на основе параметров :category_id и :include_subcategories:
  • Если :category_id равен 0, фильтр по категории не применяется.
  • Если :include_subcategories равно true, запрос включает флаги из исходной категории и всех её подкатегорий.
  • Если :include_subcategories равно false, запрос включает флаги только из исходной категории.

4. Группировка и сортировка:

• Результаты группируются по дате создания флага для предоставления подсчёта по дням.
• Результаты сортируются по дате для представления данных в хронологическом порядке.

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

flag_date	flags_count
2023-11-19	2
2023-11-22	1
2023-11-23	1
2023-11-24	2
2023-11-26	1
…	…