Last and Before includes cursor from previous page

[Niedman]

I'm using Cursor Based pagination and when i'm using first and after, the last element from the previous page is skipped as expected, however when i perform the last before operation the first element from the next page is actually moved to the last element of the current page. Is there a easy way of fixing this in Flop or we need to decode cursor and try to build a filter out of it?

Thanks in advance!

[woylie]

Hi @Niedman,

could you post the code I need to reproduce this?

[Niedman]

Hi @woylie

Sure :)

Query:

query Transactions {
    transactions(
        startDate: "2024-09-07T22:00:00.000Z"
        endDate: "2024-09-15T21:59:59.999Z"
        before: "g3QAAAABdwdjcmVhdGVkdAAAAAl3C21pY3Jvc2Vjb25kaAJhAGEAdwZzZWNvbmRhB3cIY2FsZW5kYXJ3E0VsaXhpci5DYWxlbmRhci5JU093BW1vbnRoYQl3Cl9fc3RydWN0X193FEVsaXhpci5OYWl2ZURhdGVUaW1ldwNkYXlhCXcEeWVhcmIAAAfodwZtaW51dGVhM3cEaG91cmEN"
        last: 3
    )

Decoded cursor from before:
{:ok, %{created: ~N[2024-09-09 13:51:07]}}

Params

 params = %{
      first: first,
      after: args[:after],
      before: args[:before],
      last: last,
      order_by: [sort_by_atom],
      order_directions: [sort_order],
      filters: filters
    }

Fetch Data

def list_charges(params \\ %{}) do

    IO.inspect(params)
    response = Flop.validate_and_run(Charge, params, for: Charge)

    case response do
      {:error, meta} ->
        meta

      {:ok, {charges, meta}} ->
        {charges, meta}
    end
  end

do you want me to post the iterations that i do?

I believe that behind the scenes Flop does a <= before and i guess what i´m looking for is to < before. that will exclude the repeated element.

[Niedman]

hey @woylie

I created a new dummy project from scratch because I was with a feeling that the problem could be from the table that i have.
In the new project i create a dummy table via migration and it works!
However when i create my table via SQL script in order to make the exact same table that i have working in prod and i use the same code as the one for the dummy table the before and last don't work as expected.

Do the cursors have a requirement in terms of constraints like primary keys??

I have the dummy code if you want to take a look.
https://github.com/Niedman/elixir-flop-filter

[woylie]

Hey @Niedman,

thanks for setting up the repo. I didn't have a look at it yet, but about the cursor: the combination of cursor fields (order-by fields) should be unique across the data set. There's a note about this in the Flop Phoenix documentation: https://hexdocs.pm/flop_phoenix/Flop.Phoenix.html#cursor_pagination/1-order-fields. I should probably put that somewhere in the Flop documentation as well.

(edit: Pinged myself. Should sleep more.)

[Niedman]

Hey @woylie
Ahh that explains it i believe. I've tried using the id and the sort works as expected! However i'm not sure how to tackle the issue then. The most important sort and the one that people will most likely use it(in my case) is the one per creation date. I cannot add a unique index into this one as I cannot guarantee that.

[woylie]

Hey @Niedman, the answer is in the linked documentation: You can sort by any number of non-unique columns, but to ensure that the cursor indeed is unique across the dataset, you should include the primary key or another unique column in the order-by columns. So instead of sorting by :created_at only, you'd need to sort by [:created_at, :id]. That would still give your users what they want, but ensure that cursor pagination works even if multiple rows share the same created_at value.

[woylie]

In your repo, you defined sort_by as a string argument. You can keep that. To avoid putting the burden on the API user, I'd add a helper function to handle this on the server side:

defp get_sort_by(%{sort_by: "id"}), do: [:id]
defp get_sort_by(%{sort_by: nil}), do: [:created_at, :id]
defp get_sort_by(%{sort_by: field}), do: [field, :id]

[Niedman]

Hey @woylie

Just to give you some feedback :)

I've tried the above approach, and initially didn't work. Not sure why because i actually had the created and id combined as per your function. Imo this should have worked as created and id indeed should create a unique combination, so not sure why it didn't worked. However, I give it a try and switched from :naive_datetime to :naive_datetime_usec to have into consideration the microseconds and it started working...

[woylie]

Hey @Niedman,

that sounds like the query doesn't actually sort by ID. Did you add the ID field to the sortable fields? Did you see an order by clause on both fields in the Ecto debug output? Does the cursor include both fields? (You can decode the cursor with https://hexdocs.pm/flop/Flop.Cursor.html#decode/1)

[Niedman]

Hey @woylie

Yes, I actually did that to understand if I using the both fields to sort or not. And the debug query also contained that's why I mentioned it.

It was something like this:

---
nil
-------------> Start Cursor: %{id: "po_3", created: ~N[2023-06-27 09:57:14]}
%{
  last: 2,
  order_by: [:created, :id],
  before: "g3QAAAACdwJpZG0AAAAEcG9fM3cHY3JlYXRlZHQAAAAJdwttaWNyb3NlY29uZGgCYQBhAHcGc2Vjb25kYQ53CGNhbGVuZGFydxNFbGl4aXIuQ2FsZW5kYXIuSVNPdwVtb250aGEGdwpfX3N0cnVjdF9fdxRFbGl4aXIuTmFpdmVEYXRlVGltZXcDZGF5YRt3BHllYXJiAAAH53cGbWludXRlYTl3BGhvdXJhCQ==",
  order_directions: [:desc]
}
[debug] QUERY OK source="Payouts" db=17.2ms idle=328.6ms
SELECT P0."id", P0."account_id", P0."currency", P0."created", P0."status" FROM "Payouts" AS P0 WHERE ((P0."created" >= $1::timestamp) AND ((P0."created" > $2::timestamp) OR (P0."id" < $3::varchar))) ORDER BY P0."created", P0."id" DESC LIMIT $4 [~N[2023-06-27 09:57:14], ~N[2023-06-27 09:57:14], "po_3", 3]