CODEWITHSUNDEEP
phpsymfonydoctrine-orm
mR.Rian
mR.Rian

Reputation: 645

setMaxResults does not works fine when Doctrine query has join

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?

Upvotes: 17

Views: 14276

Answers (2)

Jannes Botis
Jannes Botis

Reputation: 11242

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

Upvotes: 29

rob006
rob006

Reputation: 22164

I suggest to run this query (with limit) on DB directly. You will see that query returns 5 rows, but with only 3 unique posts. This is because one post may have multiple tags, so each combination of post-tag will result one row. So 2 posts with 3 tags each will result 6 rows. Setting limit will affect number of rows returned by query, it will not magically limit only posts count. Duplicated post in query result are removed on mapping query result to objects, but at this point you have only 3 unique posts.

You should probably query posts and tags separately and not use limit with one-to-many or many-many relations.

Upvotes: 1

Related Questions