Add support of cursor pagination

[seb-jean]

Feature Request

Context

The current pagination system likely relies on offset-based pagination (e.g., using LIMIT and OFFSET). While this approach works well in simple scenarios, it comes with several drawbacks when dealing with large datasets.

Limitations of Offset-Based Pagination

• Performance issues: The larger the OFFSET, the slower the query becomes, as the database must skip an increasing number of rows.
• Inconsistent results: When data is inserted or deleted between two requests, items may be skipped or duplicated.

What is Cursor-Based Pagination?

Cursor-based pagination is a way to load data one page at a time by remembering where you stopped.
Instead of saying “skip the first 20 items” like offset-based pagination does, you say “start from this specific item” using a cursor.
The cursor is an encoded string containing the location that the next paginated query should start paginating. When you load the next page, you ask the system to give you the items that come after:

https://127.0.0.1:8000/users?cursor=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjEwIn0.S_thQX0fWisKG8y6IfsiVW9vQdxwWQHWyjWGDiEDTF0

Below is an example of a query between offset pagination and cursor pagination:

# Offset Pagination
SELECT * FROM users ORDER BY id ASC LIMIT 10 OFFSET 10;
 
# Cursor Pagination
SELECT * FROM users WHERE id > 10 ORDER BY id ASC LIMIT 10;

Implementations In other frameworks

• Rails: Repository
• Laravel: PR and documentation
• Django: documentation

References

• https://use-the-index-luke.com/sql/partial-results/fetch-next-page
• https://www.pingcap.com/article/limit-offset-pagination-vs-cursor-pagination-in-mysql/
• https://www.milanjovanovic.tech/blog/understanding-cursor-pagination-and-why-its-so-fast-deep-dive
• https://www.laravel-enlightn.com/blog/laravel-offset-vs-cursor-pagination/
• https://shopify.engineering/pagination-relative-cursors

[greg0ire]

Have you seen #7383 ?

[beberlei]

Great idea!

[seb-jean]

Have you seen #7383 ?

Oh no, I hadn't seen that. I'll still keep this issue open because it's only for cursor pagination, unlike the issue mentioned.

[greg0ire]

Yes, but maybe you can find people to rally to your cause there, such as @theofidry :)

[seb-jean]

Hi @theofidry,
A cursor-based pagination implementation would be very interesting for Doctrine. In relation to the issue you created (#7383), could you please add a porting to Doctrine?