Repo.paginate using a cursor_field from a join fails because where is not aware of join

[samm81]

hi :)

I am trying to paginate over a query which contains a join.

query =
  from p in Product,
    left_join: pa in ProductAnalytics,
    on: pa.product_id == p.id,
    order_by: [
      desc: coalesce(pa.total_purchased, 0),
      desc: p.updated_at,
      desc: p.id
    ],
    select: %{
      id: p.id,
      updated_at: p.updated_at,
      total_purchased:
        coalesce(pa.total_purchased, 0),
      product: p
    }

I select all the cursor fields as a map so that the paginate function will have access to them, but in the end I'll only be interested in the Product, so I keep that around under the product key.

the paginate call:

page =
  Repo.paginate(
    query,
    include_total_count: true,
    limit: 10,
    cursor_fields: [
      {:total_purchased, :desc},
      {:updated_at, :desc},
      {:id, :desc}
    ]
  )

which works:

%Paginator.Page{
  metadata: %Paginator.Page.Metadata{
    after: "g3QAAAADZAACaWRiAAADJmQAD3RvdGFsX3B1cmNoYXNlZGEAZAAKdXBkYXRlZF9hdHQAAAAJZAAKX19zdHJ1Y3RfX2QAFEVsaXhpci5OYWl2ZURhdGVUaW1lZAAIY2FsZW5kYXJkABNFbGl4aXIuQ2FsZW5kYXIuSVNPZAADZGF5YQJkAARob3VyYRNkAAttaWNyb3NlY29uZGgCYQBhAGQABm1pbnV0ZWELZAAFbW9udGhhAWQABnNlY29uZGENZAAEeWVhcmIAAAfp",
    before: nil,
    limit: 1,
    total_count: 426,
    total_count_cap_exceeded: false
  },
  entries: [
    %{
      id: 806,
      product: %Product{
        ...

but if I try to get the next page:

Repo.paginate(
  query,
  include_total_count: true,
  after: page.metadata.after
  limit: 10,
  cursor_fields: [
    {:total_purchased, :desc},
    {:updated_at, :desc},
    {:id, :desc}
  ]
)

it fails

** (Ecto.QueryError) deps/paginator/lib/paginator/ecto/query/desc_nulls_first.ex:51: field `total_purchased` in `where` does not exist in schema Product in query:

from p0 in Product,
  left_join: p1 in ProductAnalytics,
  on: p1.product_id == p0.id,
  where: (p0.total_purchased == ^0 and
   ((p0.updated_at == ^~N[2025-01-02 19:11:13] and p0.id < ^798) or
      p0.updated_at < ^~N[2025-01-02 19:11:13])) or p0.total_purchased < ^0,
  order_by: [desc: coalesce(p1.total_purchased, 0), desc: p0.updated_at, desc: p0.id],
  limit: ^10,
  select: %{
  id: p0.id,
  updated_at: p0.updated_at,
  total_purchased: coalesce(p1.total_purchased, 0),
  product: p0
}

    (elixir 1.14.3) lib/enum.ex:2468: Enum."-reduce/3-lists^foldl/2-0-"/3
    (elixir 1.14.3) lib/enum.ex:1780: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir 1.14.3) lib/enum.ex:1780: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
    (elixir 1.14.3) lib/enum.ex:2468: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto 3.9.4) lib/ecto/repo/queryable.ex:211: Ecto.Repo.Queryable.execute/4
    (ecto 3.9.4) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (paginator 1.2.0) lib/paginator.ex:179: Paginator.paginate/4
    iex:28: (file)

we can see that in the where clause Paginator is assuming that the cursor_fields are all coming from p0, but it needs to be selecting from p1.

is there a way to do this that I'm not aware of? what would it take to add this functionality? it seems like if cursor_fields allowed for specifying the join it was operating on, that could work?