Symfony - Updating Multiple Entries

Question

What is the best way to update multiple records in a database using doctrine, symfony2?

I get an array of record identifiers that I have to update.
I want to assign each index my index from the resulting array to the show_order column. Therefore, if I get an array $ array = array (22, 1, 5, 10), then I want to do

$i = 0; foreach($array as $a) { $record = $this->getDoctrine->getRepository('AcmeBundle:SomeEntity')->findOneById($a); if ($record != null) $record->setOrder($i++); } $this->getDoctrine()->getEntityManager()->flush(); 

but this is terrible because for each record I make one SELECT, so the number of queries is O (n).

How to do it better?

Answer 1

Something like...

 foreach ($repo->findById($ids) as $obj) { $obj->setOrder(array_search($obj->getId(), $ids)); } $em->flush(); 

Answer 2

As a first option, you should consider Batch Processing . If for some reason this is not suitable for you, the second option is to use plain SQL, possibly through DBAL .

Answer 3

So this is still 0 (n), but it is 1n, not 2n. To avoid unnecessary selections, I solved this problem using the user repository class and the doctrine query builder as follows:

 namespace BRS\PageBundle\Repository; use Doctrine\ORM\EntityRepository; class ContentRepository extends EntityRepository { public function reorder($content) { $em = $this->getEntityManager(); $count = 0; foreach($content as $i => $content_id){ $q = $em->createQuery('update BRSPageBundle:Content c set c.display_order = ?1 where c.id = ?2') ->setParameter(1, $i) ->setParameter(2, $content_id); $count += $q->execute(); } return $count; } } 

let's say that you have an array of content identifiers, for example:

 $content = array(23,12,8,4); 

Then you can quite simply update the order from your controller:

 $count = $this->getRepository('BRSPageBundle:Content')->reorder($content);