NULL first_visited_at dates in the topic_users table

مطور
6

I have the feeling that when I first looked into the weird topic id problem I may have opened a can of worms.

I’ve put together some queries that can help find the earliest “known” first_visited_at timestamps.

SELECT topic_users.topic_id
 , topic_users.user_id
 , topic_users.last_visited_at
FROM topic_users
WHERE ( topic_users.first_visited_at IS NULL
      AND topic_users.last_visited_at IS NOT NULL ) 

SELECT topic_users.topic_id
 , topic_users.user_id
 , MIN(posts.created_at)
FROM topic_users
JOIN posts
ON posts.topic_id = topic_users.topic_id 
AND posts.user_id = topic_users.user_id
WHERE topic_users.posted 
 AND ( topic_users.first_visited_at IS NULL
      AND topic_users.last_visited_at IS NOT NULL )   
GROUP BY topic_users.topic_id, topic_users.user_id  

SELECT tu.topic_id
 , tu.user_id
 , MIN(pa.created_at)
FROM ( SELECT topic_users.topic_id
        , topic_users.user_id
      FROM topic_users
      WHERE ( topic_users.first_visited_at IS NULL
            AND topic_users.last_visited_at IS NOT NULL )
      ) AS tu 
JOIN LATERAL ( SELECT posts.id
              FROM posts
              WHERE posts.topic_id = tu.topic_id 
              ) AS pt
ON true JOIN LATERAL ( SELECT post_actions.created_at
              FROM post_actions
              WHERE post_actions.post_id = pt.id 
              AND post_actions.user_id = tu.user_id 
              ) AS pa
ON true
GROUP BY tu.topic_id, tu.user_id

But I’m wondering just how important getting a more accurate first_visited_at timestamp really is.

I’ve noticed that when a member is mentioned or invited, a row in the topic_users table is created.
Both the first_visited_at and last_visited_at fields are given :now even if the member never visited the topic.

3 إعجابات