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 =
query
|> by_cursor(Options.decode(opts.cursor), opts.direction)
|> limit(^opts.limit)
|> Repo.all()
total = Repo.aggregate(query, :count)
cursor =
entries
|> last_entry()
|> cursor(opts.fields, opts.cursor_function)
%ExPaginator{
total: total,
entries: entries,
cursor: cursor
}
end
defp cursor(_fields, _cursor_function, []), do: ""
defp cursor(last_entry, fields, cursor_function) do
fields
|> 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})
end)
|> Options.encode()
end
defp last_entry(entries) do
Enum.at(entries, Enum.count(entries) - 1)
end
defp by_cursor(query, nil, _direction), do: query
defp by_cursor(query, cursor, direction) do
cursor
|> 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)
end
end)
end
end
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
Repo.insert(%Models.Post{
user: %Models.User{name: "user_#{i}"},
title: "title_#{i}",
body: "body_#{i}"
})
end
query =
Models.Post
|> join(:left, [p], u in assoc(p, :user), as: :user)
|> preload(:user)
|> order_by([user: u], asc: u.name)
|> select([u], u)
paginator =
ExPaginator.paginate(
query,
%Options{
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 =
ExPaginator.paginate(
query,
%Options{
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
end
end
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