Pagination with Ecto skipping page

Using GitHub - duffelhq/paginator: Cursor-based pagination for Elixir Ecto

I have a list of Event Entries, with order_by: [desc: entries.ror].

The query is:

  def list_event_entries(%Event{id: event_id}, %{limit: limit} = page_params) do
      entries in Entry,
      where: entries.event_id == ^event_id,
      order_by: [desc: entries.ror],
      preload: [:event, :user]
    |> Data.Pager.paginate([{:ror, :desc}], limit, page_params)

When paging the list (after_cursor), the second page is skipped because the rate of return for those entries is 0.0. When paging back (before_cursor) the second page is rendered correctly. I have a nearly identical, index component where order_by: [desc: entries.updated_at] and it works fine.

Page 1:

Click fwd arrow to page 2 but page 3 is rendered:

Click 'back arrow` and Page 2 is returned:

Any ideas what might be happening here that would cause the after_cursor paged results to not return entries where ror = 0.0 but the before_cursor does?

  • note ignore the total: 12, should be 13

The Paginator docs mention that a deterministic sort order is required; if you have multiple rows with ror == 0.0, sorting by that alone doesn’t satisfy that property.

A convenient unique column to break ties with is id; I assume that’s why it’s included in the documentation examples.

Ah, yes I missed the Caveats section of the docs!
simple adding order_by: [desc: entries.ror, asc:], and Data.Pager.paginate([{:ror, :desc}, {:id, :asc}], limit, page_params) solved the problem.