Doctrine : Pagination with left Joins

Question

I would like to paginate a complex request with at least 2 left joins, but the pagination bundle I'm using (KnpPaginationBundle) can't tell Doctrine how to count the result (which is needed for the pagination process), and keep having this Exception.

Cannot count query which selects two FROM components, cannot make distinction

Here is a sample request built with the Doctrine QueryBuilder.

public function findGroupsByUser(User $user, $listFilter, $getQuery = false, $order = 'ASC')
{
    $query = $this->createQueryBuilder('r')
        ->select('r as main,g')
        ->select('r as main,g, count(gg) as members')
        ->leftjoin('r.group', 'g')
        ->innerjoin('MyBundle:GroupMemberRel', 'gg', 'WITH', 'r.group = gg.group')
        ->addGroupBy('g.groupId')
        ->add('orderBy', 'g.name ' . $order);
   if ($getQuery == true) {
        return $query;
    }

    return $query->getQuery()->getResult();
}

Then I give this request to the knp_paginator service, and then I've got the exception

    $groupQuery = $this->em->getRepository('MyBundle:GroupMemberRel')->findGroupsByUser($user, $listFilter, true);
    $paginator = $this->container->get('knp_paginator');
    /* @var $groups Knp\Component\Pager\Pagination\PaginationInterface */
    $groups = $paginator->paginate(
        $groupQuery, $this->container->get('request')->query->get('page', 1), 10 /* limit per page */
    );

Any idea on how to paginate over a complex request, I'm pretty sure this use-case is common, don't want to hydrate my result after the pagination.

Answer 1

For anyone looking for an answer about this, there is a good solution at: https://github.com/KnpLabs/KnpPaginatorBundle/blob/master/Resources/doc/manual_counting.md

$paginator = new Paginator;

// determine the count of the entire result set
$count = $entityManager
    ->createQuery('SELECT COUNT(c) FROM Entity\CompositeKey c')
    ->getSingleScalarResult();

// create a query to be used with the paginator, and specify a hint
$query = $entityManager
     ->createQuery('SELECT c FROM Entity\CompositeKey c')
     ->setHint(
        'knp_paginator.count', 
        $count
    );

// paginate, and set "disctinct" option to false
$pagination = $paginator->paginate(
    $query, 
    1, 
    10, 
    array(
        'distinct' => false,
    )
);

Basically, what you are doing, is you are creating your own 'count' query and instruct knp paginator to use this.

Answer 2

It is difficult to understand the entities in the original question, but I ran into this same problem and it was indeed solvable.

Suppose that you have an entity like this:

  class User
  {
    // ...
    /**
     * @ORM\OneToMany(targetEntity="Registration", mappedBy="user")
     */
    private $registrations;
    // ...
  }

The important thing is that it has a one-to-many relationship with another entity, and you want to be able to join to that via the QueryBuilder for whatever reason (for example, you may want to add a HAVING clause to your query to select only those entities with one or more of these other entities).

Your original code might look like:

  $qb = $this->createQueryBuilder();

  $query = $qb
    ->select('u')
    ->add('from', '\YourBundle\ORM\Model\User u')
    ->leftJoin('\YourBundle\ORM\Model\Registration', 'r', 'WITH', 'u.id = r.user')
    ->groupBy('u.id')
    ->having($qb->expr()->gte($qb->expr()->count('u.registrations'), '1')
    ->getQuery();

This will throw the exception: Cannot count query which selects two FROM components, cannot make distinction

To fix this, rewrite the query so that the QueryBuilder only has one "from" component -- exactly as the exception indicates -- by moving the join inline. Like so:

  $qb = $this->createQueryBuilder();

  $query = $qb
    ->select('u')
    ->add('from', '\YourBundle\ORM\Model\User u LEFT JOIN u.registrations r')
    ->groupBy('u.id')
    ->having($qb->expr()->gte($qb->expr()->count('r'), '1')
    ->getQuery();

Should work just fine with Doctrine's Paginator class, with no other bundles necessary. Also note the shorthand syntax on the join; with Doctrine you don't seen to specify the joined entity explicitly (although you can) since the mapping already knows what it is.

Hope this helps somebody out, there isn't a lot on this issue out there. You can read more about how the internals handle this by looking at @halfer's comment here.

Answer 3

Updating doctrine/dbal to version 2.5 fixed this for me.