While investigating the topic users table last month
Mysterious entries in topic_users table:
I noticed quite a few rows had NULL first_visited_at dates even though the last_visited_at date was not null.
Not certain whether or not it might be due to my dev set-up I ran this query to clean up the cruft.
UPDATE topic_users
SET first_visited_at = last_visited_at - interval '1 hour'
WHERE last_visited_at IS NOT NULL
AND first_visited_at IS NULL
(* I didn’t think to give a total_msecs_viewed - a query for another day)
Shortly after more of the mysterious NULL first_visited_at fields appeared.
I’ve finally narrowed it down to Category Watching.
i.e. when a member is Watching a Category a row is created in the topic_users table. The first_visited_at date is NULL when the member hasn’t visited the topic yet.
Unfortunately, when the member does get around to visiting the topic the first_visited_at field does not get populated.
These two queries should give much the same result
SELECT COUNT(id)
FROM topic_users
WHERE topic_users.first_visited_at IS NULL
AND topic_users.last_visited_at IS NOT NULL
SELECT COUNT(topic_users.user_id)
FROM topic_users
JOIN topics
ON topics.id = topic_users.topic_id
WHERE topic_users.first_visited_at IS NULL
AND topic_users.last_visited_at IS NOT NULL
AND topic_users.notification_level = 3
AND topics.category_id IS NOT NULL
AND topics.category_id IN( SELECT category_users.category_id
FROM category_users
WHERE category_users.notification_level = 3 )
AND topic_users.user_id IN( SELECT category_users.user_id
FROM category_users
WHERE category_users.notification_level = 3 )