CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-orm
TiPi
TiPi

Reputation: 330

Doctrine bulk DQL update with join

I need to update a large collection of entities (~100k) with a few set of values. To do this, I want to use a DQL update Query.

The problem came from the where clause of my query. I need to filter the entities to update.

$qb->update('MyBundle/Entity/MyEntity', 'e')
->set('e.fieldToUpdate', ':value')
->innerJoin('e.relation', 'r')
->where('r.filter < :filter')
->setParameters...

Unfortunatly joins are not supported on update and delete queries. And I won't be able to use a where clause with e.id IN "subquery" since MySQL documentation says :

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

I'd like to avoid using Query#iterate() facility or other solutions based on loop for performance reason, so...I don't know how to deal with this.

It sounds like a common problem, and I may miss something very obvious...so if there is a workaround to do this, i'll be glad to read it !

Upvotes: 1

Views: 7979

Answers (1)

TiPi
TiPi

Reputation: 330

Thanks to Miro, I found a pretty obvious solution (of course)...

Since I can't select inside the subquery the same table I'm updating, I had to select the relation, from another table.

Doctrine doesn't allow to select something like r.mytable where 'mytable` is the Entity I am targeting, but, there is a DQL function to do this : IDENTITY

For instance : 

    $dql = $queryBuilder
        ->from('Relation', 'r')
        ->select('IDENTITY(r.myEntity)')
        ->where('r.filter > :filter')
        ->getDQL()
    ;  

    $queryBuilder = $this
        ->createQueryBuilder('e')
        ->update('MyBundle/Entity/MyEntity', 'e')
        ->set('e.fieldToUpdate', ':value')
        ->where(
            $queryBuilder->expr()->In('e.id', $dql)
        )
        ->setParameters([
            'filter'      => $filter
        ]);

Upvotes: 5

Related Questions