Аудит контента: отчет по категориям, темам и ответам

Создание сообщества Данные и отчетность
3

Привет @RachFeverBee,

Использование запроса в Data Explorer — лучший способ найти нужную информацию.

Основываясь на предоставленных вами темах и описании ваших потребностей в отчётности, вот запрос Data Explorer, который можно использовать как отправную точку:

Категории, темы и ответы

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2025-01-28

WITH filtered_topics AS (
    SELECT 
        t.id AS topic_id,
        t.title AS topic_title,
        t.created_at AS topic_created_at,
        t.user_id AS topic_user_id,
        t.category_id AS topic_category_id
    FROM 
        topics t
    WHERE 
        t.created_at BETWEEN :start_date AND :end_date
),
filtered_posts AS (
    SELECT 
        p.id AS post_id,
        p.topic_id,
        p.user_id AS post_user_id,
        p.created_at AS post_created_at,
        p.raw AS post_content,
        p.post_number
    FROM 
        posts p
    WHERE 
        p.created_at BETWEEN :start_date AND :end_date
),
categories_with_topics AS (
    SELECT 
        c.id AS category_id,
        c.name AS category_name,
        ft.topic_id,
        ft.topic_title,
        ft.topic_created_at,
        ft.topic_user_id
    FROM 
        categories c
    JOIN 
        filtered_topics ft ON c.id = ft.topic_category_id
),
final_data AS (
    SELECT 
        cwt.category_name,
        cwt.topic_id,
        cwt.topic_title,
        cwt.topic_created_at,
        cwt.topic_user_id,
        fp.post_id,
        fp.post_content,
        fp.post_created_at,
        fp.post_user_id,
        fp.post_number
    FROM 
        categories_with_topics cwt
    LEFT JOIN 
        filtered_posts fp ON cwt.topic_id = fp.topic_id
)
SELECT 
    fd.category_name AS "Категория",
    fd.topic_id AS "ID темы",
    fd.topic_title AS "Заголовок темы",
    fd.topic_created_at AS "Дата создания темы",
    fd.topic_user_id AS "Автор темы",
    fd.post_id AS "ID сообщения",
    fd.post_content AS "Содержимое сообщения",
    fd.post_created_at AS "Дата создания сообщения",
    fd.post_user_id AS "Автор сообщения",
    fd.post_number AS "Номер сообщения"
FROM 
    final_data fd
ORDER BY 
    fd.topic_created_at ASC, 
    fd.post_created_at ASC

Этот отчёт будет генерировать следующие результаты:

  • Название категории: Название категории.
  • ID темы: Ссылочный идентификатор темы.
  • Заголовок темы: Заголовок темы.
  • Дата создания темы: Дата создания темы.
  • Автор темы: Пользователь, создавший тему.
  • ID сообщения: Ссылочный идентификатор сообщения.
  • Содержимое сообщения: Полный текст сообщения.
  • Дата создания сообщения: Дата создания сообщения.
  • Автор сообщения: Пользователь, создавший сообщение.
  • Номер сообщения: Номер сообщения в теме.

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

Категория ID темы Заголовок темы Дата создания темы Автор темы ID сообщения Содержимое сообщения Дата создания сообщения Автор сообщения Номер сообщения
Общие обсуждения 101 Добро пожаловать на форум! 2023-01-02 10:00:00 UTC 1 201 Всем привет, добро пожаловать! 2023-01-02 10:05:00 UTC 2 1
Общие обсуждения 101 Добро пожаловать на форум! 2023-01-02 10:00:00 UTC 1 202 Спасибо за тёплый приём! 2023-01-02 10:10:00 UTC 3 2
Техподдержка 102 Как сбросить устройство? 2023-02-15 14:30:00 UTC 4 203 Кто-нибудь может помочь мне сбросить это? 2023-02-15 14:35:00 UTC 4 1
Техподдержка 102 Как сбросить устройство? 2023-02-15 14:30:00 UTC 4 204 Конечно, вот шаги… 2023-02-15 14:40:00 UTC 5 2
Объявления 103 Вышли новые функции! 2023-03-01 09:00:00 UTC 6 205 Ознакомьтесь с нашими новыми функциями! 2023-03-01 09:05:00 UTC 6 1

Вы можете изменить порядок сортировки результатов в запросе с помощью оператора ORDER BY в конце, в зависимости от того, как вы хотите их просматривать. Также имейте в виду, что если на вашем сайте большое количество тем и сообщений, вам следует учитывать ограничения результатов плагина Data Explorer.