doctrine querybuilder limit and offset

Question

i'm a symfony beginner and i want to make a blog with the framework. i use repository to get home articles with this method :

public function getHomeArticles($offset = null, $limit = null) {     $qb = $this->createQueryBuilder('a')                ->leftJoin('a.comments', 'c')                ->addSelect('c')                ->addOrderBy('a.created', 'DESC');       if (false === is_null($offset))         $qb->setFirstResult($offset);      if (false === is_null($limit))         $qb->setMaxResults($limit);      return $qb->getQuery()               ->getResult(); } 

so in my database i have 10 articles. In my BlogController i use :

$blog = $em->getRepository('TestBlogBundle:Article')                 ->getHomeArticles(3,4); 

With this i want 4 articles. But in return i also have one article.

What is the problem?

Answer 1

This is a know issue where setFirstResult() and setMaxResults() need to be use with care if your query contains a fetch-joined collection.

As stated about First and Max Result Items:

If your query contains a fetch-joined collection specifying the result limit methods are not working as you would expect. Set Max Results restricts the number of database result rows, however in the case of fetch-joined collections one root entity might appear in many rows, effectively hydrating less than the specified number of results.

Instead, you can:

1. Lazy load

2. use the Paginator (as stated by @Marco here)

3. Use Doctrine\Common\Collections\Collection::slice()