Symfony - update multiple records

Question

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

I receive array of records ids which I have to update.
I want to assign to each record its index from received array to column show_order. So if i receive 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 it's horrible way, because for each record I do one SELECT, so number of queries is O(n).

How to do it better?

Answer 1

So this is still 0(n), but it's 1n rather than 2n. To avoid the unnecessary selects, I solved this problem using a custom repository class and the doctrine query builder like so:

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;
    }
}

then say you have an array of content ids in order like this:

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

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

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

Answer 2

Something like...

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

$em->flush();

Answer 3

As the first option, you should consider Batch Processing. If that's not viable for you for some reason, the second option is to go with plain SQL, probably via DBAL.