Count unread messages in chats after last read message id in Postgres

Question

I have a chat in an app and need to select unread messages for given chats.

Each chat can have multiple users and for each user I store last read message id per chat.

Here is select for user chats with all messages count:

select c.id, count(cm.id)
from chats_users cu
inner join chats c
on c.id = cu.chat_id
left join chat_messages cm
on cm.chat_id = c.id
where cu.user_id = 1
group by c.id

How to modify this query so it counts only messages after chats_users.last_read_message_id per chat?

Solution query from comments

select c.id, count(cm.id)
from chats_users cu
inner join chats c
on c.id = cu.chat_id
left join chat_messages cm
on cm.chat_id = c.id
and cm.id > cu.last_read_message_id
where cu.user_id = 1
group by c.id

It almost works, but shows incorrectly 0 when cu.last_read_message_id is null.

Answer 1

Note that you don't need the join to chats:

select cu.chat_id, count(cm.id)
from chats_users cu left join
     chat_messages cm
     on cm.chat_id = cu.chat_id and
        (cm.id > cu.last_read_message_id or
         cu.last_read_message_id is null
        )
where cu.user_id = 1
group by cu.chat_id