Doctrine Query Builder not working with UPDATE and INNER JOIN

Question

In my repository I have this query:

$qb = $this->getEntityManager()->createQueryBuilder();
$qb
    ->update('MyBundle:Entity1', 'e1') 
    ->join('e1.Entity2', 'e2')
    ->set('e1.visibile', '1')
    ->andWhere('e2.id = :id')->setParameter("id", 123)
;

throw this error

[Semantical Error] line 0, col 66 near 'e2.id = :id': Error: 'e2' is not defined

I have checked the relation and it is right. Is there any issue using join in query update?

Answer 1

Very old question, but do not contain an answer in full query builder.

So yes, the following query is not possible to sync fields of two tables:

$this->createQueryBuilder('v')
    ->update()
    ->join(Pegass::class, 'p', Join::WITH, 'v.identifier = p.identifier')
    ->set('v.enabled', 'p.enabled')
    ->where('p.type = :type')
    ->setParameter('type', Pegass::TYPE_VOLUNTEER)
    ->andWhere('v.enabled <> p.enabled');

The generated query do not contain the relation because of its lack of support in all dbms as explained above. They also tell you to use subqueries instead.

So that's how I did the equivalent (even if using 2 queries and is less performant...):

foreach ([false, true] as $enabled) {
    $qb = $this->createQueryBuilder('v');

    $sub = $this->_em->createQueryBuilder()
        ->select('p.identifier')
        ->from(Pegass::class, 'p')
        ->where('p.type = :type')
        ->andWhere('p.enabled = :enabled');

    $qb
        ->setParameter('type', Pegass::TYPE_VOLUNTEER)
        ->setParameter('enabled', $enabled);

    $qb
        ->update()
        ->set('v.enabled', $enabled)
        ->where($qb->expr()->in('v.identifier', $sub->getDQL()))
        ->getQuery()
        ->execute();
}

Answer 2

try using a subquery instead Join will not work in DQL while you re doing an update:

LEFT JOIN, or JOINs in particular are only supported in UPDATE statements of MySQL. DQL abstracts a subset of common ansi sql, so this is not possible. Try with a subselect:

$qb = $this->getEntityManager()->createQueryBuilder();
    $qb ->update('MyBundle:Entity1', 'e') 
        ->set('e.visibile', '1')
        ->where('e.id IN (SELECT e1.id FROM Entity1 e1 INNER JOIN e2.Entity2 e2 WHERE e2 = :id')
        ->setParameter("id", 123);