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!

I fixed your database tags. Please tag only with the database you are really using. — Gordon Linoff
– Gordon Linoff, Commented Oct 9, 2020 at 16:01
Please post your query as text. This is a straightforward issue; the tldr is "use a correlated subquery", I'll include an example when I can copy the query. — AdamKG
– AdamKG, Commented Oct 9, 2020 at 16:26
Where is GROUP BY in posted query? Is this posted query the temporary solution used in app? What is the question here exactly? Is current query running slow? Please describe better but with no success. Errors? Undesired results? Slow process? — Parfait
– Parfait, Commented Oct 9, 2020 at 16:58

GMB · Accepted Answer · 2020-10-09 17:37:44Z

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

Collectives™ on Stack Overflow

Retrieving data from PostgreSQL DB in a more efficient way

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related