Preload only first nested association's association

Owens

Hello all,

I’m doing a DB call for the index page of a WhatsApp-style chat app.

I have ChatsUsers → Chats → Messages.

I need to get the list of the user’s chats ordered by recency of the last_message, and just for the first Chat preload all of its Messages.

See below for reference.

Here’s the DB call I’m trying to make

ChatUser
    |> where(user_id: ^user_id)
    |> join(:left, [cu], c in assoc(cu, :chat)) 
    |> order_by([cu, c], [{:desc, c.last_message}]) 
    |> preload([cu, c], chat: c)
    |> Repo.all()

This will order all the ChatsUsers based on the descending order of its chat.last_message.

But now I need to preload the messages to only the first ChatsUsers’s chat.

Any tips appreciated!

8 comments

#ecto #associations #nested-associations #preload

0 1310 8

2020-11-16 12:00:05 UTC

Showing Posts 1 to 8

Show Best Posts
Show All (oldest first)
Show All (newest first)

LostKobrakai

Why “preload” the messages in the first place? I’d load load those messages separately (in liveview components are great for that).

Post #1

Owens

I thought this would save a DB query and be more performant. Is that not the case?

Post #2

fuelen

Hello!
For my use case I used this:

threads # list of structs
|> MyApp.Repo.preload(messages: {MyApp.Chat.recent_messages_in_thread(), [:author]})

defmodule MyApp.Chat do
  def recent_messages_in_thread(query \\ MyApp.Chat.Message) do
    from messages in query,
      distinct: messages.thread_id,
      order_by: [asc: messages.thread_id, desc: messages.inserted_at]
  end
end

I hope the idea is clear

Post #3

Owens

Thanks for this, I’m not seeing where it loads only the recent messages for the first thread?

Post #4

LostKobrakai

Unless you’re joining data in the query preloading will do additional queries as well. You could join messages only for your first chat, but this would need you knowing which chat the first one is or window functions.

Post #5

Owens

You could join messages only for your first chat, but this would need you knowing which chat the first one

Thanks, assuming we know the first one how would we preload just that?

Post #6

LostKobrakai

Preloading is the act of loading associations for ecto schemas. This can happen in multiple ways, but the default is that associations are loaded in separate queries. So no more performant than a manual query for the messages of your first chat.

The only thing potentially making preloads more performant is joining the needed data in the query, which loads the primary schema and the assoc in one query. Joining messages would by default join them for all chats, but you can alter the join condition to join messages only for the chat id of the first chat and not for all other chats. Preloading that joined binding would mean all chats having an list of messages preloaded, but only the first one would actually hold messages, while all others would be empty lists.

So here we should make sure not to take joining data and preloading for the same thing.

Post #7