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
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
We’ve ended up passing
order_by arguments to
with_pagination but breaks the abstraction.
How do you deal with
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.