I'm trying to get a list of conversations out of a database, and I'd like the most recent message to be displayed along with them. I'm having trouble finding a query that lets me do it (and which isn't N+1).
I have these tables:
chats(id, user_id, post_id, created_at)
messages(id, user_id, chat_id, body, created_at)
select chats.id, chats.user_id, m.latest from chats
inner join (
select chat_id, max(created_at) as latest from messages
group by chat_id
) as m on m.chat_id = chats.id;
With standard SQL this can be done using window functions (see sagi's answer).
With Postgres, the proprietary
distinct on() is often faster than the equivalent solution using a window function:
select chats.id, chats.user_id, m.body, m.latest from chats join ( select distinct on (chat_id) chat_id, body, created_at as latest from messages order by chat_id, created_at desc; ) as m on m.chat_id = chats.id;
The solution using a window function is more flexible though. You could also get the latest two (or three or ...) messages with that, whereas
distinct on() will only get you one.