Download the PHP package paysera/lib-pagination without Composer

Paysera Pagination component

This component allows to paginate Doctrine QueryBuilder instances using cursor-based pagination. Offset is also supported.

Why?

Performance

Cursor pagination can be much more efficient under large data-sets.

For example, to take 3000th page, consisting of 100 items, using offset you would need to execute such query:

This type of query, even if you have indexes on the fields you are querying by (and the fields you order by), would need to iterate over 29900 items first, before giving you the requested result.

If using cursor based pagination, the resulting query can look like this:

This allows to add required indexes to the table to enable fast querying. Keep in mind that without any indexes, the performance can be really similar.

In this concrete example, there should be a multiple-column index on field,created_at,id for best performance (or just field,created_at if created_at is rather unique).

Iterate over all the items

When using query from previous example with offset based pagination, it's possible to get the same item twice or miss some of the items.

This is because when we're getting the pages, new items can be added or some of the items removed, which changes the position of all other items.

Let's take an example. We have pages, each of 5 items. We've already got the first page with items 1 2 3 4 5. Before we get the second page, these situations might occur:

If we're using cursor based pagination, we start the page after (including or excluding) or before some concrete item, so we don't get these types of issues.

Caveats

Using cursors, it's easy to move to the next and previous pages, but it's not possible to:

• know what page you are in right now;
• to skip some number of pages directly or go to page number X.

To support mixed cases:

• cursors are always provided and available for pagination;
• hasNext and hasPrevious are also always available to know if you're in the first or last page;
• offset is available for pagination, but can be limited to some maximum. This could be used to move to N-th page, but not 100N-th, which is not needed that often;
• if you need to skip to the last page, you should offer (or do that automatically) to reverse the ordering and go to first page instead;
• to display number of pages, total count must be calculated. This can be done but is disabled by default, again, for performance reasons. You should avoid giving this with each of the pages – instead provide total count only when needed or at least cache it.

Installation

Basic Usage

Use ResultProvider class (service) to provide the result.

To get the result, two arguments are needed:

• ConfiguredQuery. This is wrapper around QueryBuilder and has configuration for available ordering fields, maximum offset, result item transformation and whether total count should be calculated;
• Pager. This holds parameters provided by the user: offset or after/before cursor, limit and ordering directions.

Generally ConfiguredQuery holds internals related to QueryBuilder so it's recommended to return this from the Repository directly – let's hold implementation details together.

Pager should be created somewhere in the input layer, usually in the controller or similar place.

Example usage:

Using iterator

There are a few classes (services) to help iterating over large result sets:

• ResultIterator – iterates using pagination, hides the pagination beneath;
• FlushingResultIterator – same as ResultIterator, but flushes and clears EntityManager after each page. Use when you need to modify Entities managed by Doctrine. This pattern (instead of just calling $em->flush() in the end) is needed to avoid out of memory failures and to optimize the process.

Using ResultIterator:

Using FlushingResultIterator:

If there was out of memory exception etc, search logs (INFO level) for last "Continuing with iteration" message, look at "after" in the context, then:

Semantic versioning

This library follows semantic versioning.

See Symfony BC rules for basic information about what can be changed and what not in the API.

Running tests

Contributing

Feel free to create issues and give pull requests.

You can fix any code style issues using this command: