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!
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.
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.
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;
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.
Apparently you cannot. Sorry about that. I have pushed a fix to master.
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.
@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) association
Storage.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.
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
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)
But what about the WHERE
clause in the lateral join’s subquery? What would AS s1 ON TRUE
evaluate to without a WHERE
clause?
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.
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.
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)>
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.