CODEWITHSUNDEEP
databasesymfonydoctrine-ormdql
Alessandro Pessina
Alessandro Pessina

Reputation: 263

Doctrine Query Builder not working with UPDATE and INNER JOIN

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

Answers (4)

Alain
Alain

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

Jeffrey Nicholson Carr&#233;
Jeffrey Nicholson Carr&#233;

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

Maxence
Maxence

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

Hugo Dozois
Hugo Dozois

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

Related Questions