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?

score 7 · Accepted Answer · answered Jul 31 '13 at 16:03

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;

Thank you so much, but now the error is: [Semantical Error] line 0, col 74 near 'following FROM': Error: Invalid PathExpression. Must be a StateFieldPathExpression. — m4t1t0, Jul 31 '13 at 16:10
@m4t1t0 can this help: http://stackoverflow.com/questions/14216470/symfony2-and-doctrine-error-invalid-pathexpression-must-be-a-statefieldpathe — cheesemacfly, Jul 31 '13 at 16:17

score 1 · Answer 2 · answered Jan 02 '16 at 01:05

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)
        ;
}

}

Error with subquery in doctrine2 query builder

2 Answers2