Retrieving data from PostgreSQL DB in a more efficient way

Question

I'm developing a real-time chat app using PostgreSQL and I'm having the following issue:

When a user logs in, I need to fetch all the users that are not the logged-in user, in order to display them on the sidebar of the app.

Below each user should be displayed the latest message that was sent either by the logged-in user or by the other user.

I'm trying to execute an efficient query in order to retrieve all the users with their latest message at once but with no success.

Here are my tabels:

I tried at first to do something like that:

SELECT users.id, users.first_name, users.last_name, users.image, messages.sender_id, messages.recipient_id, messages.content
FROM users LEFT JOIN messages on users.id = messages.sender_id OR users.id = messages.recipient_id
WHERE (messages.sender_id = 1 OR messages.recipient_id = 1) AND users.id != 1
GROUP BY users.id
ORDER BY messages.created_at DESC;

And I got this error:

• "1" refers to the logged user id

My temporary solution is to fetch all the users from the db, mapping over them on the server and executing another query which sends back the latest message between the users using - ORDER BY created_at DESC LIMIT 1.

I'm sure there are more efficient ways, and I would appreciate any help!

Answer 1

If I follow you correctly, you can use conditional logic to select the messages exchanged (sent or received) between the logged-in user and any other user, and then a join to bring the corresponding user records. To get the latest message per user, distinct on comes handy in Postgres.

Consider:

select distinct on (u.id) u.id, ... -- enumerate the columns you want here
from (
    select m.*, 
        case when sender_id = 1 then recipient_id else sender_id end as other_user_id
    from messages m
    where 1 in (m.sender_id, m.recipient_id)
) m
inner join users u on u.id = m.other_user_id
order by u.id, m.created_at desc

We could also phrase this with a lateral join:

select distinct on (u.id) u.id, ... 
from messages m
cross join lateral (values 
    (case when sender_id = 1 then recipient_id else sender_id end as other_user_id)
) as x(other_user_id)
inner join users u on u.id = x.other_user_id
where 1 in (m.sender_id, m.recipient_id)
order by u.id, m.created_at desc