Doctrine2 query behavior with groupBy

Question

I have the following custom method in the repo:

$query = $qb->select('Client', 'Organization')
                ->from(':Client', 'Client')
                ->leftJoin('Client.organizations', 'Organization');
--different searh conditions--

Then I use paginator to get the results:

$paginator = new \Doctrine\ORM\Tools\Pagination\Paginator($query->getQuery());
$clients = $paginator->getQuery()
                     ->setMaxResults($length)
                     ->setFirstResult($start);

Here where the magic comes. I set length to 10 and first result to 0. So basicaly I should get 10 results on the page. However if there are 5 Organizations for the Client (Client mtm Organization), there will be 5 results, if there are 7 Organizations - 3 results.

But if I add

$query->groupBy('Client');

Then all is "ok" with the root level of results: i.e. there are 10 Clients on the page, but there is not all of the Organizations (max 1).

Did anyone experience the same issue? Any thoughts, suggestions?

Answer 1

It probably counts the same item several times.

Try once to add a distinct clause to your query.

$query = $qb->select('Client', 'Organization')
            ->from(':Client', 'Client')
            ->leftJoin('Client.organizations', 'Organization');
            ->distinct();