Doctrine Query Builder not working with UPDATE and INNER JOIN

Question

In my repository, I have this request:

$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 checked the attitude and that’s right. Is there a problem connecting to updating the request?

Answer 1

You cannot use the connection to update and delete requests. You must use subqueries.

Connections are not supported when updating and deleting requests, because it is not supported on all dbms. It will not be implemented in Doctrine 1 or Doctrine 2. However, you can get the same effect using subqueries.

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

If you use MySQL, using subqueries will not work. Then you will need 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

Answer 2

Doctrine DQL does not support connection in the update.

Try 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 if it is the primary key of the associated object directly, as if it were an object, Doctrine will display it.

I do the same in my queries and it works.

Answer 3

try using a subquery instead of Join will not work in DQL when updating :

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

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