Macros for establishing your pagination strategies.

For example:

defmodule MyApp.PaginationPlanner do
  use Chunkr.PaginationPlanner

  # Sort by a single column.
  paginate_by :username do
    sort :asc, as(:user).username
  end

  # Sort by DESC `user.inserted_at`, with ASC `user.id` as a tiebreaker.
  # In this case, `user.id` is explicitly called out as a UUID.
  paginate_by :user_created_at do
    sort :desc, as(:user).inserted_at
    sort :asc, as(:user).id, type: :binary_id
  end

  # Sort names in ASC order.
  # Coalesce any `NULL` name values so they're at the end of the result set.
  # Use `user.id` as the tiebreaker.
  paginate_by :last_name do
    sort :asc, fragment("coalesce(?, 'zzz')", as(:user).last_name)
    sort :asc, fragment("coalesce(?, 'zzz')", as(:user).first_name)
    sort :desc, as(:user).id
  end
end

The paginate_by/2 macro above takes a name for the pagination strategy along with the fields to sort by in their desired order. The fields can be actual table columns or dynamically-generated values via Ecto fragments. Fragments are especially handy for implementing case-insensitive sorts, coalescing NULL values, and so forth.

Each call to sort requires a sort direction (:asc or :desc), any valid Ecto fragment or field (using :as), and an optional :type keyword. If :type is provided, the relevant cursor value will be cast to that type when filtering records.

The result of registering these pagination strategies is that, at compile time, Chunkr automatically defines the functions necessary to take future queries and extend them for your desired pagination strategies. This involves dynamically implementing functions to sort, filter, and limit your queries according to these strategies as well as functions to select both the fields needed for the cursor as well as the records themselves.

Ordering

It is essential that your results are deterministically ordered, otherwise you will see unexpected results. Therefore, the final column used for sorting (i.e. the ultimate tie-breaker) must always be unique and non-NULL.

Named bindings

Because these sort clauses must reference bindings that have not yet been established, we use :as to take advantage of Ecto's late binding. The column referenced by :as must then be explicitly provided within your query or it fail.

Always coalesce NULL values!

SQL cannot reasonably compare NULL to a non-NULL value using operators like < and >. However, when filtering records against our cursor values, it's not uncommon to find ourselves in a situation where our sorted fields may include NULL values. Without intervention, any records that contain a NULL value in one of the sort fields would be entirely dropped from the result set, which is almost surely not the intention.

To work around this awkwardness, you'll need to pick a value that is almost sure to come before or after the rest of your results (depending on whether you want NULL values to sort to the beginning or the end of your results respectively) and coalesce any NULL values in sorted fields so that these records sort to the desired location. With keyset-based pagination, it's not enough to use a strategy like ordering by NULLS LAST or NULLS FIRST. Remember, it's not the ordering itself where this is problematic; it's the efficient filtering of records (via comparison to a cursor) where records with NULL values would get dropped.

Note that you only need to coalesce values within your actual pagination strategy, and the coalesced values will only be used behind the scenes (for cursor values and when filtering records against cursors). You do not need to coalesce values in the query that you provide to Chunkr.Pagination.paginate/4, and you need not worry about values somehow being altered by Chunkr in the records that are returned in each page of results.

Indexes

In order to get maximum performance from your paginated queries, you'll want to create database indexes that align with your pagination strategy. When sorting by multiple columns, you will need to have an index in place that includes each of those columns with sort orders matching your strategy. However, you shouldn't need to include the inverse order as the database should be able to recognize and automatically reverse the index order when necessary. By providing an index that matches your pagination strategy, you should be able to take advantage of efficient pipelined top-N queries.

Limitations

Chunkr limits the number of sort clauses to 4.