How to 'order_by' a query with distinct

Questions / Help
, ,
1

Hi,

I’m trying to write a query that takes users and messages and returns the users ordered by which one most recently sent a message. My query looks like this:

query = from u in User,
            join: m in Message,
            on: m.user_id == u.id,
            distinct: u.id,
            order_by: [desc: m.inserted_at],
            select: [u, m]

This returns distinct users and the last message sent by each by they are ordered according to the user.id and not the message.inserted_at value.
I noticed that the results are ordered if I don’t use ‘distinct’.

2

Im doing this in SQL and not Ecto but hope you understand anyway.
You can do the join so users join on the last message they did:

SELECT * FROM users
INNER JOIN messages ON messages.id = (SELECT MAX(id) FROM messages WHERE messages.user_id = users.id)
ORDER BY messages.id DESC
1 Like
3

Yeah, but how do I translate this to ecto?

4

Add an inner join like:

inner_join: m in Message, on: m.user_id == fragment("(SELECT MAX(id) FROM messages WHERE messages.user_id = u0.id)"),
order_by: [desc: m.id],
3 Likes
5

This is how I used it and it returned an empty result. Have I missed something?

query = from u in User,
            inner_join: m in Message,
            on: m.user_id == fragment("(SELECT MAX(id) FROM messages WHERE messages.user_id = u0.id)"),
            order_by: [desc: m.id],
            select: [u, m]

Nevermind, I found the issue. it is supposed to be m.id not m.user_id. It works perfectly now :slight_smile:

Thank you very much :slight_smile:

1 Like
6

You can do this even without using fragments. Ecto.Query does support subqueries.

1 Like
7

Nevermind, I found the issue. it is supposed to be m.id not m.user_id . It works perfectly now :slight_smile:

Sorry, my bad :slight_smile:

1 Like
8

But how would the subquery know what the u value is?

1 Like