I think a correlated subquery is easier to control (though possibly less performant). Example:
$ iex -S mix
Erlang/OTP 20 [erts-9.3.1] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:10] [hipe] [kernel-poll:false] [dtrace]
Interactive Elixir (1.6.5) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> alias MusicDB.{Repo,Album,Tracks}
[MusicDB.Repo, MusicDB.Album, MusicDB.Tracks]
iex(2)> album_ids = [1,2,3]
[1, 2, 3]
iex(3)> query = from(a in Album, [
...(3)> join: t in Track, on: a.id == t.album_id,
...(3)> where: a.id in ^album_ids,
...(3)> where: t.id in fragment("SELECT t_f1.id FROM tracks AS t_f1 WHERE t_f1.album_id = ? ORDER BY t_f1.index DESC LIMIT 3", a.id),
...(3)> order_by: [asc: a.id, desc: t.index],
...(3)> select: t
...(3)> ])
#Ecto.Query<from a in MusicDB.Album, join: t in MusicDB.Track,
on: a.id == t.album_id, where: a.id in ^[1, 2, 3],
where: t.id in fragment("SELECT t_f1.id FROM tracks AS t_f1 WHERE t_f1.album_id = ? ORDER BY t_f1.index DESC LIMIT 3", a.id),
order_by: [asc: a.id, desc: t.index], select: t>
iex(4)> Repo.all(query)
13:55:47.460 [debug] QUERY OK source="albums" db=4.8ms
SELECT t1."id", t1."title", t1."duration", t1."index", t1."number_of_plays", t1."inserted_at", t1."updated_at", t1."album_id" FROM "albums" AS a0 INNER JOIN "tracks" AS t1 ON a0."id" = t1."album_id" WHERE (a0."id" = ANY($1)) AND (t1."id" = ANY(SELECT t_f1.id FROM tracks AS t_f1 WHERE t_f1.album_id = a0."id" ORDER BY t_f1.index DESC LIMIT 3)) ORDER BY a0."id", t1."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(5)>
https://pragprog.com/titles/wmecto/source_code
As such LATERAL (view/join) seems to be largely used as a RDBMS engine specific optimization rather than a generic SQL concept.