Query for new replies on a comment table?

Question

I have a standard comment_id/comment_parent_id setup on my mysql comments table (with a created timestamp).

My question is what is the least process intensive query to get all NEW replies on a user's comment? Just like with the commenting systems that use a checkbox to email replies to your comment?

Do you cookie a timestamp for last login? Update a last login table?

I guess I'm looking to understand how to retrieve the starting point so I can alert users of replies to their replies.

Sorry if my explanation is cryptic.

Answer 1

Add a "post_last_viewed_by_user" column (hopefully with a better name). Then, when the user visits the post:

SELECT
   comment
FROM
   comment.comment c
   JOIN posts USING p (post_id)
   JOIN user_post_views u USING (user_id)
WHERE
   user_id = ?
   AND c.comment_time > u.post_last_viewed_by_user