Pagination in Ecto without offset-limit

Phoenix Forum Questions / Help
,
1

Hi,

I am trying to get pagination working on SQL Server 2008. The problem is that this version does not support offset - limit.

Following the ideas from here, I wrote the following SQL script

select * from (
select row_number() over(order by inserted_at desc) as row, * from users
) m
where row BETWEEN 6 and 10

And this is my Ecto version:

def list_users_mssql_2008(_limit, _page) do
  first = 6
  last = 10

  sub =
    from u in User,
      select: %{
        id: u.id,
        email: u.email,
        row: fragment("row_number() over(order by inserted_at desc)")
      }

  q =
    from u in subquery(sub),
      where: fragment("row between (?) and (?)", ^first, ^last)

  Ecto.Adapters.SQL.to_sql(:all, Repo, q)
  Repo.all(q)
end

How can I improve this code?
I do not like that I have to specify each field in the select map.

Any related ideas or comments are welcome.

2 Likes
2

It depends on what unique constraints and indexes you have but a handful of methods are described here.

1 Like
3

Paging over an index with row values is much better than OFFSET + LIMIT. See OFFSET is bad for skipping previous rows. The “difficulty” here is that you can no longer work with page numbers.

4

But they can work with “last seen ID” if they do pagination through ID ranges. It’s also faster in terms of DB query durations.