Fetch unread messages, by user

Question

I want to maintain a list of global messages that will be displayed to all users of a web app. I want each user to be able to mark these messages as read individually. I've created 2 tables; messages (id, body) and messages_read (user_id, message_id).

Can you provide an sql statement that selects the unread messages for a single user? Or do you have any suggestions for a better way to handle this?

Thanks!

Answer 1

Well, you could use

SELECT id FROM messages m WHERE m.id NOT IN(
    SELECT message_id FROM messages_read WHERE user_id = ?)

Where ? is passed in by your app.

Answer 2

Something like:

SELECT id, body FROM messages LEFT JOIN
  (SELECT message_id FROM messages_read WHERE user_id = ?)
  ON id=message_id WHERE message_id IS NULL

Slightly tricky and I'm not sure how the performance will scale up, but it should work.

Answer 3

If the table definitions you mentioned are complete, you might want to include a date for each message, so you can order them by date.

Also, this might be a slightly more efficient way to do the select:

SELECT id, message
FROM messages
LEFT JOIN messages_read
    ON messages_read.message_id = messages.id
    AND messages_read.[user_id] = @user_id
WHERE
    messages_read.message_id IS NULL