CODEWITHSUNDEEP
symfonydoctrine-orm
leebake
leebake

Reputation: 55

Doctrine2 OFFSET and LIMIT

I want to use limit and offset in my query but the number of records returned does not match. When I'm not using offset and limit function gets 26 objects, and after setting methods 

->setMaxResults(5)
->setFirstResult(10)

the number is 1 ... What's going on?

Upvotes: 1

Views: 8969

Answers (1)

Ocramius
Ocramius

Reputation: 25431

What you are probably is a typical problem you get when fetch-joining in DQL. This is a very simple issue and derives from the fact that offset and limit are applied on a resultset that is not yet hydrated and has to be normalized (see the documentation about first and max results about that).

If you want to avoid the problem (even with more complex joined or fetch-joined results), you will need to use ORM DQL Paginator API. Using the paginator basically triggers multiple queries to:

  1. compute the number of records in the resultset according to your offset/limit
  2. compute the different identifiers of the root entity of your query (with applied max/first results)
  3. retrieve joined results (without applied first/max results)

Its usage is quite simple:

$query = $em->crateQuery($fetchJoinQuery);

$paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query);

$query->setFirstResult(20);
$query->setMaxResults(100);

foreach ($paginator as $result) {
    var_dump($result->getId());
}

This will print 100 items starting from the one at offset 20, regardless of the numer of joined or fetch-joined results.

While this may seem to be un-performant, it's the safest way to handle the problem of fetch-joined results causing apparently scrambled offsets and limits in results. You may look into how this is handled directly by diving into the internals of the ORM Paginator.

Upvotes: 7

Related Questions