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