I looked through pagination questions and I haven’t found a generic solution to the problem of pagination.
I want offset/limit style pagination with the possibility to jump to a given page and scrivener works great for that.
The only problem was that for has_many associations it returned a number of rows instead of a number of top-level entities. E.g. If I had one blog post with two comments:
Post
|> join(:inner, [p], c in assoc(p.comments)
|> where([p, c], c.inserted_at > ^yesterday)
|> Repo.paginate()
Scrivener returns two entries.
I usually worked around the issue by something like this:
Post
|> join(:inner, [p], c in assoc(p.comments)
|> where([p, c], c.inserted_at > ^yesterday)
|> preload(:comments)
|> distinct([p, c], p.id)
|> Repo.paginate
This works great. E.g. I can filter by a secondary entity (comments in here) and the DB deduplicates results at the end. I am using binding-less preload to ask Ecto to preload comments in a separate query. This pattern worked so great that I added a helper with_pagination
.
def with_pagination(query) do
query
|> distinct([first_entity], first_entity.id)
|> Repo.paginate
end
However, there is a problem with order_by.
Post
|> order_by([p], [asc: p.inserted_at])
|> preload(:comments)
|> with_pagination()
doesn’t work because as documentation states distinct
function prepends whatever it gets to order_by
. So the final query is ordered by post.id Ecto.Query — Ecto v3.5.7
What I would like to do here is to extract the order_by
from query and prepend it to list from distinct.
def with_pagination(query) do
query
|> distinct([first_entity], __whatever is in query.order_bys__ ++ [first_entity.id])
|> Repo.paginate
end
I am not sure it is even possible because you can use distinct
only once in a query, query.order_bys is a list of QueryExpr structs and even if I extract [asc: p.inserted_at]
I am not sure how to build the dynamic query in such a way that Ecto knows, that p
is the same as first_entity
.
We’ve ended up passing order_by
arguments to with_pagination
but breaks the abstraction.
How do you deal with order_by
, has_many
and pagination at the same time?
Bonus question: why does distinct prepend its contents to order_by instead of the other way round? In my case the reverse would make more sense. Especially given that distinct doesn’t care about order of fields and order_by does. Prepending order_by fields to distinct (if distinct is set) should work.
CC: @michalmuskala