4

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.

Community
  • 1
  • 1
Damaged Organic
  • 8,175
  • 6
  • 58
  • 84
  • Please reference all the existing question and answers in your question and explain for each one why it didn't work for you so it is clear what makes your question different from the existing ones. – hakre Dec 27 '14 at 12:07
  • 2
    Yes, it is totally correct, that the manager will create one update query for each user object. This is what the ORM is for. If that is not what you want, you're using the wrong tool for the job. Instead of the OOP API, try DQL: Doctrine Query Language or Native SQL - Both are available with the entity manager as well. – hakre Dec 27 '14 at 12:13
  • And it can just be that you have multiple queries there. I don't think that those three update queries really hurt you, do they? – hakre Dec 27 '14 at 12:15
  • Of course, tiny queries in small numbers won't do any harm, but as system grows, I'll get a bottleneck. I'm really surprised that such common and simple task brings pain, and is not solved on SO... – Damaged Organic Dec 27 '14 at 12:22
  • 1
    if you run into a bottleneck, port to DQL and if that even doesn't scale any longer to SQL. But if it's not causing you any problems, don't bother. Nobody said that an ORM is fast. – hakre Dec 27 '14 at 12:29
  • Got your point, thanks : ) – Damaged Organic Dec 27 '14 at 12:32
  • BTW, querybuilder / DQL has [`CASE-WHEN`](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#case-expressions) which is quite like that `CASE-THEN`: [How to convert a complex MySQL Query to Doctrine2](http://stackoverflow.com/q/9793684/367456) – hakre Dec 27 '14 at 12:43
  • Indeed... Will check that, thank you. – Damaged Organic Dec 29 '14 at 09:12
  • https://stackoverflow.com/questions/39848929/symfony3-update-multiple-entities-in-single-sql-query – Balachandran Jul 15 '17 at 12:38
  • 1
    @Balachandran Thanks, but the question is about query builder, not DQL. – Damaged Organic Jul 17 '17 at 07:13
  • I think QueryBuilder is not suitable for your question – Balachandran Jul 17 '17 at 07:58
  • @Balachandran The problem is in coupling. Using QB you can abstract away the implementation and eventually turn to other ORM/framework/language, but with DQL or MySQL-specific solution you get tightly coupled to concrete code that is "written in blood". – Damaged Organic Jul 17 '17 at 08:20

0 Answers0