Symfony3 - Update multiple entities in single sql query

Question

Bouncing from this old post i am looking for a way to update several Symfony entites in one sql query - for optimisation reasons. I have a Content entity, and a method in a service, that updates all "sibling" contents with setCurrent(0). Here's the working code so far:

/**
 * Set given content the latest regarding its siblings
 */
public function setLatestContent(Entity\Content $content)
{
    // Get siblings entities
    $siblings = $this
        ->em
        ->getRepository('LPFrontRteBundle:Content')
        ->findBySibling($content);

    foreach ($siblings as $sibling_content) {
        $sibling_content->setCurrent(0);
    }

    $this->em->flush();
}

Well that works. But since i have 38 sibling contents, i get 38 SQL queries like:

UPDATE mr_content SET current = 0 WHERE id = 55
UPDATE mr_content SET current = 0 WHERE id = 56
UPDATE mr_content SET current = 0 WHERE id = 57
...

I would like to use Doctrine's "clean" Entity system, in a way to have one query like:

UPDATE mr_content SET current = 0 WHERE id = 55 OR id = 56 OR id = 57 ...

Any thoughts on how to achieve that - or a smarter workaround, would be greatly appreciated.

EDIT

For the record, here's what i came up with - i like it verbose :) With $qb as query builder.

    $qb->update('LPFrontRteBundle:Content', 'c')
        ->set('c.current', 0)

        ->where('c.keyword = :keyword')
        ->setParameter('keyword', $content->getKeyword())

        ->andWhere('c.locale = :locale')
        ->setParameter('locale', $content->getLocale())

        ->andWhere('c.id != :id')
        ->setParameter('id', $content->getId())

        ->getQuery()->execute();

Jakub Matczak · Accepted Answer · 2016-10-04T09:58:35.097

6

Batch processing is what you're looking for.

There are two bulk update methods in Doctrine2.

First one is DQL Update Query, which will best probably best for you. It would be something like:

$q = $em->createQuery("UPDATE LPFrontRteBundle:Content c SET c.current = 0 WHERE id IN (:ids)")
        ->setParameter(':ids', $ids, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY));
$numUpdated = $q->execute();

edited Oct 04 '16 at 09:58

answered Oct 04 '16 at 09:53

Jakub Matczak

15,341
5
46
64

Thanks! I finally went with a very close solution that i will post, but this is indeed what i was looking for :) – Loïc Pennamen Oct 04 '16 at 10:13

Max P. · Answer 2 · 2016-10-04T10:30:03.167

2

You can gather all ids and execute DQL to update all entities at once

$ids = array();
foreach ($siblings as $sibling_content) {
    $ids[] = $sibling_content->getId();
}

$this->em->createQuery("
    UPDATE
        LPFrontRteBundle:Content t
    SET
        t.current = 0
    WHERE
        t.id IN (:ids)
")
->setParameter(':ids', $ids, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY)
->execute();

edited Oct 04 '16 at 10:30

answered Oct 04 '16 at 09:51

Max P.

5,579
2
13
32

Thanks! What i did was skipping the "entities fetching" part to directly update from query. I read Repos are for "fetching" purpose only, but maybe it's only a philosophical matter? – Loïc Pennamen Oct 04 '16 at 10:16
I think that you are right and and some service is more applicable for so operations. In my project I place so operations in repository for simplicity :) I'll delete 2-nd part of my answer not to confuse other users – Max P. Oct 04 '16 at 10:29

Symfony3 - Update multiple entities in single sql query

2 Answers2

Linked