The question has some answers on SO, but non of them seems to help accomplish, actually, a simple task.
I need to update multiple rows based on condition in one query, using Doctrine2 QueryBuilder. Most obvious way is supposed to be wrong:
$userAgeList = [
'user_name_a' => 30,
'user_name_b' => 40,
'user_name_c' => 50,
];
//Array of found `User` objects from database
$usersList = $this->getUsersList();
foreach($usersList as $user)
{
$userName = $user->getName();
$user->setAge($userAgeList[$userName]);
$this->_manager->persist($user);
}
$this->_manager->flush();
It will create an update query for each User
object in transaction, but I need only one query. This source suggests that instead you should rely on the UPDATE
query, because in case of it we only execute one SQL UPDATE
statement, so I've made like this:
$userAgeList = [
'user_name_a' => 30,
'user_name_b' => 40,
'user_name_c' => 50,
];
$builder = $this->_manager->getRepository(self::REPOSITORY_USER)
->createQueryBuilder(self::REPOSITORY_USER);
foreach($userAgeList as $userName => $age)
{
$builder->update(self::REPOSITORY_USER, 'user')
->set('user.age', $builder->expr()->literal($age))
->where('user.name = :name')
->setParameter('name', $userName)
->getQuery()->execute();
}
But that also makes (obviously) a bunch of updates instead of one. If I assign result of getQuery()
to a variable, and try to execute()
it after foreach loop, I see that $query understands and accumulates a set()
, but it does no such thing for WHERE
condition.
Is there any way to accomplish such task in QueryBuilder?
UPDATE - similar questions:
Multiple update queries in doctrine and symfony2 - this one does not assume UPDATE in one query;
Symfony - update multiple records - this also says, qoute, 'one select - one update';
Update multiple columns with Doctrine in Symfony - WHERE
condition in this query is always the same, not my case;
Doctrine 2: Update query with query builder - same thing as previous, only one WHERE
clause;
http://doctrine-orm.readthedocs.org/en/latest/reference/batch-processing.html - doctrine batch processing does not mention conditions at all...
In MySQL I used to do it using CASE-THEN
, but that's not supported by Doctrine.