Запрос Data Explorer для возврата определенных уведомлений о постах?

[nathank (Nathan Kershaw)]

Есть ли способ немного расширить эту функцию и отправлять уведомление пользователю (или группе), если опубликовано сообщение о первом визите или о возвращении?

У нас есть человек, который хотел бы выполнять роль «комитета по приветствию», но не читает каждое сообщение на крупном форуме. Было бы здорово получать об этом уведомления.

[nathank (Nathan Kershaw)]

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

-- [params]
-- date :start_date

SELECT u.id AS user_id, p.id AS post_id, p.created_at
FROM users u
JOIN user_stats us
ON u.id = us.user_id
JOIN posts p
ON u.id = p.user_id
WHERE p.created_at = us.first_post_created_at
AND us.first_post_created_at BETWEEN :start_date::date AND NOW()
ORDER BY us.first_post_created_at desc

Это незначительная модификация запроса от @tshenry:

Users with first post within period

Would something like this meet your needs? -- [params] -- date :start_date -- date :end_date SELECT u.id AS user_id, p.id AS post_id, p.created_at FROM users u JOIN user_stats us ON u.id = us.user_id JOIN posts p ON u.id = p.user_id WHERE p.created_at = us.first_post_created_at AND us.first_post_created_at BETWEEN :start_date::date AND :end_date::date

Его можно было бы улучшить, добавив фиксированный период, за который нужно искать (например, одну неделю или один месяц), но я не смог разобраться, как заставить работать выражение вроде NOW() - 7.

Также было бы здорово исключить личные сообщения (как в одном из других запросов @tshenry) (Return all non-PM topics that have not received a staff reply), но поскольку я новичок в SQL, мне потребовалось бы много времени, чтобы понять, как это сделать.

[simonk (Simon King)]

@nathank

Это можно было бы улучшить, задав фиксированный период, за который нужно искать данные, например, одну неделю или один месяц, но у меня не получилось заставить работать выражение вроде NOW() - 7.

Думаю, вам понадобится что-то вроде:

AND us.first_post_created_at > NOW() - INTERVAL '7 DAYS'

(документация PostgreSQL по датам и времени)

@nathank

Также было бы здорово исключить личные сообщения, как в одном из других запросов @tshenry (Return all non-PM topics that have not received a staff reply), но так как я новичок в SQL, мне потребовалось бы много времени, чтобы разобраться, как это сделать.

Чтобы исключить личные сообщения, нужно выполнить соединение с таблицей topics (используя внешний ключ posts.topic_id) и проверить столбец archetype. Добавьте это перед предложением WHERE:

JOIN topics t ON p.topic_id = t.id

…и это перед предложением ORDER BY:

AND t.archetype = 'regular'

[simonk (Simon King)]

Уведомления о постах на самом деле хранятся в таблице post_custom_fields, поэтому вы можете получить более точный список постов следующим образом:

-- [params]
-- int :days_ago = 7

SELECT p.created_at,
       p.id AS post_id,
       p.user_id,
       pcf.value AS "тип уведомления"
FROM post_custom_fields pcf
INNER JOIN posts p ON pcf.post_id = p.id
INNER JOIN topics t ON p.topic_id = t.id
WHERE pcf.name = 'notice_type'
  AND p.created_at > NOW() - INTERVAL ':days_ago days'
  AND t.archetype = 'regular'
ORDER BY p.created_at ASC

[nathank (Nathan Kershaw)]

@simonk

Вы можете получить более точный список постов, например так:

Просто великолепно! Спасибо, это гораздо лучше.