[SOLVED] Limiting a doctrine query with a fetch-joined collection?

Limiting a doctrine query with a fetch-joined collection?

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

Solution

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.