Limiting a doctrine query with a fetch-joined coll

Question

I have a doctrine query that returns blog posts and their comments:

SELECT b, c FROM BlogPost b LEFT JOIN b.comments c

I would like to limit the results to 10 blog posts. According to the DQL documentation, setMaxResults() doesn't work correctly on queries that fetch-join a collection (comments in this case):

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.

How would I properly limit a doctrine query that contains a fetch-joined collection (in this case, limit the results to 10 blog posts)?

Answer 1

Even though this is an old one this scores high on google looking for doctrine left join limit. Quickly it got me looking for "doctrine paginator" and after insisting to google that that was what I was looking for (not doctrine pagination) I still had some trouble figuring out how to use it.

Maybe searched wrong but could not really find good documentation on the paginator object. Anyway; to get the results I used getIterator and that worked fine.

My code has Sources that contain rss links and Articles that are articles from the rss feed. So in this example I'll get one Source and all it's articles. This code is from Symfony.

        // get the articles (latest first) from source 743
        $q=$this->getDoctrine()->getManager()
          ->createQuery('select s, a from MyCompanyRssBundle:Source s 
          join s.Articles a 
          where s.id = :id 
          order by a.id desc')
          ->setParameter('id',743);
        $q->setMaxResults(1);  // this limits Articles to be only 1
                               // when using $q->getResult();
        $sources=new Paginator($q, $fetchJoin = true);
        $sources=$sources->getIterator();
        var_dump($sources[0]->getArticles());

Answer 2

Paginate was merged with doctrine 2.2 And the new symfony2 release 2.0.10 is compatible with.

Now use it like that

//use Doctrine paginator
use Doctrine\ORM\Tools\Pagination\Paginator;

Write your query then call results like that.

$query->setMaxResults($limit);
$query->setFirstResult($offset);
$results = new Paginator($query, $fetchJoin = true);

Hope this will help you.

Note: If you are using SF2 2.0.10, you should update the deps and deps.lock files and specify the 2.2 version for Doctrine bundles.

Answer 3

Did the same with a querybuilder and it works. Maybe something else is the problem?

$qb->add('select', 'b, c, ch, g')
   ->add('from', 'Broadcast b')
   ->add('groupBy', 'b.title')
   ->add('where', 'b.imageBig != \'\'')
   ->add('orderBy', 'b.starttime ASC')
   ->setMaxResults(10)
   ->leftJoin('b.category', 'c')
   ->leftJoin('b.channel', 'ch')
   ->leftJoin('b.genre', 'g')

Answer 4

This repository http://github.com/beberlei/DoctrineExtensions has a paginator extension that works with fetch joins. You have to essentially make 3 SELECT statements, all of which this extension does for you.