How to limits results by main entity, not with childrens?

Question

If I do:

    $queryBuilder = $em->createQueryBuilder();

    $queryBuilder->select('n, c');
    $queryBuilder->from('AppBundle:News', 'n');
    $queryBuilder->leftJoin('n.comments', 'c');
    $queryBuilder->setMaxResults(3);

    $results = $queryBuilder->getQuery()->getResult();

And first News has for example 3 comments, then results return me only one record News. If I don't have comments or if I remove leftJoin, then this is working well.

in query builder you can't or remove left join on comments and lazy load your comments data but this would produce N+1 queries issue — M Khalid Junaid, Jul 02 '18 at 07:19
Misunderstood the question on my first duplicate flag, however still a duplicate https://stackoverflow.com/questions/5620771/limiting-a-doctrine-query-with-a-fetch-joined-collection offers a solution. — Jenne, Jul 02 '18 at 09:08

score -1 · Answer 1 · answered Jul 02 '18 at 07:11

-1

You are using the wrong variable for your results.

You are using $queryBuilder to prepare your statement:

$queryBuilder = $em->createQueryBuilder();

But taking $qb to get your result;

$results = $qb->getQuery()->getResult();

answered Jul 02 '18 at 07:11

Jim Panse

2,220
12
34

Sorry, it was my mistake only on stackoverflow. – vosavik Jul 02 '18 at 07:24

Dimitris · Answer 2 · 2018-07-02T15:06:04.953

Doctrine can behave unexpectedly with joins, multiple wheres and pagination.

The recommended way is to use the Paginator, and not directly call getResults. The paginator handles the grouping of the results to the main entity.

Edit: to supply some sample code:

<?php
use Doctrine\ORM\Tools\Pagination\Paginator;

$dql = "SELECT p, c FROM BlogPost p JOIN p.comments c";
$query = $entityManager->createQuery($dql)
                       ->setFirstResult(0)
                       ->setMaxResults(100);

$paginator = new Paginator($query, $fetchJoinCollection = true);

$c = count($paginator);
foreach ($paginator as $post) {
    echo $post->getHeadline() . "\n";
}

https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/tutorials/pagination.html

How to limits results by main entity, not with childrens?

2 Answers2