Dealing with cursors when data has been updated

[m1dnight]

Hi,

We're using cursor-based pagination for large tables. Each page shows 3 results and each row has a button that allows the user to delete the row.

In the situation where a row has been deleted, and it was the only row on the last page, the pagination fails.

When the last page with the single row is requested and the single item is present, this is what the meta-field looks like:

%Flop.Meta{
  backend: nil,
  current_offset: nil,
  current_page: nil,
  # %{date_due: ~D[2023-06-21], id: 206417}
  end_cursor:
    "g3QAAAACZAAIZGF0ZV9kdWV0AAAABWQACl9fc3RydWN0X19kAAtFbGl4aXIuRGF0ZWQACGNhbGVuZGFyZAATRWxpeGlyLkNhbGVuZGFyLklTT2QAA2RheWEVZAAFbW9udGhhBmQABHllYXJiAAAH52QAAmlkYgADJlE=",
  next_offset: nil,
  next_page: nil,
  page_size: 3,
  previous_offset: nil,
  previous_page: nil,
  schema: Justified.Action,
  # %{date_due: ~D[2023-06-21], id: 206417}
  start_cursor:
    "g3QAAAACZAAIZGF0ZV9kdWV0AAAABWQACl9fc3RydWN0X19kAAtFbGl4aXIuRGF0ZWQACGNhbGVuZGFyZAATRWxpeGlyLkNhbGVuZGFyLklTT2QAA2RheWEVZAAFbW9udGhhBmQABHllYXJiAAAH52QAAmlkYgADJlE=",
  total_count: nil,
  total_pages: nil,
  errors: [],
  flop: %Flop{
    # %{date_due: ~D[2023-06-08], id: 205843}
    after:
      "g3QAAAACZAAIZGF0ZV9kdWV0AAAABWQACl9fc3RydWN0X19kAAtFbGl4aXIuRGF0ZWQACGNhbGVuZGFyZAATRWxpeGlyLkNhbGVuZGFyLklTT2QAA2RheWEIZAAFbW9udGhhBmQABHllYXJiAAAH52QAAmlkYgADJBM=",
    before: nil,
    first: 3,
    last: nil,
    limit: nil,
    offset: nil,
    order_by: [:date_due, :id],
    order_directions: [:asc, :asc],
    page: nil,
    page_size: nil,
    filters: [%Flop.Filter{field: :searchable, op: :like, value: "xw"}]
  },
  has_next_page?: false,
  has_previous_page?: true,
  opts: [for: Justified.Action, default_pagination_type: :first],
  params: %{}
}

The after cursor contains the date_due and id of the last item on the previous page, which makes sense.
The start_cursor and end_cursor contain the id and date_dueof the only item on the last page.

When a user now deletes this item, on the last page and I request the new page in the controller, I get zero results back because the only after the cursor has been deleted, and because there are no items, there is no start_cursor or end_cursor.

This causes the Flop.Phoenix.cursor_pagination function to fail with the below error.

no function clause matching in Flop.to_previous_cursor/1

I'm wondering why, because if you know the after cursor, then it should be possible to get the 3 values right before the after cursor too, right?

Any help is greatly appreciated.

• Christophe

I made a small MWE based on the documentation with Pets. You can find it here: https://github.com/m1dnight/flop_bug/blob/pagination_mwe/.iex.exs

The exampl shows two pages; one page with 2 two pets (Bunny and Cat), and the second page with one pet, Dog.

If you delete the last pet, but you still have a cursor from the first page to the second page that page will load, but show 0 pets. If you then try to get the previous cursor from that page it will fail with the following error:

16:24 christophe@Christophes-MacBook-Pro ~/Documents/Work/flop_bug (pagination_mwe)$ iex -S mix 
Erlang/OTP 25 [erts-13.2] [source] [64-bit] [smp:10:10] [ds:10:10:10] [async-threads:1] [jit]

Interactive Elixir (1.14.4) - press Ctrl+C to exit (type h() ENTER for help)

16:28:09.502 [debug] QUERY OK source="pets" db=0.9ms decode=0.7ms queue=51.5ms idle=0.0ms
DELETE FROM "pets" AS p0 []
 
16:28:09.534 [debug] QUERY OK db=4.3ms queue=3.4ms idle=30.7ms
INSERT INTO "pets" ("age","name","species","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" [1, "BunnyMcBunnyface", "labrabunny", ~N[2023-05-05 14:28:09], ~N[2023-05-05 14:28:09]]
 
16:28:09.539 [debug] QUERY OK db=2.5ms queue=2.3ms idle=41.4ms
INSERT INTO "pets" ("age","name","species","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" [1, "CattyMcCatface", "labracat", ~N[2023-05-05 14:28:09], ~N[2023-05-05 14:28:09]]
 
16:28:09.543 [debug] QUERY OK db=1.1ms queue=2.9ms idle=46.5ms
INSERT INTO "pets" ("age","name","species","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id" [1, "DoggyMcDogface", "labradoodle", ~N[2023-05-05 14:28:09], ~N[2023-05-05 14:28:09]]
 
16:28:09.556 [debug] QUERY OK source="pets" db=0.9ms queue=1.8ms idle=60.7ms
SELECT p0."id", p0."name", p0."age", p0."species", p0."inserted_at", p0."updated_at" FROM "pets" AS p0 ORDER BY p0."name" LIMIT $1 [3]
Page 1: BunnyMcBunnyface, CattyMcCatface

16:28:09.584 [debug] QUERY OK source="pets" db=2.7ms queue=5.2ms idle=83.4ms
SELECT p0."id", p0."name", p0."age", p0."species", p0."inserted_at", p0."updated_at" FROM "pets" AS p0 WHERE (p0."name" = 'DoggyMcDogface') []
 
16:28:09.596 [debug] QUERY OK db=6.7ms queue=3.4ms idle=93.3ms
DELETE FROM "pets" WHERE "id" = $1 [36]
 
16:28:09.608 [debug] QUERY OK source="pets" db=2.3ms queue=2.7ms idle=110.1ms
SELECT p0."id", p0."name", p0."age", p0."species", p0."inserted_at", p0."updated_at" FROM "pets" AS p0 WHERE (p0."name" > $1) ORDER BY p0."name" LIMIT $2 ["CattyMcCatface", 3]
Page 2: 
Error while evaluating: /Users/christophe/Documents/Work/flop_bug/.iex.exs
** (FunctionClauseError) no function clause matching in Flop.to_previous_cursor/1    
    
    The following arguments were given to Flop.to_previous_cursor/1:
    
        # 1
        %Flop.Meta{
          backend: nil,
          current_offset: nil,
          current_page: nil,
          end_cursor: nil,
          next_offset: nil,
          next_page: nil,
          page_size: 2,
          previous_offset: nil,
          previous_page: nil,
          schema: FlopTest.Pet,
          start_cursor: nil,
          total_count: nil,
          total_pages: nil,
          errors: [],
          flop: %Flop{
            after: "g3QAAAABZAAEbmFtZW0AAAAOQ2F0dHlNY0NhdGZhY2U=",
            before: nil,
            first: 2,
            last: nil,
            limit: nil,
            offset: nil,
            order_by: [:name],
            order_directions: [:asc],
            page: nil,
            page_size: nil,
            filters: []
          },
          has_next_page?: false,
          has_previous_page?: true,
          opts: [for: FlopTest.Pet, default_pagination_type: :first],
          params: %{}
        }
    
    Attempted function clauses (showing 2 out of 2):
    
        def to_previous_cursor(%Flop.Meta{flop: flop, has_previous_page?: false})
        def to_previous_cursor(%Flop.Meta{flop: flop, has_previous_page?: true, start_cursor: start_cursor}) when is_binary(start_cursor)
    
    (flop 0.20.0) lib/flop.ex:1694: Flop.to_previous_cursor/1
    /Users/christophe/Documents/Work/flop_bug/.iex.exs:58: (file)

[woylie]

The start_cursor and end_cursor in the Flop.Meta struct are derived from the first row and the last row in the result set respectively. If the result set is empty, the start cursor and end cursor will be nil.

When you go to the next page, the end cursor (derived from the last row) becomes the after value for the Flop struct. The new start cursor will be derived from the first row of the new result set. Would you use the after value as the new before value to go to the previous page, you would skip one row. You can see this by changing line 58 in your example to flop_page_1 = Flop.to_previous_cursor(%{meta_page_2 | start_cursor: meta_page_2.flop.after}). The output will be Page 1: BunnyMcBunnyface. CattyMcCatface will be skipped.

The to_previous_cursor and to_next_cursor functions should not raise an error under these circumstances. I'll open a PR later to address this. We have two options:

1. Reset the before and after values in that case, which would send the user back to page 1.
2. Use the after value as the before value, which would skip the last item on the previous page as described above.
3. Switch from first to last without setting before, in which case the last x items will be shown without skipping any.

In case the current user deletes an item from the list, you can handle deletions by:

• querying the items for the current page with the same parameters again after the deletion if more than one item is displayed
• querying the items for the previous page by calling to_previous_page on the meta for the current page after deleting the item if the only item on a page is deleted

In case an item is deleted without the doing of the current user, you'll have to live with the implications above, unless you want to put in the effort of publishing deletions on Phoenix PubSub and subscribing the LiveView to those events.

[woylie]

#333 addresses the function clause error. You can go from an empty last page back to the previous page now.

If you want to detect the condition and show a notice to the user instead of rendering an empty page with pagination, you can check whether the result list is empty and either has_previous_page? (You went past the last page!) or has_next_page? (You went past the first page!) are true.

[m1dnight]

This has been resolved in a PR, and Ive got it fixed in production. I appreciate your help!