Faster pagination with Doctrine 2.1 EXTRA_LAZY associations

Question

Doctrine 2.1 brings a new feature EXTRA_LAZY loading for associations: https://www.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/extra-lazy-associations.html

This feature creates a new method slice($offset, $length) to query just a page of the association and is very useful for pagination of large data sets.

However, behind the scene the SQL query uses the classic LIMIT XX OFFSET XX syntax which is slow for large data sets (https://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/)

Is there a way to use the pagination with a WHERE clause?

If not, how may I extend the instance of Doctrine\ORM\PersistentCollection to create a method sliceWithCursor($columnName, $cursor, $length)?

My main goal is to implement a faster pagination while using the very convenient magic of Doctrine for associations.

Thanks !

score 3 · Answer 1 · edited Jan 01 '23 at 15:11

You can use the matching function of Doctrine\ORM\PersistentCollection, providing the criteria to filter, e.g.:

use Doctrine\Common\Collections\Criteria;

$group = $entityManager->find('Group', $groupId);
$userCollection = $group->getUsers();

$criteria = Criteria::create()
    ->where(Criteria::expr()->eq("birthday", "1982-02-17"))
    ->orderBy(array("username" => Criteria::ASC));

$birthdayUsers = $userCollection->matching($criteria);

matching() returns a Doctrine\ORM\LazyCriteriaCollection, if your association is defined as EXTRA_LAZY.

You can paginate with the latter:

$birthdayUsers->slice($offset, $length);

Using cursor pagination

In some cases, it is required to use cursor pagination. You could do this by extending Doctrine\ORM\PersistentCollection, as suggested:

use Doctrine\Common\Collections\Criteria;

public function sliceWithCursor($criteria, $cursorEntity, $limit) {
    $orderBy = $criteria->getOrderings();

    foreach ($orderBy as $columnName => $direction) {        
        if ($direction === Criteria::ASC) {
           $criteria->andWhere(Criteria::expr()->gte($columnName, $cursorEntity->{$columnName}));
        } else {
           $criteria->andWhere(Criteria::expr()->lte($columnName, $cursorEntity->{$columnName}));
        }
    }

    // exclude cursor entity from the results
    $criteria->andWhere(Criteria::expr()->neq("id", $cursorEntity->id));

    $criteria->setMaxResults($limit);

    return $this->matching($criteria);
}

The idea of cursor based pagination is to use a result row as starting point, instead of an offset, and get the next rows. As stated at alternative for using OFFSET, the idea is to substitute offset, with conditions from the order by clause.

Faster pagination with Doctrine 2.1 EXTRA_LAZY associations

1 Answers1

Using cursor pagination