Запрос в Data explorer: найти посты без ответов

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

Следуя теме: How to find topics without a reply from someone other than the topic owner?

Я создал этот запрос, но в моём случае возникает проблема.

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "t"
LINE 31:    AND t.category_id = ANY ('{48,23}'::int[])

Вот код запроса:

-- [params]
-- int :months_ago = 1

WITH query_period as (
    SELECT
        date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
        date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
)

SELECT 
p.created_at,
p.topic_id,
p.id as post_id,
p.like_count,
p.post_number,
p.reply_count
FROM posts p
LEFT JOIN post_search_data psd ON psd.post_id = p.id
RIGHT JOIN query_period qp
    ON p.created_at >= qp.period_start
        AND p.created_at <= qp.period_end
WHERE 
      reply_count = 0
      AND post_number != 0
	  AND t.category_id = ANY ('{48,23}'::int[])

У меня два вопроса:

  1. Как определить таблицу Categories и исправить эту ошибку?
  2. Как начать период на 1 день после текущего дня?
2

Ошибка возникает из-за того, что в таблице posts отсутствует столбец category_id. Чтобы это работало, необходимо выполнить соединение (JOIN) с таблицей topics.

Например:
LEFT JOIN topics t ON t.id = p.topic_id

Если вы уточните, какие именно данные вам нужны, я смогу попробовать скорректировать запрос.
Вы ищете все сообщения (posts), а не темы (topics), которые относятся к категориям 48 и 23? Следует ли исключать из результата сообщения и темы, которые были удалены?

Функция CURRENT DAY() возвращает сегодняшнюю дату. Какой именно период вы хотите использовать для поиска?

3

Спасибо за ответ!

Верно.

Верно (темы не должны включаться в запрос).

1 месяц, начиная с метки времени (текущее время - 24 часа).

4

Посты, связанные с удалёнными темами, также были исключены.

Период, учтённый в запросе: period_start: 2021-01-14 / period_end: 2021-02-14

-- [params]
-- int :months_ago = 1

WITH query_period as (
    SELECT
        DATE_TRUNC('day', CURRENT_DATE - INTERVAL '1 day') - INTERVAL ':months_ago months' AS period_start,
        (CURRENT_DATE - INTERVAL '1 day')  AS period_end
)

SELECT 
    p.created_at,
    p.topic_id,
    p.id AS post_id,
    p.like_count,
    p.post_number,
    p.reply_count
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id
WHERE p.reply_count = 0
    AND p.post_number > 1
    AND t.category_id IN (48, 23)
    AND p.deleted_at IS NULL
    AND t.deleted_at IS NULL
    AND p.created_at >= (SELECT period_start FROM query_period)
    AND p.created_at <= (SELECT period_end FROM query_period)
5

Отлично! Код работает.

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

Можно ли исключить в запросе посты пользователей, состоящих в некоторых группах?

Я проверил .json-файл страницы темы и обнаружил, что в видимом виде там отслеживаются только следующие данные о пользователе:

  • “moderator”
  • “admin”
  • “staff”
  • primary_group_name (не установлен на нашем форуме, так как модераторы могут входить в разные группы)