Pagination in Ecto without offset-limit

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.

1 Like

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

1 Like

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.

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.