Limiting a doctrine query with a fetch-joined collection?

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

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 2

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.