Quarto

---

Quarto (abbreviated Qto, 4to or 4º) is a book or pamphlet produced from full "blanksheets", each of which is printed with eight pages of text, four to a side, then folded twice to produce four leaves. The leaves are then trimmed along the folds to produce eight book pages. Each printed page presents as one-fourth size of the full blanksheet. (https://en.wikipedia.org/wiki/Quarto)

Quarto is a is a keyset-based pagination library for Ecto but confusingly the it is also referred to as cursor-based pagination. (Though cursor based pagination also means something else).

In this case it means that the pagination relies on an opaque cursor to figure out where to start with the next batch of records. See https://use-the-index-luke.com/no-offset

This library is ported from https://github.com/duffelhq/paginator/ but has some important backwards incompatible differences that warrant a new library.

Installation

If available in Hex, the package can be installed by adding quarto to your list of dependencies in mix.exs:

def deps do
  [
    {:quarto, "~> 1.0.0"}
  ]
end

Usage

You can add Quarto to your Ecto.Repo.

  defmodule MyApp.Repo do
    use Ecto.Repo, otp_app: :my_app
    use Quarto, limit: 10
  end

It adds the paginate/3 function to your repository which you can use to paginate through a resultset. See Quarto.paginate/4 on the options that can be passed to use Quarto.

To paginate you can query an Ecto schema like you normally would. It is important that the columns you order by are deterministically ordered. If there are null values in the columns or if two rows have the same values in the columns you order by the results may be wrong. To fix this you can always add an extra unique column to order by.

The columns you order by will also be used to construct the opaque cursor that can be used to retrieve the next set of results.

# First set of results
%{entries: entries, metadata: metadata} = Post |> order_by(desc: :user_id) |> order_by({^order,:published_at}) |> MyApp.Repo.paginate

after_cursor = metadata.after
# Next set of results
%{entries: entries, metadata: metadata} = Post |> order_by(desc: :user_id) |> order_by({^order,:published_at}) |> MyApp.Repo.paginate(after: after_cursor)

# Get the before cursor
before_cursor = metadata.before

# Previous set of results
%{entries: entries, metadata: metadata} = Post |> order_by(desc: :user_id) |> order_by({^order,:published_at}) |> MyApp.Repo.paginate(before: before_cursor)


%{entries: entries, metadata: metadata} = Post |> order_by(desc: :user_id) |> order_by({^order,:published_at}) |> MyApp.Repo.paginate(include_total_count: true)

# Total count of rows satisfying the query
metadata.total_count

Advanced usage

Some more advanced use cases are shown below.

Without a macro

If you cannot use the macro in the repo you can still use Quarto.

Quarto.Post
|> order_by(desc: :published_at)
|> Quarto.paginate([], MyApp.Repo)

Simply call Quarto.paginate(queryable, opts, MyApp.Repo, repo_opts) with your repo as the third argument.

Joining on related tables

In addition to ordering by a field on the current table it's also possible to order on the field of a related table.

E.g. in this example the ordering of the posts is by the name of the related user and the published_at date of the post.

Quarto.Post
|> join(:left, [p], u in assoc(p, :user), as: :user)
|> preload([p, u], user: u)
|> order_by([p, u], desc: u.name)
|> order_by(desc: :published_at)
|> Repo.paginate

What's important is that the preload function loads the user. This is necessary for when the cursor for the next result set is created as it needs the name of the user and the published_at of the post to create the new cursor.

Passing in the cursor

Although in normal usage the encoded cursor can be passed in. It's possible to use a normal erlang term as well.

Quarto.Post
|> join(:left, [p], u in assoc(p, :user), as: :user)
|> preload([p, u], user: u)
|> order_by([p, u], desc: u.name)
|> order_by(desc: :id)
|> Repo.paginate(after: ["Alice", 10])

Implementing a custom cursor

A default cursor implementation is provided but you can provide a custom implementation. E.g. to sign the cursors. The codec needs to implement the Quarto.Cursor behaviour. See Quarto.Cursor.Base64 for an example

Encoding/decoding cursor values

Normally a cursor is composed out of a list of values of one or more fields in the database. How each field is encoded/decoded depends on its type and can be controlled with the Quarto.Cursor.Decode and Quarto.Cursor.Encode protocols. See those modules for examples how you can override this

Fragments

Ecto fragments provide ways to use custom SQL expressions in your ecto queries. Quarto does not support them in the order_by clause.

NULL values

NULL values in columns can be problematic for Quarto. Using coalescing it's possible to order by NULL values by coalescing them into a known value. E.g. when there are NULL values in a datetime column, the NULL values can be coalesced into the max or min datetime your database supports, depending on where you want them in your resultset.

In the following example the title field has NULL values and therefore hard to order by. If we order alphabetically and asc and decide to place the rows with NULL values at the end we need to coalesce the NULL values to e.g. ZZZZZZZ. It's important that it's a value higher than any other in that column.

coalesce = fn field, position, value ->
  case field do
    :title -> "ZZZZZZZ"
    _ -> value
  end
end

Quarto.Post |> order_by({:asc, :title}) |> order_by({:asc, :position}) |> MyApp.Repo.paginate(limit: 4, coalesce: coalesce)

The field with the name

Documentation can be generated with ExDoc and published on HexDocs. Once published, the docs can be found at https://hexdocs.pm/quarto.