Ecto Query preloading complex association

maz

I’m doing a fairly complex join where I want to preload an array of ContentMediaItems (called :contents) that are nested in an association, Channel

But it appears that ecto thinks I’m referencing the MediaItem in the preload even though I’m doing:

preload: [channel: {chan, contents: chancon}],

and name the associations previously:

join: chan in assoc(con, :channel),
join: chancon in assoc(chan, :contents),

So why does the exception

    ** (Ecto.QueryError) field `MyWord.Multimedia.MediaItem.channel` in preload is not an association in query:

appear?

Here is the full query along with the error below it.

    from(m in MediaItem,
      join: con in ContentMediaItems,
      on: m.id == con.media_item_id,
      where: is_nil(m.published_at) == false,
      join: chan in assoc(con, :channel),
      where: chan.id == con.channel_id,
      join: chancon in assoc(chan, :contents),
      on: chancon.channel_id == chan.id,
      join: org in Org,
      on: m.org_id == org.id,
      where: org.slug == ^org_slug,
      order_by: [{:desc, :published_at}],
      preload: [:mediaitemartifacts],
      preload: [channel: {chan, contents: chancon}],
      select: %{id: m.id, inserted_at: m.inserted_at, media_item: m, channel: chan}
    )

Exception:

** (Ecto.QueryError) field `MyWord.Multimedia.MediaItem.channel` in preload is not an association in query:

from m0 in MyWord.Multimedia.MediaItem,
  join: c1 in MyWord.Channels.ContentMediaItems,
  on: m0.id == c1.media_item_id,
  join: c2 in MyWord.Channels.Channel,
  on: c2.id == c1.channel_id,
  join: c3 in MyWord.Channels.ContentMediaItems,
  on: c3.channel_id == c2.id and c3.channel_id == c2.id,
  join: o4 in MyWord.Orgs.Org,
  on: m0.org_id == o4.id,
  where: is_nil(m0.published_at) == false,
  where: c2.id == c1.channel_id,
  where: o4.slug == ^"myword-app",
  order_by: [desc: m0.published_at],
  limit: ^41,
  select: %{id: m0.id, inserted_at: m0.inserted_at, media_item: m0, channel: c2},
  preload: [:mediaitemartifacts],
  preload: [channel: {c2, contents: c3}]

Relevant schema information:

schema "mediaitems" do
    has_many(:mediaitemartifacts, MyWord.Multimedia.MediaItemArtifact)
    many_to_many(:channels, Channel, join_through: "channels_content_media_items", unique: true)

schema "channels" do
  has_many(:contents, ContentMediaItems)

schema "channels_content_media_items" do
  belongs_to :channel, Channel
  belongs_to :media_item, MediaItem

3 comments

#ecto #ecto-query #preload

0 582 3

2024-04-29 00:33:15 UTC

Showing Posts 1 to 3

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

jswanner

When using assoc/3 or preload the name of the association given as an argument needs to match the name given in the schema (including pluralization).

In the case of your error message, I’m pretty sure you need to use :channels not :channel.

Post #1

sodapopcan

Chiming in to say: when preloading in the query with complex joins like that, it can often be very slow. This is because only one query is run which returns a massive, de-normalized table that has to filtered down by Elixir. I would try it out with Repo.preload as well to see if there is a difference. There might not be, but from experience I once had a response time go from over 30 seconds to under 1 second just by switching to Repo.preload. Fewer queries aren’t always better!

Post #2

maz

This is really good advice and is what I ended up doing.

So to help anyone who’s stuck with a similar problem, here is my less-heavyweight query(I only Ecto.preload :mediaitemartifacts at first).

    from(m in MediaItem,
      join: con in ContentMediaItems,
      on: m.id == con.media_item_id,
      where: is_nil(m.published_at) == false,
      join: chan in assoc(con, :channel),
      where: chan.id == con.channel_id,
      join: org in Org,
      on: m.org_id == org.id,
      where: org.slug == ^org_slug,
      order_by: [{:desc, :published_at}],
      preload: [:mediaitemartifacts],
      select: %{id: m.id, inserted_at: m.inserted_at, media_item: m, channel: chan}
    )

and later on, in the controller, I Repo.preload() the contents:

list =
    Enum.map(list, fn %{media_item: media_item, channel: channel} ->
        %{media_item: media_item, channel: Repo.preload(channel, :contents)}
    end)

Post #3

— All posts loaded —