Paging

Ecto for Beginners : part 21 of 22 published on Sep 07, 2015

To page through results in Ecto, we are going to use the offset and the limit queries.

Offset moves the starting point for our return. So if we have an array [1,2,3,4,5] and started at an offset of 2, we would get [3,4,5].

We will start the offset at:

offset = page * per_page

We then limit the results we want by the number we want per page.

So to return the first page of Posts with 5 posts per page, our query would look like:

posts = Post 
  |> offset([_], 0)
  |> limit([_], 5)
  |> Repo.all

And if we wanted the second page of Posts with 5 posts per page, our query would look like:

posts = Post 
  |> offset([_], ^(1 * 5))
  |> limit([_], 5)
  |> Repo.all

Our full test looks like:

# /test/models/posts/paging_test.exs
defmodule Readdit.Posts.PagingTest do
  use ExUnit.Case
  use Readdit.ConnCase
  alias Readdit.Post
  alias Readdit.Repo
  import Ecto.Query

  setup do
    Enum.each((1..20), fn i -> 
      %Post{title: "Post #{i}"} |> Readdit.Repo.insert!
    end)
    
    :ok
  end
  
  test "first page" do
    posts = Post 
    |> offset([_], 0)
    |> limit([_], 5)
    |> Repo.all
      
    assert Enum.count(posts) == 5

    post_titles = Enum.map(posts, fn p -> p.title end)
    assert post_titles == ["Post 1", "Post 2", "Post 3", "Post 4", "Post 5"]
  end
  
  test "second page" do
    page = 1
    per_page = 5
    offset = page * per_page

    posts = Post 
    |> offset([_], ^(1 * 5))
    |> limit([_], 5)
    |> Repo.all
      
    assert Enum.count(posts) == 5

    post_titles = Enum.map(posts, fn p -> p.title end)
    assert post_titles == ["Post 6", "Post 7", "Post 8", "Post 9", "Post 10"]
  end
  
end
Next: Generic Paging Function