Cursor-based pagination for Elixir Ecto
Cursor based pagination for Elixir Ecto.
There are several ways to implement pagination in a project and they all have pros and cons depending on your situation.
This is the easiest method to use and implement: you just have to set
LIMITand
OFFSETon your queries and the database will return records based on this two parameters. Unfortunately, it has two major drawbacks:
Inconsistent results: if the dataset changes while you are querying, the results in the page will shift and your user might end seeing records they have already seen and missing new ones.
Inefficiency:
OFFSET Ninstructs the database to skip the first N results of a query. However, the database must still fetch these rows from disk and order them before it can returns the ones requested. If the dataset you are querying is large this will result in significant slowdowns.
This method relies on opaque cursor to figure out where to start selecting records. It is more performant than
LIMIT-OFFSETbecause it can filter records without traversing all of them.
It's also consistent, any insertions/deletions before the current page will leave results unaffected.
It has some limitations though: for instance you can't jump directly to a specific page. This may not be an issue for an API or if you use infinite scrolling on your website.
defmodule MyApp.Repo do use Ecto.Repo, otp_app: :my_app, adapter: Ecto.Adapters.Postgresuse Paginator end
query = from(p in Post, order_by: [asc: p.inserted_at, asc: p.id])
page = MyApp.Repo.paginate(query, cursor_fields: [:inserted_at, :id], limit: 50)
page.entries
contains all the entries for this page.
page.metadata
contains the metadata associated with this page (cursors, limit, total count)
Add
paginatorto your list of dependencies in
mix.exs:
def deps do [{:paginator, "~> 1.0.4"}] end
Add
Paginatorto your repo.
defmodule MyApp.Repo do use Ecto.Repo, otp_app: :my_app, adapter: Ecto.Adapters.Postgresuse Paginator end
Use the
paginatefunction to paginate your queries.
query = from(p in Post, order_by: [asc: p.inserted_at, asc: p.id])return the first 50 posts
%{entries: entries, metadata: metadata} = Repo.paginate(query, cursor_fields: [:inserted_at, :id], limit: 50)
assign the
after
cursor to a variablecursor_after = metadata.after
return the next 50 posts
%{entries: entries, metadata: metadata} = Repo.paginate(query, after: cursor_after, cursor_fields: [{:inserted_at, :asc}, {:id, :asc}], limit: 50)
assign the
before
cursor to a variablecursor_before = metadata.before
return the previous 50 posts (if no post was created in between it should be the same list as in our first call to
paginate
)%{entries: entries, metadata: metadata} = Repo.paginate(query, before: cursor_before, cursor_fields: [:inserted_at, :id], limit: 50)
return total count
NOTE: this will issue a separate
SELECT COUNT(*) FROM table
query to the database.%{entries: entries, metadata: metadata} = Repo.paginate(query, include_total_count: true, cursor_fields: [:inserted_at, :id], limit: 50)
IO.puts "total count: #{metadata.total_count}"
Repo.paginate/4 will throw an ArgumentError should it detect an executable term in the cursor parameters passed to it (before
,after`). This is done to protect you from potential side-effects of malicious user input, see paginator_test.exs.
If you want to reap all the benefits of this method it is better that you create indexes on the columns you are using as cursor fields.
# If your cursor fields are: [:inserted_at, :id] # Add the following in a migrationcreate index("posts", [:inserted_at, :id])
paginate/2. In the future we might do that for you automatically based on the fields specified in `:cursorfields`.
Documentation is written into the library, you will find it in the source code, accessible from
iexand of course, it all gets published to hexdocs.
Clone the repo and fetch its dependencies:
$ git clone https://github.com/duffelhq/paginator.git $ cd paginator $ mix deps.get $ mix test
$ mix docs
See LICENSE