Preloading top comments for posts in Ecto

Questions / Help
22

D’oh!

iex(1)> alias MusicDB.{Repo,Album,Tracks}
[MusicDB.Repo, MusicDB.Album, MusicDB.Tracks]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> album_ids = [1,2,3]
[1, 2, 3]
iex(4)> query = from(a in Album, [
...(4)>   inner_lateral_join: t in fragment("SELECT * FROM tracks WHERE album_id = ? ORDER BY index DESC LIMIT 3", a.id),
...(4)>   where: a.id in ^album_ids,
...(4)>   order_by: [asc: a.id],
...(4)>   select: %MusicDB.Track{id: t.id, album_id: t.album_id, index: t.index, title: t.title, duration: t.duration, number_of_plays: t.number_of_plays, inserted_at: type(t.inserted_at,:naive_datetime), updated_at: type(t.updated_at,:naive_datetime)}
...(4)> ])
#Ecto.Query<from a in MusicDB.Album,
 join_lateral: f in fragment("SELECT * FROM tracks WHERE album_id = ? ORDER BY index DESC LIMIT 3", a.id),
 on: true, where: a.id in ^[1, 2, 3], order_by: [asc: a.id],
 select: %MusicDB.Track{id: f.id, album_id: f.album_id, index: f.index, title: f.title, duration: f.duration, number_of_plays: f.number_of_plays, inserted_at: type(f.inserted_at, :naive_datetime), updated_at: type(f.updated_at, :naive_datetime)}>
iex(5)> Repo.all(query)

14:39:21.742 [debug] QUERY OK source="albums" db=4.8ms
SELECT f1."id", f1."album_id", f1."index", f1."title", f1."duration", f1."number_of_plays", f1."inserted_at"::timestamp, f1."updated_at"::timestamp FROM "albums" AS a0 INNER JOIN LATERAL (SELECT * FROM tracks WHERE album_id = a0."id" ORDER BY index DESC LIMIT 3) AS f1 ON TRUE WHERE (a0."id" = ANY($1)) ORDER BY a0."id" [[1, 2, 3]]
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 481,
    duration_string: nil,
    id: 5,
    index: 5,
    inserted_at: ~N[2018-06-05 20:27:48.251456],
    number_of_plays: 0,
    title: "Flamenco Sketches",
    updated_at: ~N[2018-06-05 20:27:48.251460]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 693,
    duration_string: nil,
    id: 4,
    index: 4,
    inserted_at: ~N[2018-06-05 20:27:48.250970],
    number_of_plays: 0,
    title: "All Blues",
    updated_at: ~N[2018-06-05 20:27:48.250975]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 327,
    duration_string: nil,
    id: 3,
    index: 3,
    inserted_at: ~N[2018-06-05 20:27:48.250395],
    number_of_plays: 0, 
    title: "Blue In Green",
    updated_at: ~N[2018-06-05 20:27:48.250400]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1061,
    duration_string: nil,
    id: 10,
    index: 5,
    inserted_at: ~N[2018-06-05 20:27:48.255063],
    number_of_plays: 0,
    title: "No Blues",
    updated_at: ~N[2018-06-05 20:27:48.255068]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 754,
    duration_string: nil,
    id: 9,
    index: 4,
    inserted_at: ~N[2018-06-05 20:27:48.254648],
    number_of_plays: 0,
    title: "Miles",
    updated_at: ~N[2018-06-05 20:27:48.254652]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 896,
    duration_string: nil,
    id: 8,
    index: 3,
    inserted_at: ~N[2018-06-05 20:27:48.254241],
    number_of_plays: 0,
    title: "Walkin'",
    updated_at: ~N[2018-06-05 20:27:48.254245]
  },
  %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-05 20:27:48.264357],
    number_of_plays: 0,
    title: "All of You",
    updated_at: ~N[2018-06-05 20:27:48.264361]
  },
  %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-05 20:27:48.263899],
    number_of_plays: 0,
    title: "Freddie Freeloader",
    updated_at: ~N[2018-06-05 20:27:48.263903]
  },
  %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-05 20:27:48.263477],
    number_of_plays: 0,
    title: "Without a Song",
    updated_at: ~N[2018-06-05 20:27:48.263481]
  }
]
iex(6)>

or more conveniently:

iex(1)> alias MusicDB.{Repo,Album,Tracks}
[MusicDB.Repo, MusicDB.Album, MusicDB.Tracks]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> album_ids = [1,2,3]
[1, 2, 3]
iex(4)> query = from(a in Album, [
...(4)>   inner_lateral_join: ljt in fragment("SELECT id FROM tracks WHERE album_id = ? ORDER BY index DESC LIMIT 3", a.id),
...(4)>   inner_join: t in Track, on: ljt.id == t.id,
...(4)>   where: a.id in ^album_ids,
...(4)>   order_by: [asc: a.id, desc: t.index],
...(4)>   select: t
...(4)> ])
#Ecto.Query<from a in MusicDB.Album,
 join_lateral: f in fragment("SELECT id FROM tracks WHERE album_id = ? ORDER BY index DESC LIMIT 3", a.id),
 on: true, join: t in MusicDB.Track, on: f.id == t.id,
 where: a.id in ^[1, 2, 3], order_by: [asc: a.id, desc: t.index], select: t>
iex(5)> Repo.all(query)

15:34:56.753 [debug] QUERY OK source="albums" db=4.1ms
SELECT t2."id", t2."title", t2."duration", t2."index", t2."number_of_plays", t2."inserted_at", t2."updated_at", t2."album_id" FROM "albums" AS a0 INNER JOIN LATERAL (SELECT id FROM tracks WHERE album_id = a0."id" ORDER BY index DESC LIMIT 3) AS f1 ON TRUE INNER JOIN "tracks" AS t2 ON f1."id" = t2."id" WHERE (a0."id" = ANY($1)) ORDER BY a0."id", t2."index" DESC [[1, 2, 3]]
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 481,
    duration_string: nil,
    id: 5,
    index: 5,
    inserted_at: ~N[2018-06-05 20:27:48.251456],
    number_of_plays: 0,
    title: "Flamenco Sketches",
    updated_at: ~N[2018-06-05 20:27:48.251460]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 693,
    duration_string: nil,
    id: 4,
    index: 4,
    inserted_at: ~N[2018-06-05 20:27:48.250970],
    number_of_plays: 0,
    title: "All Blues",
    updated_at: ~N[2018-06-05 20:27:48.250975]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 327,
    duration_string: nil,
    id: 3,
    index: 3,
    inserted_at: ~N[2018-06-05 20:27:48.250395],
    number_of_plays: 0, 
    title: "Blue In Green",
    updated_at: ~N[2018-06-05 20:27:48.250400]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1061,
    duration_string: nil,
    id: 10,
    index: 5,
    inserted_at: ~N[2018-06-05 20:27:48.255063],
    number_of_plays: 0,
    title: "No Blues",
    updated_at: ~N[2018-06-05 20:27:48.255068]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 754,
    duration_string: nil,
    id: 9,
    index: 4,
    inserted_at: ~N[2018-06-05 20:27:48.254648],
    number_of_plays: 0,
    title: "Miles",
    updated_at: ~N[2018-06-05 20:27:48.254652]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 896,
    duration_string: nil,
    id: 8,
    index: 3,
    inserted_at: ~N[2018-06-05 20:27:48.254241],
    number_of_plays: 0,
    title: "Walkin'",
    updated_at: ~N[2018-06-05 20:27:48.254245]
  },
  %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-05 20:27:48.264357],
    number_of_plays: 0,
    title: "All of You",
    updated_at: ~N[2018-06-05 20:27:48.264361]
  },
  %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-05 20:27:48.263899],
    number_of_plays: 0,
    title: "Freddie Freeloader",
    updated_at: ~N[2018-06-05 20:27:48.263903]
  },
  %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-05 20:27:48.263477],
    number_of_plays: 0,
    title: "Without a Song",
    updated_at: ~N[2018-06-05 20:27:48.263481]
  }
]
iex(6)>

And alternately:

iex(1)> alias MusicDB.{Repo,Album,Tracks}
[MusicDB.Repo, MusicDB.Album, MusicDB.Tracks]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> album_ids = [1,2,3]
[1, 2, 3]
iex(4)> query = from(t in Track, [
...(4)>   inner_join: tp in fragment("SELECT id, ROW_NUMBER() OVER (PARTITION BY album_id ORDER BY index DESC) AS rownum FROM tracks WHERE album_id = ANY(?)", ^album_ids), on: t.id == tp.id,
...(4)>   where: tp.rownum <= 3,
...(4)>   order_by: [asc: t.album_id, desc: t.index],
...(4)>   select: t
...(4)> ])
#Ecto.Query<from t in MusicDB.Track,
 join: f in fragment("SELECT id, ROW_NUMBER() OVER (PARTITION BY album_id ORDER BY index DESC) AS rownum FROM tracks WHERE album_id = ANY(?)", ^[1, 2, 3]),
 on: t.id == f.id, where: f.rownum <= 3,
 order_by: [asc: t.album_id, desc: t.index], select: t>
iex(5)> Repo.all(query)

19:59:20.217 [debug] QUERY OK source="tracks" db=3.7ms decode=2.1ms
SELECT t0."id", t0."title", t0."duration", t0."index", t0."number_of_plays", t0."inserted_at", t0."updated_at", t0."album_id" FROM "tracks" AS t0 INNER JOIN (SELECT id, ROW_NUMBER() OVER (PARTITION BY album_id ORDER BY index DESC) AS rownum FROM tracks WHERE album_id = ANY($1)) AS f1 ON t0."id" = f1."id" WHERE (f1."rownum" <= 3) ORDER BY t0."album_id", t0."index" DESC [[1, 2, 3]]
[
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 481,
    duration_string: nil,
    id: 5,
    index: 5,
    inserted_at: ~N[2018-06-05 20:27:48.251456],
    number_of_plays: 0,
    title: "Flamenco Sketches",
    updated_at: ~N[2018-06-05 20:27:48.251460]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 693,
    duration_string: nil,
    id: 4,
    index: 4,
    inserted_at: ~N[2018-06-05 20:27:48.250970],
    number_of_plays: 0,
    title: "All Blues",
    updated_at: ~N[2018-06-05 20:27:48.250975]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 1,
    duration: 327,
    duration_string: nil,
    id: 3,
    index: 3,
    inserted_at: ~N[2018-06-05 20:27:48.250395],
    number_of_plays: 0, 
    title: "Blue In Green",
    updated_at: ~N[2018-06-05 20:27:48.250400]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 1061,
    duration_string: nil,
    id: 10,
    index: 5,
    inserted_at: ~N[2018-06-05 20:27:48.255063],
    number_of_plays: 0,
    title: "No Blues",
    updated_at: ~N[2018-06-05 20:27:48.255068]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 754,
    duration_string: nil,
    id: 9,
    index: 4,
    inserted_at: ~N[2018-06-05 20:27:48.254648],
    number_of_plays: 0,
    title: "Miles",
    updated_at: ~N[2018-06-05 20:27:48.254652]
  },
  %MusicDB.Track{
    __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
    album: #Ecto.Association.NotLoaded<association :album is not loaded>,
    album_id: 2,
    duration: 896,
    duration_string: nil,
    id: 8,
    index: 3,
    inserted_at: ~N[2018-06-05 20:27:48.254241],
    number_of_plays: 0,
    title: "Walkin'",
    updated_at: ~N[2018-06-05 20:27:48.254245]
  },
  %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-05 20:27:48.264357],
    number_of_plays: 0,
    title: "All of You",
    updated_at: ~N[2018-06-05 20:27:48.264361]
  },
  %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-05 20:27:48.263899],
    number_of_plays: 0,
    title: "Freddie Freeloader",
    updated_at: ~N[2018-06-05 20:27:48.263903]
  },
  %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-05 20:27:48.263477],
    number_of_plays: 0,
    title: "Without a Song",
    updated_at: ~N[2018-06-05 20:27:48.263481]
  }
]
iex(6)>
2 Likes
23

peerreynders

Thanks for taking the time to show all of the options here. This has got me out of a big hole today and I’m in your debt for that.

People like you are making development a better place, I really appreciate it.

3 Likes