Existe alguma maneira de estender um pouco esse recurso e enviar uma notificação a um usuário (ou grupo) quando um aviso de “Primeira Vez” ou “Retornando” for publicado?
Temos alguém que gostaria de atuar como um “comitê de boas-vindas”, mas que não lê todas as postagens em um fórum grande. Seria ótimo ter isso como uma notificação para essa pessoa.
Respondendo à minha própria pergunta, aqui está uma consulta do Data Explorer para buscar os usuários que fizeram seu primeiro post mais recentemente:
-- [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 é uma pequena modificação da consulta de @tshenry:
Poderia ser melhorada definindo um período fixo para trás, como uma semana ou um mês, mas isso me deixou sem saída, pois não consegui descobrir como fazer NOW() - 7 ou algo similar funcionar.
Além disso, excluir MPs conforme outra consulta de @tshenry seria incrível, mas como sou iniciante em SQL, levaria muito tempo para eu descobrir como fazer isso.
Acho que você gostaria de algo como:
AND us.first_post_created_at > NOW() - INTERVAL '7 DAYS'
(documentação de data/hora do PostgreSQL)
Para excluir MPs, você precisa fazer um join na tabela topics (seguindo a chave estrangeira posts.topic_id) e verificar a coluna archetype. Adicione isso antes da cláusula WHERE:
JOIN topics t ON p.topic_id = t.id
…e isso antes do ORDER BY:
AND t.archetype = 'regular'
As notificações de post são armazenadas na tabela post_custom_fields, então você pode obter uma lista mais precisa de posts assim:
-- [params]
-- int :days_ago = 7
SELECT p.created_at,
p.id AS post_id,
p.user_id,
pcf.value AS "tipo de notificação"
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
Absolutamente brilhante! Obrigado, isso é muito melhor.