Cursor decode does not work for custom sort field with `fragment`

[marinakr]

Custom field with fragment, passed with anonymous function

cursor_fields = [
      {{:vendors,
       fn ->
         dynamic([vendors: v], fragment("LOWER(?)", v.name))
       end}, :asc},
      {:id, :asc}
]

Call without after_cursor is working:

opts = [
          limit: args[:limit] || @hard_limit,
          cursor_fields: cursor_fields,
          fetch_cursor_value_fun: fetch_cursor_value_fun
 ]
 %{metadata: %{after: after_cursor}} = Paginator.paginate(query, opts, Db.Repo, [])

However, when paginate with after, &Paginator.Cursor.decode/1 will fail:

opts = [
          limit: args[:limit] || @hard_limit,
          cursor_fields: cursor_fields,
          fetch_cursor_value_fun: fetch_cursor_value_fun,
          after: after_cursor
 ]
 
 Paginator.paginate(query, opts, Db.Repo, []) # <- Fails

Output:

     ** (ArgumentError) cannot deserialize #Function<2.128102115/0 in Clients.Queries.CollectionQuery..../1>, the term is not safe for deserialization
     stacktrace:
       (plug_crypto 1.2.5) lib/plug/crypto.ex:80: Plug.Crypto.non_executable_terms/1
       (plug_crypto 1.2.5) lib/plug/crypto.ex:99: Plug.Crypto.non_executable_tuple/2
       (plug_crypto 1.2.5) lib/plug/crypto.ex:65: anonymous fn/3 in Plug.Crypto.non_executable_terms/1
       (stdlib 5.2) maps.erl:416: :maps.fold_1/4
       (plug_crypto 1.2.5) lib/plug/crypto.ex:51: Plug.Crypto.non_executable_binary_to_term/2
       (paginator 1.2.0) lib/paginator/config.ex:44: Paginator.Config.new/1
       (paginator 1.2.0) lib/paginator.ex:175: Paginator.paginate/4

It is documented already:
https://github.com/duffelhq/paginator/pull/166/files#diff-b335630551682c19a781afebcf4d07bf978fb1f8ac04c6bf87428ed5106870f5R190

Function

fn ->
         dynamic([vendors: v], fragment("LOWER(?)", v.name))
 end

is not safe term to decode in Paginator.Cursor, deps/paginator/lib/paginator/cursor.ex:9

Idea: mb to support {:mfa, module, functions, args} way to decode after_cursor

[marinakr]

While this is not resolved, temporary solution can be a using of computed columns (like views for tables) that is not stored on disk

    CREATE FUNCTION name_lower(vendors) RETURNS text AS $$ 
    SELECT LOWER($1.name);
    $$ LANGUAGE SQL;

However, does not properly with virtual: true

[senconscious]

On what version have you tried this ? I've encountered same problem, but it turns out that I was using not latest version.

On main branch this version is working (run inside paginator_test.exs)

test "expression based field is properly encoded and decoded" do
    customers =
      ["A customer", "b customer", "C customer", "d customer"]
      |> Enum.map(&%{name: &1, internal_uuid: Ecto.UUID.generate()})
      |> Enum.map(&insert(:customer, &1))

    paginate_opts = [
      limit: 1,
      cursor_fields: [
        {
          {:name,
           fn ->
             Ecto.Query.dynamic(
               [customer],
               fragment("lower(?)", customer.name)
             )
           end},
          :desc
        }
      ],
      fetch_cursor_value_fun: fn schema, :name ->
        schema
        |> Map.get(:name)
        |> String.downcase()
      end
    ]

    assert %Page{entries: [%Customer{name: "d customer"}], metadata: metadata} =
             Customer
             |> order_by([customer], desc: fragment("lower(?)", customer.name))
             |> Repo.paginate(paginate_opts)

    paginate_opts = Keyword.put(paginate_opts, :after, metadata.after)

    assert %Page{entries: [%Customer{name: "Charlie"}], metadata: metadata} =
             Customer
             |> order_by([customer], desc: fragment("lower(?)", customer.name))
             |> Repo.paginate(paginate_opts)

    paginate_opts = Keyword.put(paginate_opts, :after, metadata.after)

    assert %Page{entries: [%Customer{name: "C customer"}], metadata: metadata} =
             Customer
             |> order_by([customer], desc: fragment("lower(?)", customer.name))
             |> Repo.paginate(paginate_opts)

    paginate_opts =
      paginate_opts
      |> Keyword.put(:before, metadata.before)
      |> Keyword.put(:after, nil)

    assert %Page{entries: [%Customer{name: "Charlie"}]} =
             Customer
             |> order_by([customer], desc: fragment("lower(?)", customer.name))
             |> Repo.paginate(paginate_opts)
  end