Example implementation of cursor-based pagination

cursor-pagination.md

Example implementation of cursor-based pagination

This shows how one can paginate throug a list of tenants with id and name using two different sortings. The limit is always 2.

Data used

Table: tenants

id	name
1	a
2	b
3	c
4	b

Sort by ID (default)

Page 1

GET /api/tenants

SELECT *
FROM "tenants"
ORDER BY "id" ASC
LIMIT 2

id	name
1	a
2	b

Page 2

GET /api/tenants?after=#{encode_cursor(2)}

SELECT *
FROM "tenants"
WHERE "id" > 2
ORDER BY "id" ASC
LIMIT 2

id	name
3	c
4	b

Sort by name

Page 1

GET /api/tenants?sort=name

SELECT *
FROM "tenants"
ORDER BY "name" ASC, "id" ASC
LIMIT 2

id	name
1	a
4	b

Page 2

GET /api/tenants?sort=name&after#{encode_cursor(['b', 4])}

Note that the cursor always holds the ID value and values of the fields you want to sort by. The trick here is that you put all fields you want to sort by inside the WHERE clause. You also have to use ">=" (or (x > y OR x = y)) for all non-unique fields.

SELECT *
FROM "tenants"
WHERE (name, id) > ('b', 4)
ORDER BY "name" ASC, "id" ASC
LIMIT 2

id	name
2	b
3	c