Undesired behavior when preloading and ordering a has_many :through association

Question

If we have 3 schemas: ChatRoom, User, and Message

ChatRoom.ex
schema "chat_rooms" do
  has_many(:users, User)
  has_many(:messages, through: [:users, :messages])
end

Is there an established way with Ecto to "Get a chat room, and all of the messages of associated users sorted by some field on the message"?

I originally was trying to simply get a chatroom and preload it with all of its messages sorted by message.inserted_at, and was ok with having it do that in two queries (and understand how to do that in a single query combining join and preload), but when it comes time to sort by a value on the has_many :through resource, it seems to apply that same sort to the middle association (in this example, user).

Example issue: So if ChatRoom A has two users in it (user1 joined first, user2 joined second), and we had the message sequence of:

• user1: "hi"
• user2: "hello"
• user1: "goodbye"
• user2: "farewell"

If I did:

sorted_message_query = from(message in Message, order_by: message.inserted_at)

from(chat_room in ChatRoom,
  where: chat_room.id == ^chat_room_id,
  preload: [messages: ^sorted_message_query]
)

The messages result on the chat_room would actually be listed like:

1. user1's "hi"
2. user1's "goodbye"
3. user2's "hello"
4. user2's "farewell"

which is clearly not the goal. How do you sort a preloaded query in Ecto without having that apply to the join resource?

Answer 1

Well, I am not sure why I was finding this so confusing, total brain fart kind of day. Explicitly joining both associations and then preloading those existing joins did it for me:

  from chat_room in ChatRoom,
    where: chat_room.id == ^chat_room_id,
    join: user in assoc(chat_room, :users),
    left_join: message in assoc(user, :messages),
    preload: [users: user, messages: message],
    order_by: message.inserted_at