Reputation: 263
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?
Upvotes: 19
Views: 24374
Reputation: 37004
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();
}
Upvotes: 6
Reputation: 3038
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);
Upvotes: 6
Reputation: 13329
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
Upvotes: 18
Reputation: 8420
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.
Upvotes: 7