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?

Catabolite

do e1 have Entity2 memeber into its class? – Consciencestricken

Try printing your DQL to see what could went wrong. ($qb->getQuery()->getDQL()) – Unassuming

Your error doesnt match your code - the error shows :use - where is that in your code ? – Fatal

generated DQL: UPDATE MyBundle:Entity1 e1 SET e1.visibile = 1 WHERE e2.id = :id – Catabolite

@AlessandroPessina have you try my answer? Did you get any error with it? It should work, I'm using something that is very similar in my project – Rankins

Answer 1

You can not use join on update and delete queries. You have to use subqueries.

Joins are not supported on update and delete queries because it is not supported on all dbms. It won't be implemented in Doctrine 1 or Doctrine 2. You can however get the same affect by using subqueries.

http://www.doctrine-project.org/jira/browse/DC-646

If you are using MySQL, using subqueries will not work. You will have then to use 2 queries.

In MySQL, you cannot modify a table and select from the same table in a subquery

http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

Shrum

Well, usually you join with another table, not with the one you are currently updating, so it should work in MySQL for most cases. – Dasteel

Not in my case,well, you can always add it to the alias, it's not nice, but works ->update('someTable', alias: 'someAlias INNER JOIN anotherTable......') – Tectonic

Answer 2

Doctrine DQL does not support join in update.

Try doing the following :

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

You can set the id, as long as it is the primary key, of the linked entity directly as if it was the entity, Doctrine will map it.

I'm doing the exact same thing in my queries and it works.

Rankins

I'd love to hear what is not helpful or wrong about my answer, to deserve a downvote. That way I can improve my answer! – Rankins

#5595353 u re rigth – Bonnybonnyclabber

Answer 3

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

Kablesh

it doesn't work: SQLSTATE[HY000]: General error: 1093 You can't specify target table 'my_entity1' for update in FROM clause. #45994 – Catabolite

Answer 4

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

Rowley