Select chat messages unread by current user id in Postgres

Question

I have a chat app. User can have many chats. Chats can have many users. Chats have messages. Messages have readers.

Simplified database structure:

# users
id

# chats
id

# chat_messages
id
chat_id (fk chats.id)
author_id (fk users.id)
message

# chat_message_readers
id
message_id (fk chat_messages.id)
user_id (fk users.id)

I'm trying to get list of messages which were not read by user (messages which don't have reader with user id). My attempts:

List of messages with readers:

select cm.id, count(cmr.id) as readers
from chat_messages as cm
left join chat_message_readers as cmr
on cm.id = cmr.message_id
group by cm.id

List of messages with no readers:

select cm.id, count(cmr.id) as readers
from chat_messages as cm
left join chat_message_readers as cmr
on cm.id = cmr.message_id
where cmr.user_id is null
group by cm.id

I'm stuck at combining above with current user id. Should select all messages except messages which were read by current user (e.g. chat_message_readers.user_id != 1). Would love some help 🙏

Answer 1

You can use the left join version:

select cm.id, (cmr.user_id is not null) as has_been_read_by_user
from chat_messages cm left join
     chat_message_readers cmr
     on cm.id = cmr.message_id and cmr.user_id = ?
where cmr.user_id is null;

Aggregation is not needed.