CODEWITHSUNDEEP
sqlpostgresqljoin
Almaron
Almaron

Reputation: 4147

Counting the number of unread messages for a user

I have done a fair amount of googling, but I can't seem to get this one right..

I have three tables:

comm_messages:

- room_id:integer
- created_at:datetime

comm_visitors:

- room_id:integer
- user_id:integer
- updated_at:datetime

I want to find out for a given user_id, how many new messages (the ones that were created after the user visited the room) are there?

Example

If I have this kind of data:

comm_messages:

+---------+------------------+
| room_id |    created_at    |
+---------+------------------+
|   1     |   20.11.2018     |
|   1     |   20.12.2018     |
|   2     |   21.12.2018     |
|   3     |   24.12.2018     |
|   4     |   19.05.2018     |
+---------+------------------+

comm_visitors:

+---------+---------+------------+
| user_id | room_id | updated_at |
+---------+---------+------------+
|    1    |    1    | 03.11.2018 |
|    1    |    3    | 25.12.2018 |
|    1    |    4    | 11.05.2018 |
|    2    |    1    | 01.01.2019 |
|    2    |    2    | 03.11.2018 |
|    2    |    4    | 03.11.2018 |
+---------+---------+------------+

For user_id = 1 I should get 3 and for user_id = 2 I should get 2.

My current attempt has been revolving around something like this:

SELECT COUNT(comm_messages.id) 
  FROM comm_messages LEFT JOIN comm_visitors 
   ON comm_messages.room_id = comm_visitors.room_id 
  WHERE comm_visitors.user_id = 1 
   AND comm_visitors.updated_at > comm_messages.updated_at;

But that's no good..

Upvotes: 0

Views: 90

Answers (2)

George S
George S

Reputation: 2161

You're very close, but to get the unread messages you need to look for messages created after / "greater than" the user's visit time, like so:

select count(1)
from comm_visitors v
join comm_messages  m on v.room_id = m.room_id 
 --only include messages created _after_ the last visit
 and v.updated_at < m.created_at
where v.user_id = 1

If you want to include messages in rooms the user has never visited as unread, you can use something like:

select count(1)
from comm_messages m
left join comm_visitors v on v.room_id = m.room_id and v.user_id = 1
where m.created_at > coalesce(v.updated_at, '1900-01-01')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271161

I think you are pretty close. This should do what you want:

SELECT COUNT(*) 
FROM (SELECT v.user_id, v.room_id, MAX(v.updated_at) as last_visit
      FROM comm_visitors v
      WHERE v.user_id = 1
      GROUP BY v.user_id, v.room_id
     ) v JOIN
     comm_messages m
     ON m.room_id = v.room_id AND
        v.last_visit > m.updated_at;

EDIT:

Based on the comment, you should be able to do:

SELECT COUNT(*) 
FROM comm_visitors v JOIN
     comm_messages m
     ON m.room_id = v.room_id AND
        v.updated_at > m.updated_at
WHERE v.user_id = 1

Upvotes: 1

Related Questions