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()