Error with subquery in doctrine2 query builder

Question

I have this query with a subquery:

$query = $this->getEntityManager()->createQueryBuilder();
    $subquery = $query;
    $subquery
        ->select('f.following')
        ->from('ApiBundle:Follow', 'f')
        ->where('f.follower = :follower_id')
        ->setParameter('follower_id', $id)
    ;

    $query
        ->select('c')
        ->from('ApiBundle:Chef', 'c')
        ->where('c.id <> :id')
        ->setParameter('id', $id)
    ;
    $query
        ->andWhere(
            $query->expr()->notIn('c.id', $subquery->getDQL())
        );

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

And I get this error:

[Semantical Error] line 0, col 116 near 'f, ApiBundle:Chef': Error: 'f' is already defined.

I can't find the cause of the error, the alias f is defined only one time. Any suggestions?

Answer 1

This issue is about objects and references in PHP.

When you do $subquery = $query;, $query being an object, you simply have $subquery pointing to the same value.

A PHP reference is an alias, which allows two different variables to write to the same value. As of PHP 5, an object variable doesn't contain the object itself as value anymore. It only contains an object identifier which allows object accessors to find the actual object. When an object is [...] assigned to another variable, the different variables are not aliases: they hold a copy of the identifier, which points to the same object.

Reference: http://us1.php.net/manual/en/language.oop5.references.php

It means in your code that when you write this:

$subquery
    ->select('f.following')
    ->from('ApiBundle:Follow', 'f')
    ->where('f.follower = :follower_id')
    ->setParameter('follower_id', $id)
;

This is equivalent to:

$query
    ->select('f.following')
    ->from('ApiBundle:Follow', 'f')
    ->where('f.follower = :follower_id')
    ->setParameter('follower_id', $id)
;

So when at the end you call:

$query->andWhere(
        $query->expr()->notIn('c.id', $subquery->getDQL())
    );

You are using 2 times the same object pointed by 2 different variables ($query === $subquery).

To solve this issue, you can either use:

$query = $this->getEntityManager()->createQueryBuilder();
$subquery = $this->getEntityManager()->createQueryBuilder();

Or the clone keyword:

$query = $this->getEntityManager()->createQueryBuilder();
$subquery = clone $query;

Answer 2

I would like to share my solution which requires ORM mapping:

Following entities are mapped like this: Event 1:M Participant

Participant class
 /**
 * @ORM\ManyToOne(targetEntity="KKB\TestBundle\Entity\Event", inversedBy="participants")
 * @ORM\JoinColumn(name="event_id", referencedColumnName="id", nullable=false)
 */
private $event;

Event class
/**
 * @ORM\OneToMany(targetEntity="KKB\TestBundle\Entity\Participant", mappedBy="event", cascade={"persist"})
 */
private $participants;


class EventRepository extends \Doctrine\ORM\EntityRepository
{

public function getEventList($userId)
{

    $query = $this->createQueryBuilder('e');
    $subquery = $this->createQueryBuilder('se');

    $subquery
        ->leftJoin('se.participants', 'p')
        ->where('p.user = :userId')
        ;

    return $query->where($query->expr()->notIn('e.id', $subquery->getDQL()))
        ->setParameter('userId', $userId)
        ;
}

}