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.

Answer 4

If you aren't working with regular mapped entities in the way supplied in the answers above, joins will create additional components in the result set. This means the set cannot be counted since its not a scalar result.

So the key is keeping your result sets to one component which will allow a scalar result to be produced and counted.

You can convert a regular field to an entity mapping at run time. This allows you to write queries with only one component returned even if you're joining other entities at random. The 'other entities' become a child of the main entity as opposed to an additional root entity or extra component of the result.

The example below shows a one-to-one mapping. I haven't tried more complex mappings but my success with this suggests it should be possible. The Registration entity has a field called user that contains a user Id, but its not a mapped entity just a plain field.

(This has been modified from a working example but not tested as is - so treat as pseudo-code)

    $queryBuilder // The query builder already has the other entities added.
        ->innerJoin('r.user', 'user')
    ;


    $mapping['fieldName'] = 'user';
    $mapping['targetEntity'] = '\YourBundle\ORM\Model\User';
    $mapping['sourceEntity'] = '\YourBundle\ORM\Model\Registration';
    $mapping['sourceToTargetKeyColumns'] = array('user' => 'id');
    $mapping['targetToSourceKeyColumns'] = array('id' => 'user');
    $mapping['fetch'] = 2;
    $mapping['joinColumns'] = array(
        array(
            'name' => 'user',
            'unique' => false,
            'nullable' => false,
            'onDelete' => null,
            'columnDefinition' => null,
            'referencedColumnName' => 'id',
        )
    );
    $mapping['mappedBy'] = 'user';
    $mapping['inversedBy'] = null; // User doesn't have to link to registrations
    $mapping['orphanRemoval'] = false;
    $mapping['isOwningSide'] = true;
    $mapping['type'] = ClassMetadataInfo::MANY_TO_ONE;

    $vm = $this->em->getClassMetadata('YourBundle:Registration');

    $vm->associationMappings["user"] = $mapping;

Note: I was using PagerFanta not KNP - but the problem lies with Doctrine so I expect this will work anywhere.