How to address the issue with limits on preloaded associations using Ecto?

Phoenix Forum Questions / Help
1

I’d love to see how others are building such queries. From my research, I haven’t found a single thread that comes of anything. Is it that rare of a problem?

My current issue is I need to fetch (10) records from one table, and limit (5) the preloaded associated records in another table. I also imagine in the future I’ll need to be able to offset and order records in either table.

Can I do this with subqueries? If so, how do I go about mapping this to structs?

The only solution I can see right now is to build it using subqueries, and then manually assign the struct mappings for the results of the subquery and forget about Ecto preload.

I feel like I’m really close:

Repo.all(
  from c in query,
  left_join: rr in fragment("select * from my_table limit 10"),
  on: rr.c_id == c.id,
  preload: [records: rr],
  limit: 1
)

However, doesn’t seem possible with preload:

Bug Bug …!!** (Ecto.QueryError) can only preload sources with a schema (fragments, binary and subqueries are not supported) in query:

Can I not just assign the preloaded associations to each of my structs myself, or is the problem with that one of the reasons this isn’t implemented?

4 Likes
2

I’m sorry about reviving an old thread, but did you ever find a solution to this? I’m needing to do something very similar.

3

You can limit the number of associations with a preload query.

Another example.

4

I was doing something very similar to this and got different results. I’ll recheck though! Thanks for replying.

5

That won’t work for this, which is the whole problem. If you limit: 10 in a preload query you’ll get back 10 records total not 10 records per item, which is what we want. The only ways I know to get the 10 records per item is via lateral joins or window functions. lateral joins have limited support in ecto, window functions have no support.

6 Likes
6

I wish I could say I found a streamlined way of achieving this… but I really didn’t.

I had to:

  1. write custom sql query and execute using Repo.query!(query)
  2. alias preloaded columns from subquery (r_my_column, etc) to avoid conflicts (id, etc)
  3. loop over the results, parse the column names/values and load into schemas using Repo.load

Lot of ceremony, but this does work and gives me back exactly what I need. I’m only 3 months into Elixir so if I’m doing something naive and this can be simplified I’m very open to suggestions. It was written as part experiment, part requirement, so am not settled yet.

Considering this is in fact possible as seen below, I’m not sure why Ecto isn’t providing a way. I wish I could contribute, but I’m probably a good 6+ months from being able to contribute anything meaningful.

I actually prefer writing custom queries as can fine-tune them to my needs and know exactly what is being executed. I just need a simple way of parsing generic result sets… and I guess that is one of the challenges and potentially why Ecto doesn’t currently support this.

(selects on query have been simplified for illustration purposes)

defmodule Check.Queries do
  alias My.Repo
  import Ecto.Query

  def checks_with_recres(options \\ %{}) do
    query = """
    SELECT c.id,
    r.response_status_code as r_response_status_code
    FROM (
    SELECT * FROM checks
    ORDER BY id
    LIMIT 10
    ) c LEFT OUTER JOIN LATERAL (
      SELECT * FROM request_responses rr
      WHERE rr.check_id = c.id
      LIMIT 2
    ) r on true
    """

    checks = Repo.query!(query)

    checks.rows
    |> Enum.group_by(&List.first/1)
    |> Enum.map(fn {id, group} ->
      check = Repo.load(Check, { checks.columns, List.first(group) })

      recres = group
               |> Enum.map(fn row ->
                 Repo.load(Recres, {
                   assoc_columns_without_alias(checks.columns),
                   assoc_values(row, checks.columns)
                 })
               end)

      %{check | request_responses: recres }
    end)
  end

  # private

  defp assoc_columns_without_alias(columns) do
    assoc_columns(columns)
    |> Enum.map(fn col -> String.replace(col, "r_", "") end)
  end

  defp assoc_columns(columns) do
    columns
    |> Enum.sort
    |> Enum.filter(fn col -> String.contains?(col, "r_") end)
  end

  defp assoc_values(row, columns) do
    Enum.zip(columns, row)
    |> Map.new()
    |> Map.take(assoc_columns(columns))
    |> Map.values
  end
end
1 Like
7

In this particular case a fragment/1 is only really necessary for the right side of the :lateral_join. One advantage of doing that is that you can push some of the work back to Ecto.

Example:

latest_albums_query = from(q in subquery(latest_artists_query), [
  left_lateral_join: lal in fragment("SELECT id FROM albums AS al WHERE al.artist_id = ? ORDER BY al.inserted_at DESC NULLS LAST LIMIT ?", q.id, ^albums_limit), on: true,
  join: al in Album, on: lal.id == al.id,
  select: al # only an ecto struct can be used in subquery
])

Full Example:

iex(1)> alias MusicDB.{Repo,Album,Track}
[MusicDB.Repo, MusicDB.Album, MusicDB.Track]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> forget_assoc = fn ecto_struct, field, cardinality ->
...(3)>   not_loaded = %Ecto.Association.NotLoaded{
...(3)>     __field__: field,
...(3)>     __owner__: ecto_struct.__struct__,
...(3)>     __cardinality__: cardinality
...(3)>   }
...(3)>   Map.put(ecto_struct, field, not_loaded)
...(3)> end
#Function<18.127694169/3 in :erl_eval.expr/5>
iex(4)> forget_artist = &(forget_assoc.(&1, :artist, :one))
#Function<6.127694169/1 in :erl_eval.expr/5>
iex(5)> compact_to_rest = fn
...(5)>   {nil, nil, rest} ->
...(5)>     rest
...(5)>   {artist, albums, rest} ->
...(5)>     [Map.put(artist, :albums, albums)|rest] # NOTE: could reverse albums here
...(5)> end
#Function<6.127694169/1 in :erl_eval.expr/5>
iex(6)> row_to_acc = fn
...(6)>   (%MusicDB.Album{artist: %MusicDB.Artist{id: id}} = a, {%MusicDB.Artist{id: id} = artist, albums, rest}) ->
...(6)>     {artist, [forget_artist.(a)|albums], rest}
...(6)>   (album, acc) ->
...(6)>     {album.artist, [forget_artist.(album)], compact_to_rest.(acc)}
...(6)> end
#Function<12.127694169/2 in :erl_eval.expr/5>
iex(7)> make_result = fn rows ->
...(7)>   rows
...(7)>   |> List.foldl({nil, nil, []}, row_to_acc)
...(7)>   |> compact_to_rest.() # NOTE: could reverse artists after this
...(7)> end
#Function<6.127694169/1 in :erl_eval.expr/5>
iex(8)> artists_limit = 2
2
iex(9)> albums_limit = 2
2
iex(10)> latest_artists_query = from(ar in Artist, [
...(10)>   order_by: [desc: ar.inserted_at],
...(10)>   limit: ^artists_limit
...(10)> ])
#Ecto.Query<from a in MusicDB.Artist, order_by: [desc: a.inserted_at],
 limit: ^2>
iex(11)> latest_albums_query = from(q in subquery(latest_artists_query), [
...(11)>   left_lateral_join: lal in fragment("SELECT id FROM albums AS al WHERE al.artist_id = ? ORDER BY al.inserted_at DESC NULLS LAST LIMIT ?", q.id, ^albums_limit), on: true,
...(11)>   join: al in Album, on: lal.id == al.id,
...(11)>   select: al # only an ecto struct can be used in subquery
...(11)> ])
#Ecto.Query<from a0 in subquery(from a in MusicDB.Artist,
  order_by: [desc: a.inserted_at],
  limit: ^2),
 left_join_lateral: f in fragment("SELECT id FROM albums AS al WHERE al.artist_id = ? ORDER BY al.inserted_at DESC NULLS LAST LIMIT ?", a0.id, ^2),
 on: true, join: a1 in MusicDB.Album, on: f.id == a1.id, select: a1>
iex(12)> query = from(p in subquery(latest_albums_query), [
...(12)>   preload: [:tracks, :artist],
...(12)>   order_by: [asc: :artist_id, asc: :inserted_at] # NOTE: order deliberately reversed for later processing
...(12)> ])
#Ecto.Query<from a in subquery(from a0 in subquery(from a in MusicDB.Artist,
  order_by: [desc: a.inserted_at],
  limit: ^2),
  left_join_lateral: f in fragment("SELECT id FROM albums AS al WHERE al.artist_id = ? ORDER BY al.inserted_at DESC NULLS LAST LIMIT ?", a0.id, ^2),
  on: true,
  join: a1 in MusicDB.Album,
  on: f.id == a1.id,
  select: a1),
 order_by: [asc: a.artist_id, asc: a.inserted_at], preload: [:artist, :tracks]>
iex(13)> rows = Repo.all(query)

12:19:29.805 [debug] QUERY OK db=3.5ms
SELECT s0."id", s0."title", s0."inserted_at", s0."updated_at", s0."artist_id" FROM (SELECT a2."id" AS "id", a2."title" AS "title", a2."inserted_at" AS "inserted_at", a2."updated_at" AS "updated_at", a2."artist_id" AS "artist_id" FROM (SELECT a0."id" AS "id", a0."name" AS "name", a0."birth_date" AS "birth_date", a0."death_date" AS "death_date", a0."inserted_at" AS "inserted_at", a0."updated_at" AS "updated_at" FROM "artists" AS a0 ORDER BY a0."inserted_at" DESC LIMIT $1) AS s0 LEFT OUTER JOIN LATERAL (SELECT id FROM albums AS al WHERE al.artist_id = s0."id" ORDER BY al.inserted_at DESC NULLS LAST LIMIT $2) AS f1 ON TRUE INNER JOIN "albums" AS a2 ON f1."id" = a2."id") AS s0 ORDER BY s0."artist_id", s0."inserted_at" [2, 2]
 
12:19:29.816 [debug] QUERY OK source="artists" db=1.7ms decode=1.6ms
SELECT a0."id", a0."name", a0."birth_date", a0."death_date", a0."inserted_at", a0."updated_at", a0."id" FROM "artists" AS a0 WHERE (a0."id" = ANY($1)) [[3, 2]]
 
12:19:29.816 [debug] QUERY OK source="tracks" db=1.0ms decode=1.8ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id", t0."album_id" FROM "tracks" AS t0 WHERE (t0."album_id" = ANY($1)) ORDER BY t0."album_id" [[5, 4, 3]]
[
  %MusicDB.Album{
    __meta__: #Ecto.Schema.Metadata<:loaded, "albums">,
    artist: %MusicDB.Artist{
      __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
      albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
      birth_date: nil,
      death_date: nil,
      id: 2,
      inserted_at: ~N[2018-06-16 20:29:41.481934],
      name: "Bill Evans",
      tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
      updated_at: ~N[2018-06-16 20:29:41.481940]
    },
    artist_id: 2,
    genres: #Ecto.Association.NotLoaded<association :genres is not loaded>,
    id: 3,
    inserted_at: ~N[2018-06-16 20:29:41.483682],
    title: "You Must Believe In Spring",
    tracks: [
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 3,
        duration: 192,
        duration_string: nil,
        id: 11,
        index: 1,
        inserted_at: ~N[2018-06-16 20:29:41.486309],
        number_of_plays: 0,
        title: "B Minor Waltz (for Ellaine)",
        updated_at: ~N[2018-06-16 20:29:41.486320]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 3,
        duration: 337,
        duration_string: nil,
        id: 12,
        index: 2,
        inserted_at: ~N[2018-06-16 20:29:41.487337],
        number_of_plays: 0,
        title: "You Must Believe In Spring",
        updated_at: ~N[2018-06-16 20:29:41.487343]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 3,
        duration: 255,
        duration_string: nil,
        id: 13,
        index: 3,
        inserted_at: ~N[2018-06-16 20:29:41.487989],
        number_of_plays: 0,
        title: "Gary's Theme",
        updated_at: ~N[2018-06-16 20:29:41.487996]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 3, 
        duration: 239,
        duration_string: nil,
        id: 14,
        index: 4,
        inserted_at: ~N[2018-06-16 20:29:41.488686],
        number_of_plays: 0,
        title: "We Will Meet Again (for Harry)",
        updated_at: ~N[2018-06-16 20:29:41.488695]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 3,
        duration: 360,
        duration_string: nil,
        id: 15,
        index: 5,
        inserted_at: ~N[2018-06-16 20:29:41.489335],
        number_of_plays: 0,
        title: "The Peacocks",
        updated_at: ~N[2018-06-16 20:29:41.489342]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 3,
        duration: 292,
        duration_string: nil,
        id: 16,
        index: 6,
        inserted_at: ~N[2018-06-16 20:29:41.490017],
        number_of_plays: 0,
        title: "Sometime Ago",
        updated_at: ~N[2018-06-16 20:29:41.490023]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 3, 
        duration: 353,
        duration_string: nil,
        id: 17,
        index: 7,
        inserted_at: ~N[2018-06-16 20:29:41.490832],
        number_of_plays: 0,
        title: "Theme From M*A*S*H (Suicide Is Painless)",
        updated_at: ~N[2018-06-16 20:29:41.490838]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 3,
        duration: 485,
        duration_string: nil,
        id: 18,
        index: 8,
        inserted_at: ~N[2018-06-16 20:29:41.491450],
        number_of_plays: 0,
        title: "Without a Song",
        updated_at: ~N[2018-06-16 20:29:41.491456]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 3,
        duration: 454,
        duration_string: nil,
        id: 19,
        index: 9,
        inserted_at: ~N[2018-06-16 20:29:41.492091],
        number_of_plays: 0,
        title: "Freddie Freeloader",
        updated_at: ~N[2018-06-16 20:29:41.492097]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 3,
        duration: 489,
        duration_string: nil,
        id: 20,
        index: 10,
        inserted_at: ~N[2018-06-16 20:29:41.492703],
        number_of_plays: 0,
        title: "All of You",
        updated_at: ~N[2018-06-16 20:29:41.492708]
      }
    ],
    updated_at: ~N[2018-06-16 20:29:41.483713]
  },
  %MusicDB.Album{
    __meta__: #Ecto.Schema.Metadata<:loaded, "albums">,
    artist: %MusicDB.Artist{
      __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
      albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
      birth_date: nil,
      death_date: nil,
      id: 2,
      inserted_at: ~N[2018-06-16 20:29:41.481934],
      name: "Bill Evans",
      tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
      updated_at: ~N[2018-06-16 20:29:41.481940]
    },
    artist_id: 2,
    genres: #Ecto.Association.NotLoaded<association :genres is not loaded>,
    id: 4,
    inserted_at: ~N[2018-06-16 20:29:41.493342],
    title: "Portrait In Jazz",
    tracks: [
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 4,
        duration: 204,
        duration_string: nil,
        id: 21,
        index: 1,
        inserted_at: ~N[2018-06-16 20:29:41.494540],
        number_of_plays: 0,
        title: "Come Rain Or Come Shine",
        updated_at: ~N[2018-06-16 20:29:41.494546]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 4,
        duration: 360,
        duration_string: nil,
        id: 22,
        index: 2,
        inserted_at: ~N[2018-06-16 20:29:41.495221],
        number_of_plays: 0,
        title: "Autumn Leaves",
        updated_at: ~N[2018-06-16 20:29:41.495227]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 4,
        duration: 277,
        duration_string: nil,
        id: 23,
        index: 3,
        inserted_at: ~N[2018-06-16 20:29:41.495758],
        number_of_plays: 0,
        title: "Witchcraft",
        updated_at: ~N[2018-06-16 20:29:41.495763]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 4,
        duration: 297,
        duration_string: nil,
        id: 24,
        index: 4,
        inserted_at: ~N[2018-06-16 20:29:41.496319],
        number_of_plays: 0,
        title: "When I Fall In Love",
        updated_at: ~N[2018-06-16 20:29:41.496325]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 4,
        duration: 195,
        duration_string: nil,
        id: 25,
        index: 5,
        inserted_at: ~N[2018-06-16 20:29:41.496865],
        number_of_plays: 0,
        title: "Peri's Scope",
        updated_at: ~N[2018-06-16 20:29:41.496871]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 4,
        duration: 276,
        duration_string: nil,
        id: 26,
        index: 6,
        inserted_at: ~N[2018-06-16 20:29:41.497420],
        number_of_plays: 0,
        title: "What Is This Thing Called Love?",
        updated_at: ~N[2018-06-16 20:29:41.497425]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 4,
        duration: 309,
        duration_string: nil,
        id: 27,
        index: 7,
        inserted_at: ~N[2018-06-16 20:29:41.497956],
        number_of_plays: 0,
        title: "Spring Is Here",
        updated_at: ~N[2018-06-16 20:29:41.497962]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 4,
        duration: 297,
        duration_string: nil,
        id: 28,
        index: 8,
        inserted_at: ~N[2018-06-16 20:29:41.498504],
        number_of_plays: 0,
        title: "Someday My Prince Will Come",
        updated_at: ~N[2018-06-16 20:29:41.498510]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 4,
        duration: 325,
        duration_string: nil,
        id: 29,
        index: 9,
        inserted_at: ~N[2018-06-16 20:29:41.499008],
        number_of_plays: 0,
        title: "Blue In Green",
        updated_at: ~N[2018-06-16 20:29:41.499013]
      }
    ],
    updated_at: ~N[2018-06-16 20:29:41.493348]
  },
  %MusicDB.Album{
    __meta__: #Ecto.Schema.Metadata<:loaded, "albums">,
    artist: %MusicDB.Artist{
      __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
      albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
      birth_date: nil,
      death_date: nil,
      id: 3,
      inserted_at: ~N[2018-06-16 20:29:41.500306],
      name: "Bobby Hutcherson",
      tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
      updated_at: ~N[2018-06-16 20:29:41.500311]
    },
    artist_id: 3,
    genres: #Ecto.Association.NotLoaded<association :genres is not loaded>,
    id: 5,
    inserted_at: ~N[2018-06-16 20:29:41.502041],
    title: "Live At Montreaux",
    tracks: [
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 5,
        duration: 761,
        duration_string: nil,
        id: 30,
        index: 1,
        inserted_at: ~N[2018-06-16 20:29:41.506102],
        number_of_plays: 0,
        title: "Anton's Ball",
        updated_at: ~N[2018-06-16 20:29:41.506109]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 5,
        duration: 647,
        duration_string: nil,
        id: 31,
        index: 2,
        inserted_at: ~N[2018-06-16 20:29:41.507174], 
        number_of_plays: 0,
        title: "The Moontrane",
        updated_at: ~N[2018-06-16 20:29:41.507181]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 5,
        duration: 805,
        duration_string: nil,
        id: 32,
        index: 3,
        inserted_at: ~N[2018-06-16 20:29:41.507927],
        number_of_plays: 0,
        title: "Farallone",
        updated_at: ~N[2018-06-16 20:29:41.507934]
      },
      %MusicDB.Track{
        __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
        album: #Ecto.Association.NotLoaded<association :album is not loaded>,
        album_id: 5,
        duration: 844,
        duration_string: nil,
        id: 33,
        index: 4,
        inserted_at: ~N[2018-06-16 20:29:41.508548],
        number_of_plays: 0,
        title: "Song Of Songs",
        updated_at: ~N[2018-06-16 20:29:41.508554]
      }
    ],
    updated_at: ~N[2018-06-16 20:29:41.502048]
  }
]
iex(14)> result = make_result.(rows)
[
  %MusicDB.Artist{
    __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
    albums: [
      %MusicDB.Album{
        __meta__: #Ecto.Schema.Metadata<:loaded, "albums">,
        artist: #Ecto.Association.NotLoaded<association :artist is not loaded>,
        artist_id: 3,
        genres: #Ecto.Association.NotLoaded<association :genres is not loaded>,
        id: 5, 
        inserted_at: ~N[2018-06-16 20:29:41.502041],
        title: "Live At Montreaux",
        tracks: [
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 5,
            duration: 761,
            duration_string: nil,
            id: 30,
            index: 1,
            inserted_at: ~N[2018-06-16 20:29:41.506102],
            number_of_plays: 0,
            title: "Anton's Ball",
            updated_at: ~N[2018-06-16 20:29:41.506109]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 5,
            duration: 647,
            duration_string: nil,
            id: 31,
            index: 2,
            inserted_at: ~N[2018-06-16 20:29:41.507174],
            number_of_plays: 0,
            title: "The Moontrane",
            updated_at: ~N[2018-06-16 20:29:41.507181]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 5,
            duration: 805,
            duration_string: nil,
            id: 32,
            index: 3,
            inserted_at: ~N[2018-06-16 20:29:41.507927],
            number_of_plays: 0,
            title: "Farallone",
            updated_at: ~N[2018-06-16 20:29:41.507934]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 5,
            duration: 844,
            duration_string: nil,
            id: 33,
            index: 4,
            inserted_at: ~N[2018-06-16 20:29:41.508548],
            number_of_plays: 0,
            title: "Song Of Songs",
            updated_at: ~N[2018-06-16 20:29:41.508554]
          }
        ],
        updated_at: ~N[2018-06-16 20:29:41.502048]
      }
    ],
    birth_date: nil,
    death_date: nil,
    id: 3,
    inserted_at: ~N[2018-06-16 20:29:41.500306],
    name: "Bobby Hutcherson",
    tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
    updated_at: ~N[2018-06-16 20:29:41.500311]
  },
  %MusicDB.Artist{
    __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
    albums: [
      %MusicDB.Album{
        __meta__: #Ecto.Schema.Metadata<:loaded, "albums">,
        artist: #Ecto.Association.NotLoaded<association :artist is not loaded>,
        artist_id: 2,
        genres: #Ecto.Association.NotLoaded<association :genres is not loaded>,
        id: 4,
        inserted_at: ~N[2018-06-16 20:29:41.493342],
        title: "Portrait In Jazz",
        tracks: [
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 4,
            duration: 204,
            duration_string: nil,
            id: 21,
            index: 1,
            inserted_at: ~N[2018-06-16 20:29:41.494540],
            number_of_plays: 0,
            title: "Come Rain Or Come Shine",
            updated_at: ~N[2018-06-16 20:29:41.494546]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 4,
            duration: 360,
            duration_string: nil,
            id: 22,
            index: 2,
            inserted_at: ~N[2018-06-16 20:29:41.495221],
            number_of_plays: 0,
            title: "Autumn Leaves",
            updated_at: ~N[2018-06-16 20:29:41.495227]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 4,
            duration: 277,
            duration_string: nil,
            id: 23,
            index: 3,
            inserted_at: ~N[2018-06-16 20:29:41.495758],
            number_of_plays: 0,
            title: "Witchcraft",
            updated_at: ~N[2018-06-16 20:29:41.495763] 
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 4,
            duration: 297,
            duration_string: nil,
            id: 24,
            index: 4,
            inserted_at: ~N[2018-06-16 20:29:41.496319],
            number_of_plays: 0,
            title: "When I Fall In Love",
            updated_at: ~N[2018-06-16 20:29:41.496325]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 4,
            duration: 195,
            duration_string: nil,
            id: 25,
            index: 5,
            inserted_at: ~N[2018-06-16 20:29:41.496865],
            number_of_plays: 0,
            title: "Peri's Scope",
            updated_at: ~N[2018-06-16 20:29:41.496871]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 4,
            duration: 276,
            duration_string: nil,
            id: 26,
            index: 6,
            inserted_at: ~N[2018-06-16 20:29:41.497420],
            number_of_plays: 0,
            title: "What Is This Thing Called Love?",
            updated_at: ~N[2018-06-16 20:29:41.497425]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 4,
            duration: 309,
            duration_string: nil,
            id: 27,
            index: 7,
            inserted_at: ~N[2018-06-16 20:29:41.497956],
            number_of_plays: 0,
            title: "Spring Is Here",
            updated_at: ~N[2018-06-16 20:29:41.497962]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 4,
            duration: 297,
            duration_string: nil,
            id: 28,
            index: 8,
            inserted_at: ~N[2018-06-16 20:29:41.498504],
            number_of_plays: 0,
            title: "Someday My Prince Will Come",
            updated_at: ~N[2018-06-16 20:29:41.498510]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 4,
            duration: 325,
            duration_string: nil,
            id: 29,
            index: 9,
            inserted_at: ~N[2018-06-16 20:29:41.499008],
            number_of_plays: 0,
            title: "Blue In Green",
            updated_at: ~N[2018-06-16 20:29:41.499013]
          }
        ],
        updated_at: ~N[2018-06-16 20:29:41.493348]
      },
      %MusicDB.Album{
        __meta__: #Ecto.Schema.Metadata<:loaded, "albums">,
        artist: #Ecto.Association.NotLoaded<association :artist is not loaded>,
        artist_id: 2,
        genres: #Ecto.Association.NotLoaded<association :genres is not loaded>,
        id: 3,
        inserted_at: ~N[2018-06-16 20:29:41.483682],
        title: "You Must Believe In Spring",
        tracks: [
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 3,
            duration: 192,
            duration_string: nil,
            id: 11,
            index: 1,
            inserted_at: ~N[2018-06-16 20:29:41.486309],
            number_of_plays: 0,
            title: "B Minor Waltz (for Ellaine)",
            updated_at: ~N[2018-06-16 20:29:41.486320]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 3,
            duration: 337,
            duration_string: nil,
            id: 12,
            index: 2,
            inserted_at: ~N[2018-06-16 20:29:41.487337],
            number_of_plays: 0,
            title: "You Must Believe In Spring",
            updated_at: ~N[2018-06-16 20:29:41.487343]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 3,
            duration: 255,
            duration_string: nil,
            id: 13,
            index: 3,
            inserted_at: ~N[2018-06-16 20:29:41.487989],
            number_of_plays: 0,
            title: "Gary's Theme",
            updated_at: ~N[2018-06-16 20:29:41.487996]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 3,
            duration: 239,
            duration_string: nil,
            id: 14,
            index: 4,
            inserted_at: ~N[2018-06-16 20:29:41.488686],
            number_of_plays: 0,
            title: "We Will Meet Again (for Harry)",
            updated_at: ~N[2018-06-16 20:29:41.488695]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 3,
            duration: 360,
            duration_string: nil,
            id: 15,
            index: 5,
            inserted_at: ~N[2018-06-16 20:29:41.489335],
            number_of_plays: 0,
            title: "The Peacocks",
            updated_at: ~N[2018-06-16 20:29:41.489342]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 3,
            duration: 292,
            duration_string: nil,
            id: 16,
            index: 6,
            inserted_at: ~N[2018-06-16 20:29:41.490017],
            number_of_plays: 0,
            title: "Sometime Ago",
            updated_at: ~N[2018-06-16 20:29:41.490023]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 3,
            duration: 353,
            duration_string: nil,
            id: 17,
            index: 7,
            inserted_at: ~N[2018-06-16 20:29:41.490832],
            number_of_plays: 0,
            title: "Theme From M*A*S*H (Suicide Is Painless)",
            updated_at: ~N[2018-06-16 20:29:41.490838]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 3,
            duration: 485,
            duration_string: nil,
            id: 18,
            index: 8,
            inserted_at: ~N[2018-06-16 20:29:41.491450],
            number_of_plays: 0,
            title: "Without a Song",
            updated_at: ~N[2018-06-16 20:29:41.491456]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 3,
            duration: 454,
            duration_string: nil,
            id: 19,
            index: 9,
            inserted_at: ~N[2018-06-16 20:29:41.492091],
            number_of_plays: 0,
            title: "Freddie Freeloader",
            updated_at: ~N[2018-06-16 20:29:41.492097]
          },
          %MusicDB.Track{
            __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
            album: #Ecto.Association.NotLoaded<association :album is not loaded>,
            album_id: 3,
            duration: 489,
            duration_string: nil,
            id: 20,
            index: 10,
            inserted_at: ~N[2018-06-16 20:29:41.492703],
            number_of_plays: 0,
            title: "All of You",
            updated_at: ~N[2018-06-16 20:29:41.492708]
          }
        ],
        updated_at: ~N[2018-06-16 20:29:41.483713]
      }
    ],
    birth_date: nil,
    death_date: nil,
    id: 2,
    inserted_at: ~N[2018-06-16 20:29:41.481934],
    name: "Bill Evans",
    tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
    updated_at: ~N[2018-06-16 20:29:41.481940]
  }
]
iex(15)>
1 Like
8

Breaking it down:

artists_limit = 2
albums_limit = 2
latest_artists_query = from(ar in Artist, [
  order_by: [desc: ar.inserted_at],
  limit: ^artists_limit
])

This query simply selects the %MusicDB.Artist{} of the most recently inserted artists. The intent is to use it for a subquery in

latest_albums_query = from(q in subquery(latest_artists_query), [
  left_lateral_join: lal in fragment("SELECT id FROM albums AS al WHERE al.artist_id = ? ORDER BY al.inserted_at DESC NULLS LAST LIMIT ?", q.id, ^albums_limit), on: true,
  join: al in Album, on: lal.id == al.id,
  select: al
])

This part of the query contains the lateral join to identify the two most recently added albums to the artists specified by the subquery. Because of the fragment this information can’t be correlated to the schemas known to Ecto.

  join: al in Album, on: lal.id == al.id,
  select: al

This join gets around that issue by joining against the albums table again - this time in a fashion that lets Ecto use %MusicDB.Album{} and because we want to use this query as a subquery we have to use an Ecto struct (list, maps, tuple aren’t supported for subqueries) which is accomplished with select: al (without it, only the %MusicDB.Artist{} would be returned). This means that in this pass we lose %MusicDB.Artist{} but as %MusicDB.Album{} has artist_id (and more importantly belongs_to(:artist, Artist)) we can get that back later.

query = from(p in subquery(latest_albums_query), [
  preload: [:tracks, :artist],
  order_by: [asc: :artist_id, asc: :inserted_at]
])

Now given that we have all the albums we want, we also want the child tracks and the parent artist. This query does this via Ecto’s Ecto.Query.preload/3 to load them as associations (strictly an Ecto feature, not SQL) with preload: [:tracks, :artist]. order_by: [asc: :artist_id, asc: :inserted_at] keeps all the %MusicDB.Album{} for the same artists “together” while ordering them in ascending order. This ordering will be reversed (i.e. descending) during “post load processing” which is the final ordering we are looking for.

Using the query now, Ecto will generate 3 separate SQL queries:

  1. To obtain the desired %MusicDB.Album{} values
  2. To obtain the necessary %MusicDB.Artist{} preload values
  3. To obtain the necessary %MusicDB.Track{} preload values

However the resulting shape of the data is like this:

[
  %MusicDB.Album{
    artist_id: 2,
    id: 3,
    artist: %MusicDB.Artist{id: 2, ...},
    tracks: [%MusicDB.Track{id: 11, ...}, ... %MusicDB.Track{id: 20, ...}]
    ...
  },
  %MusicDB.Album{
    artist_id: 2,
    id: 4,
    artist: %MusicDB.Artist{id: 2, ...},
    tracks: [%MusicDB.Track{id: 21, ...}, ... %MusicDB.Track{id: 29, ...}]
    ...
  },
  %MusicDB.Album{
    artist_id: 3,
    id: 5,
    artist: %MusicDB.Artist{id: 3, ...},
    tracks: [%MusicDB.Track{id: 30, ...}, ... %MusicDB.Track{id: 33, ...}]
    ...
  }
]

but the shape we are looking for is

[
  %MusicDB.Artist{
    id: 3,
    albums: [
      %MusicDB.Album{
        id: 5,
        artist_id: 3,
        tracks: [%MusicDB.Track{id: 30, ...}, ... %MusicDB.Track{id: 33, ...}],
        ...
      }
    ],
    ...
  },
  %MusicDB.Artist{
    id: 2,
    albums: [
      %MusicDB.Album{
        id: 4,
        artist_id: 2,
        tracks: [%MusicDB.Track{id: 21, ...}, ... %MusicDB.Track{id: 29, ...}],
        ...
      },
      %MusicDB.Album{
        id: 3,
        artist_id: 2,
        tracks: [%MusicDB.Track{id: 11, ...}, ... %MusicDB.Track{id: 20, ...}],
        ...
      }
    ],
    ...
  }
]

So some data transformation is still required

forget_assoc = fn ecto_struct, field, cardinality ->
  not_loaded = %Ecto.Association.NotLoaded{
    __field__: field,
    __owner__: ecto_struct.__struct__,
    __cardinality__: cardinality
  }
  Map.put(ecto_struct, field, not_loaded)
end
forget_artist = &(forget_assoc.(&1, :artist, :one))

forget_artist/1 is used to “unload” the artist association from %MusicDB.Album{} when we are done with it.

make_result = fn rows ->
  rows
  |> List.foldl({nil, nil, []}, row_to_acc)
  |> compact_to_rest.() # NOTE: could reverse artists after this
end

make_result/1 processes the loaded rows to produce the desired data shape. The accumulating data structure is {artist, [album], [artist_with_albums]} where artist is the %MusicDB.Artist{} currently being worked on, [album] is the list of %MusicDB.Albums{} for the current artist, and [artist_with_albums] is the list of already processed %MusicDB.Artist{} structs. The final compact_to_rest/1 is necessary to move the last artist and its [album] into [artist_with_albums].

compact_to_rest = fn
  {nil, nil, rest} ->
    rest
  {artist, albums, rest} ->
    [Map.put(artist, :albums, albums)|rest] # NOTE: could reverse albums here
end
row_to_acc = fn
  (%MusicDB.Album{artist: %MusicDB.Artist{id: id}} = a, {%MusicDB.Artist{id: id} = artist, albums, rest}) ->
    {artist, [forget_artist.(a)|albums], rest}
  (album, acc) ->
    {album.artist, [forget_artist.(album)], compact_to_rest.(acc)}
end

Support functions for make_result/1:

compact_to_rest1:

  • simply moves artist and its [album] into [artist_with_albums] and returns the new resulting [artist_with_albums]. OR
  • returns the current [artist_with_albums] if there is no artist (and [album]).

row_to_acc/2:

  • Adds the provided MusicDB.Album{} to [album] if its artist matches the current artist (after “unloading” the struct’s artist association). OR
  • “Compacts” the current artist and [album] into [album_with_artists], before setting the new “current” artist/[album] to album.artist/[forget_artist.(album)].

Note: this approach will lose artists without any albums. Those could be recovered with:

no_albums_artists_query = from(q in latest_artists_query, [
  left_join: al in Album, on: q.id == al.artist_id,
  where: is_nil(al.id)
])
no_albums_artists = Repo.all(no_albums_artists_query)

PS: Conceptually I’m still not entirely sold on whether it actually makes any sense to put anything but the complete set of associations inside an Ecto association given that it is an Ecto concept rather than something that emerges naturally from SQL.

While there may be practical reasons for wanting only some constrained subset of the complete set of associations:

  • associations seem to be part of Ecto’s mapping domain expressing the idea that this data is unconditionally associated with all this other data (the foreign key link either exists or it doesn’t - there is no gray area).
  • creating constrained subsets of anything is entirely within SQL’s domain.

So whenever something like LIMIT comes into play it may make more sense to avoid Ecto associations altogether and simply stick to Ecto(.Query)'s plain query functionality and then massage the loaded rows into the required shape via data transformations.

16 Likes
9

Hey, just wanted to pop in and say thank you very much for breaking this down!

I’ve not worked on the application for a few weeks so not had time to modify much. I’ll refactor once I’ve digested this excellent response. Appreciate it.

2 Likes