Paginate with Ecto

The easiest way to paginate data is to use LIMIT-OFFSET method. Although it can be what you want it has few problems:

  • It is rather slow because to skip results it still has to be fetched from db.
  • It can give inconsitent results when new data is inserted while paginating.

Alternative approach is keyset pagination where values from last pagination is remembered between calls for next page. In Elixir ecosystem we already have great library that supports it: paginator. Most of the time it is the best choice but sometimes we cannot include external library in our solution or we need something more tailored to our needs.

Our paginator will consist of following modules:

  • ExPaginator - main entry to our library - it exposes paginate function
  • Options - struct that holds parameters for pagination
  • ForwardQuery, BackwardQuery - generates pagination query
defmodule ExPaginator do
  defstruct entries: [], total: 0, cursor: ""
  import Ecto.Query

  alias ExPaginator.BackwardQuery
  alias ExPaginator.ForwardQuery
  alias ExPaginator.Options
  alias ExPaginator.Repo

  def paginate(query, %Options{} = opts) do
    entries =
      |> by_cursor(Options.decode(opts.cursor), opts.direction)
      |> limit(^opts.limit)
      |> Repo.all()

    total = Repo.aggregate(query, :count)

    cursor =
      |> last_entry()
      |> cursor(opts.fields, opts.cursor_function)

      total: total,
      entries: entries,
      cursor: cursor

  defp cursor(_fields, _cursor_function, []), do: ""

  defp cursor(last_entry, fields, cursor_function) do
    |> Enum.reduce(%{}, fn
      {{table, column}, order}, acc ->
        Map.put(acc, column, %{table: table, value: cursor_function.(last_entry), order: order})

      {column, order}, acc ->
        Map.put(acc, column, %{table: nil, value: Map.get(last_entry, column), order: order})
    |> Options.encode()

  defp last_entry(entries) do
    Enum.at(entries, Enum.count(entries) - 1)

  defp by_cursor(query, nil, _direction), do: query

  defp by_cursor(query, cursor, direction) do
    |> Map.keys()
    |> Enum.reduce(query, fn column, query ->
      %{table: table, value: value, order: order} = Map.get(cursor, column)

      case direction do
        :forward ->
          ForwardQuery.where(query, order, table, column, value)

        _ ->
          BackwardQuery.where(query, order, table, column, value)

ExPaginator based on given options does 3 things:

  • query for new page of entries - we use previous cursor to do that
  • calculate new cursor based on last entry from new page
  • calculate total values

In case when we want to use pagination keys from associated tables we have to use cursor_function option. To paginate forward/backward we can use direction option. To get first page of data we do not need cursor. To obtain next/previous pages we have to pass cursor which is map of last values from previous page of columns used in pagination.

In following test you can see how to paginate on associated table.

defmodule ExPaginatorTest do
  use ExPaginator.RepoCase

  alias ExPaginator.Models
  alias ExPaginator.Options
  test "paginate on association" do
    for i <- 1..5 do
        user: %Models.User{name: "user_#{i}"},
        title: "title_#{i}",
        body: "body_#{i}"

    query =
      |> join(:left, [p], u in assoc(p, :user), as: :user)
      |> preload(:user)
      |> order_by([user: u], asc: u.name)
      |> select([u], u)

    paginator =
          fields: [{{:user, :name}, :asc}],
          limit: 2,
          direction: :forward,
          cursor_function: fn post -> post.user.name end

    assert paginator.total == 5

    assert paginator.cursor ==
      Options.encode(%{name: %{table: :user, value: "user_2", order: :asc}})

    assert [%{user: %{name: "user_1"}}, %{user: %{name: "user_2"}}] = paginator.entries

    paginator =
          fields: [{{:user, :name}, :asc}],
          limit: 2,
          cursor: paginator.cursor,
          cursor_function: fn post -> post.user.name end

    assert paginator.total == 5

    assert paginator.cursor ==
             Options.encode(%{name: %{table: :user, value: "user_4", order: :asc}})

    assert [%{user: %{name: "user_3"}}, %{user: %{name: "user_4"}}] = paginator.entries

You can define fields that you want to use in pagination as list of tuples [{:column_name, :order_dir}]. In case when you want to paginate on associated table you have to specify it in fields as described in test. In that case you also have to provide cursor_function which is used to get value from last entry of paginated result.

You can view full code at: https://github.com/elpikel/ex_paginator