¿Consulta del explorador de datos para devolver ciertas notificaciones de publicaciones?

[nathank (Nathan Kershaw)]

¿Existe alguna forma de ampliar esta función un poco y enviar una notificación a un usuario (o grupo) si se ha publicado un aviso de “primera vez” o de “reingreso”?

Tenemos a alguien que desea desempeñar el papel de “comité de bienvenida”, pero que no lee cada publicación en un foro grande. Sería excelente que esto se convirtiera en una notificación para ellos.

[nathank (Nathan Kershaw)]

Respondiendo a mi propia pregunta, aquí hay una consulta de Data Explorer para obtener esos usuarios que recientemente hicieron su primera publicación:

-- [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

Esta es una modificación menor de esta consulta de @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

Podría mejorarse estableciendo un tiempo fijo desde el cual buscar hacia atrás, por ejemplo, una semana o un mes, pero esto me superó ya que no pude averiguar cómo hacer que NOW() - 7 o algo similar funcionara.

Además, excluir los MP según otra consulta de @tshenry sería genial, pero como soy nuevo en SQL, me tomaría mucho tiempo averiguar cómo hacerlo.

[simonk (Simon King)]

@nathank

Podría mejorarse definiendo un período de tiempo específico desde el cual buscar hacia atrás, por ejemplo, una semana o un mes, pero esto me superó ya que no logré averiguar cómo hacer que NOW() - 7 o algo similar funcionara.

Creo que necesitarías algo como:

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

(documentación de PostgreSQL sobre fechas y horas)

@nathank

Además, excluir los MP como se indica en otra consulta de @tshenry sería genial, pero como soy nuevo en SQL, me tomaría mucho tiempo averiguar cómo hacerlo.

Para excluir los MP, necesitas hacer una unión con la tabla topics (siguiendo la clave foránea posts.topic_id) y verificar la columna archetype. Agrega esto antes de la cláusula WHERE:

JOIN topics t ON p.topic_id = t.id

…y esto antes de ORDER BY:

AND t.archetype = 'regular'

[simonk (Simon King)]

Las notificaciones de publicaciones se almacenan realmente en la tabla post_custom_fields, por lo que puedes obtener una lista más precisa de publicaciones de la siguiente manera:

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

SELECT p.created_at,
       p.id AS post_id,
       p.user_id,
       pcf.value AS "tipo de notificación"
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

puedes obtener una lista más precisa de publicaciones así:

¡Absolutamente brillante! Gracias, eso es mucho mejor.