setMaxResults does not works fine when Doctrine query has join

Question

I want to write a DQL query that select post and join to another Entity's.

Here is my code:

   $dql = '
                    SELECT p , h ,t ,m 
                    FROM App:Post p 
                    LEFT JOIN p.mentions m
                    LEFT JOIN p.tags t 
                    LEFT JOIN p.file h 
                    WHERE p.user
                    IN (
                        SELECT f FROM App:User u
                        JOIN u.followers f
                        WHERE u.id = :uid
                       )
                    OR p.user = :uid ';

    $query = $this->getEntityManager()
        ->createQuery($dql)
        ->setMaxResults(5)
        ->setParameters(['uid' => $user->getId()])
        ->getArrayResult();

But the problem is setMaxResults does not limit the posts Entity but instead limits tags Entity on 5.

Here is my two type of result:

1.with setMaxResults (Not work fine)

2.with setMaxResults (Work fine)

What is wrong with my code?

Answer 1

This is the expected behaviour in doctrine when using setMaxResults() or setFirstResult() without a paginator.

setMaxResults() is actually adding an SQL LIMIT to the query produced, it will not limit only the root entity as you expect, but the rows returned by the query. This means that on joined queries it will not do what you want.

According to 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.

What you can do to achieve what you want is use a Paginator on your query:

$query = $this->getEntityManager()
    ->createQuery($dql)
    ->setMaxResults(5)
    ->setParameters(['uid' => $user->getId()]);
$paginator = new Paginator($query, $fetchJoinCollection = true);

$c = count($paginator);
foreach ($paginator as $post) {

}

From the Paginator documentation link above:

Paginating Doctrine queries is not as simple as you might think in the beginning. If you have complex fetch-join scenarios with one-to-many or many-to-many associations using the "default" LIMIT functionality of database vendors is not sufficient to get the correct results.

Also, note that:

By default the pagination extension does the following steps to compute the correct result:

• Perform a Count query using DISTINCT keyword.
• Perform a Limit Subquery with DISTINCT to find all ids of the entity in from on the current page.
• Perform a WHERE IN query to get all results for the current page.

This behavior is only necessary if you actually fetch join a to-many collection. You can disable this behavior by setting the $fetchJoinCollection flag to false; in that case only 2 instead of the 3 queries described are executed. We hope to automate the detection for this in the future.

Similar question