Preloading top comments for posts in Ecto

Doesn’t matter, I guess. The comments are being ordered by some popularity field right now, but it can be time (inserted_at?) as well. Is that what you mean? In that case (ordered by time) I’d like to get 5 latest comments for each post ( order_by: [desc: c.inserted_at]).

That would require a join at the least, probably a subquery too.

EDIT: On further thought you might be able to do it with a preload after-get query, but you’d have to order based on type and such, as well as handling the extra query data preload gives to the query…

This was asked before, but no clear answer was given in the thread. In the documentation for Ecto.Query.preload, there is a note at the bottom that says:

Note: keep in mind operations like limit and offset in the preload query will affect the whole result set and not each association.

What would be an effective way to get the top n results per post? A practical version of the question would be: how would I be able to get the 5 most recent comments for the 10 most recent posts?

The only two solutions I could think of is to either make multiple calls to get the 5 most recent comments of each post (which is essentially an n + 1 query – in this case 10 calls to get 5 comments a piece) or to create a custom SQL request to UNION ALL each query (thus knocking it down to a single database request for the comments, but seems a bit messy).

@josevalim or @michalmuskala, would you be able to help point me in the right direction?

Edit: This post was merged into the post that I linked above. I wasn’t sure what the rules were for necro’ing threads, so I made a new one. Sorry about that!

2 Likes

I am not sure what would be the best way to go here. Given it is not an Ecto problem, I would figure out what people recommend at the database level (regardless of the tool) and then once that is settled we can help “translate” it to Ecto if you have any problems.

1 Like

I’ll try to look into general solutions for SQL and see what I can find. This thread is about 7 months old, so maybe @idi527 can share the solution they found, if they found one.

The two solutions off the top of my head are the two I listed before:

A. Do the following request n numbers of times, determined by the number of posts returned by the original request. So, if the 10 most recent posts were fetched, this would need to be called 10 times (perhaps in an async loop?).

from comments in Comments,
where: [post_id: ^post_id],
order_by: [desc: :inserted_it],
limit: 5

B. This would essentially be the same request as the one above, but it would use UNION ALL to combine each one, cutting down the amount of requests to one. There is no built-in UNION ALL function in Ecto at the moment, so this would need to be done with the SQL adapter.

If anyone has ever tackled this problem before or knows a better way to approach this, please share. I’ll have to do some research to see what else I can find on my own until then.

1 Like

There are basically 2 ways to do this with posgres - one involves lateral join (supported by ecto) and subqueries in joins (not supported by ecto), another one involves windowing functions (not supported by ecto) and subqueries in from (supported by ecto).

Let’s assume following tables

CREATE TABLE posts (
  id serial PRIMARY KEY, 
  body TEXT
);
CREATE TABLE comments (
  id serial PRIMARY KEY, 
  body TEXT, 
  score INT,
  post_id serial REFERENCES posts (id)
);

We want to load 3 comments with best score for posts with ids 1, 2, 3.

Windowing function

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY score DESC) as rownum
  FROM comments
  WHERE post_id IN (1, 2, 3)
) a
WHERE rownum <= 3
ORDER BY post_id;

Lateral join + subquery

SELECT comments.* FROM posts
JOIN LATERAL (
  SELECT * FROM comments
  WHERE comments.posts_id = posts.id
  ORDER BY score DESC
  LIMIT 3
) comments ON true
WHERE posts.id IN (1, 2, 3)
ORDER BY posts.id;
12 Likes

Thanks, @michalmuskala!

I’ve been wanting to try it, but I can’t for the life of me figure out what keyword to use in the keyword syntax. The Ecto.Query.join/5 documentation only shows an example using piped expressions.

I looked at the pull request to add lateral joins, but the tests only use piped expressions as well. I tried going through the code to glean some information about how to use it, but nothing I’ve tried has worked. None of the qualifiers listed worked, I’ve tried things like lateral_join, join_lateral, left_lateral_join, etc…

@josevalim says that you can use the keyword syntax in the pull request, but I just can’t seem to figure it out.

2 Likes

Apparently you cannot. Sorry about that. I have pushed a fix to master.

3 Likes

Awesome! I just tried it out and it works as expected. Thank you to @josevalim for the incredibly fast update and @michalmuskala for being such a huge help.

2 Likes

@michalmuskala is it possible to also return fields from the lateral join in Ecto? For example, I have an account with many balances (timeseries) and I want to return the account name with the most recent balance. I cannot currently do this in Ecto, correct?

Schema

CREATE TABLE account (
  id serial PRIMARY KEY,
  name CHAR
);
CREATE TABLE balance (
  id serial PRIMARY KEY,
  date DATE,
  amount NUMERIC(12,2),
  account_id serial REFERENCES account (id)
);

SQL Query

SELECT a.name, b.amount 
FROM account AS a 
JOIN LATERAL (
  SELECT * 
  FROM balance AS b 
  WHERE b.account_id = a.id 
  ORDER BY date DESC 
  LIMIT 1
) b ON true;

Returns

   name   | amount
----------+---------
 Checking | 2858.51
 Savings  | 1307.17

This should be the Ecto equivalent

Account
|> join(:inner_lateral, [a], b in fragment("SELECT * FROM balance WHERE account_id = ? ORDER BY date DESC LIMIT 1", a.id))
|> preload([a, b], [balance: b])
|> Repo.all()

but it raises:

** (Ecto.QueryError) associationStorage.Schema.Account.balancein preload requires an inner or left join, got inner_lateral join in query:

Preloading from a fragment is not supported even with join or left_join, so adding support for this, would require adding support for preloading from fragment joins first.

1 Like

Cool thanks!

Hi @steve , would you share your working code here? I can understand @michalmuskala’s SQL, but I don’t know how to translate that into ecto query. I think it might be helpful to other newbies like me.

Maybe https://hexdocs.pm/ecto/Ecto.Query.html#join/5-joining-with-fragments would help.

I guess

SELECT comments.* FROM posts
JOIN LATERAL (
  SELECT * FROM comments
  WHERE comments.posts_id = posts.id
  ORDER BY score DESC
  LIMIT 3
) comments ON true
WHERE posts.id IN (1, 2, 3)
ORDER BY posts.id;

might be rewritten with ecto as

defmacrop last_comments(post_id, limit \\ 3) do
  quote do
    fragment(
      "SELECT * FROM comments WHERE comments.post_id = ? ORDER BY score DESC LIMIT ?",
      unquote(post_id),
      unquote(limit)
    )
  end
end

@spec list_some_posts_with_latest_comment :: [Post.t()]
def list_some_posts_with_latest_comment do
  import Ecto.Query

  Post
  |> where([p], p.id in [1, 2, 3])
  |> order_by([:id])
  |> join(:inner_lateral, [p], c in last_comments(p.id))
  |> select([p, c], %{p | last_comments: c})
  |> Repo.all()
end
2 Likes

Another way of exploring this:

$ 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)> import Ecto.Query
Ecto.Query
iex(2)> repo = MusicDB.Repo # ANY connected repo (this one from "Programming Ecto")
MusicDB.Repo
iex(3)> post_ids = [1,2,3]
[1, 2, 3]
iex(4)> sq =
...(4)>   from("comments", [
...(4)>     select: [:id, :body, :score, :post_id],
...(4)>     order_by: [desc: :score],
...(4)>     limit: 3
...(4)>   ])
#Ecto.Query<from c in "comments", order_by: [desc: c.score], limit: 3,
 select: [:id, :body, :score, :post_id]>
iex(5)> query =
...(5)>   from(p in "posts",[
...(5)>     inner_lateral_join: c in subquery(sq),
...(5)>     select: [c],
...(5)>     where: p.id in ^post_ids,
...(5)>     order_by: [asc: p.id]
...(5)>   ])
#Ecto.Query<from p in "posts",
 join_lateral: c in subquery(from c in "comments",
  order_by: [desc: c.score],
  limit: 3,
  select: [:id, :body, :score, :post_id]),
 on: true, where: p.id in ^[1, 2, 3], order_by: [asc: p.id], select: [c]>
iex(6)> Ecto.Adapters.SQL.to_sql(:all, repo, query)
{"SELECT s1.\"id\", s1.\"body\", s1.\"score\", s1.\"post_id\" FROM \"posts\" AS p0 INNER JOIN LATERAL (SELECT c0.\"id\" AS \"id\", c0.\"body\" AS \"body\", c0.\"score\" AS \"score\", c0.\"post_id\" AS \"post_id\" FROM \"comments\" AS c0 ORDER BY c0.\"score\" DESC LIMIT 3) AS s1 ON TRUE WHERE (p0.\"id\" = ANY($1)) ORDER BY p0.\"id\"",
 [[1, 2, 3]]}
iex(7)> 

i.e.

"SELECT 
   s1.\"id\", 
   s1.\"body\", 
   s1.\"score\", 
   s1.\"post_id\" 
FROM \"posts\" AS p0 
INNER JOIN LATERAL (
  SELECT 
    c0.\"id\" AS \"id\", 
    c0.\"body\" AS \"body\", 
    c0.\"score\" AS \"score\", 
    c0.\"post_id\" AS \"post_id\" 
  FROM \"comments\" AS c0 
    ORDER BY c0.\"score\" DESC 
    LIMIT 3
) AS s1 ON TRUE 
WHERE 
  (p0.\"id\" = ANY($1)) 
  ORDER BY p0.\"id\""

Ecto.Adapters.SQL.to_sql/3 - also available on any repo as Repo.to_sql(kind, queryable)

1 Like

But what about the WHERE clause in the lateral join’s subquery? What would AS s1 ON TRUE evaluate to without a WHERE clause?

1 Like

There is the hackey way:

$ 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)> import Ecto.Query
Ecto.Query
iex(2)> repo = MusicDB.Repo
MusicDB.Repo
iex(3)> sq =
...(3)>   from(c in "comments", [
...(3)>     select: [c.id, c.body, c.score, c.post_id],
...(3)>     where: fragment("? = p0.\"id\"", c.post_id),
...(3)>     order_by: [desc: c.score],
...(3)>     limit: 3
...(3)>   ])
#Ecto.Query<from c in "comments", where: fragment("? = p0.\"id\"", c.post_id),
 order_by: [desc: c.score], limit: 3,
 select: [c.id, c.body, c.score, c.post_id]>
iex(4)> repo.to_sql(:all,sq)
{"SELECT c0.\"id\", c0.\"body\", c0.\"score\", c0.\"post_id\" FROM \"comments\" AS c0 WHERE (c0.\"post_id\" = p0.\"id\") ORDER BY c0.\"score\" DESC LIMIT 3",
 []} 
iex(5)> 

Though I’m sure this would be discouraged to the extreme as this is coupled to the implementation of how Ecto generates table aliases which could change without notice.

1 Like

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.

2 Likes

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

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