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?

I've got for now one idea: get all records, check which order has changed and update only them. But i don't feel satisfied because of that solution. So it'll do one SELECT to get all records, few selects(to get entity objects) and few updates(or maybe one, because i think that update is invoked after flush()). — Wojciech Kulik, Jan 06 '12 at 22:40
If there is no good solution using doctrine2 maybe i should do it in clear PHP and use CASE WHEN ... THEN... in sql query? — Wojciech Kulik, Jan 07 '12 at 00:58

score 8 · Accepted Answer · answered Jan 07 '12 at 15:58

8

Something like...

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

$em->flush();

answered Jan 07 '12 at 15:58

Kris Wallsmith

8,945
1
37
25

seems nice to me - one select, one update. I totally forgot that I can pass array as argument in findById. :) – Wojciech Kulik Jan 07 '12 at 21:05
6

I just implemented this in my symfony project and from watching the app/dev.log it does one select and one update per record - not one update – semateos Jul 11 '12 at 20:50

score 4 · Answer 2 · answered Apr 03 '12 at 13:26

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

Symfony - update multiple records

2 Answers2

Linked