Is there a way to extend this feature a little and send a notification to a user (or group) if a First time or Returning notice has been posted?
We’ve got someone who wishes to be in the role of ‘welcoming committee’ but who doesn’t read every post on a large forum. It would be great to have it as a notification for them.
Answering my own question, here is a Data Explorer query to pull those users who have most recently done their first post:
-- [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
This is a minor modification of this query by @tshenry:
It could be improved by having a set time which it looks back from, e.g. one week or one month - but this defeated me as I couldn’t work out how to get NOW() - 7 or the like to work.
Also, excluding PMs as per another of @tshenry’s queries would be awesome but as I’m a SQL newbie it would take me ages to work out how to do it.
I think you would want something like:
AND us.first_post_created_at > NOW() - INTERVAL '7 DAYS'
To exclude PMs, you need to join to the topics table (following the posts.topic_id foreign key) and check the archetype column. Add this before the WHERE clause:
JOIN topics t ON p.topic_id = t.id
…and this before the ORDER BY:
AND t.archetype = 'regular'
The post notices are actually stored in the post_custom_fields table, so you can get a more precise list of posts like this:
-- [params]
-- int :days_ago = 7
SELECT p.created_at,
p.id AS post_id,
p.user_id,
pcf.value AS "notice type"
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
Absolutely brilliant! Thank you, that is so much better.
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.