[Feature Request] Cursor based Pagination

[Wulfheart]

E.g. like in Laravel: https://laravel.com/docs/8.x/pagination#cursor-pagination

This would allow pagination in environments where the values are changing quickly. I think one of the maintainers is familiar with Laravel. Therefore I would like to use this issue as a brain dump right now and will elaborate further if anyone has any questions.

Normal Pagination:
SELECT * FROM xxx OFFSET 10 LIMIT 10

Cursor Pagination:
SELECT * FRO xxx WHERE xy > 10 LIMIT 10

[mindcrash]

You can achieve this already, by combining

query.Where(...)

With

query.Limit(...)

Like this:

var query = new Query("data")
                  .Where("x", ">", 10)
                  .Limit(10)
                  .Select("y")

or, in a more LINQ-like style:

var query = new Query("data")
                  .Where("x", ">", 10)
                  .Take(10)
                  .Select("y")

Which for Postgres will compile down to something like this:

SELECT
  "y"
FROM
  "data"
WHERE
  "x" > 10
LIMIT
  10

[Wulfheart]

Thanks!

I thought this would be a nice addition as there is already pagination support.

[trysenga]

I don't see how offset is accomplished with your examples. X>10 doesn't apply when doing ordering. I was considering using this for a project but we need to build queries like SELECT X,Y FROM Somewhere WHERE Z LIKE '%CSD%' ORDER BY Y DESC LIMIT 20 OFFSET 40 or for MSSQL we'd need the limit to be OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY

Self-answered. Looking at the website, there are better examples. There is a .offset() to use as well as .limit().
var query = new Query("Posts").OrderByDesc("Date").Limit(10).Offset(5); I would close out some of these defects that are just answered questions.