0

I wish to update table B based on source table A so that if a new record is found, we can insert it otherwise update the record if any values have changed. How would I write the query in laravel 4 using eloquent?

Table A                                  Table B
=======                                  =======

Name         | Color                     Name         | Color
----------------------                   ----------------------
Mickey Mouse | grey                      Mickey Mouse | red
Donald Duck2 | green                     Donald Duck  | blue
Donald Duck  | blue                      Minnie       | red
Goofy        | black
Minnie       | red

In this example table B should be inserted with the rows Goofy and Donald Duck2 and the row mickey mouse should be updated with the new color grey.

mr_abbasi
  • 143
  • 1
  • 1
  • 12

1 Answers1

2

This should work: 

$a = A::all(); 

foreach($a as $current)
{ 
  $b = B::where("name", "=", $current->name)->first();
  if(!$b) $b = new B; 

  if($b->name != $current->name)
  {
   $b->name = $current->name; 
   $b->save(); 
 }
}

Or more optimized: 

$a = A::all(); 
// using this script: http://stackoverflow.com/questions/12050892/array-mapping-in-php-w ith-keys

   $aFolded = array_reduce($a, function(&$result, $item){ 
                                $result[$item->name] = $item->color;
                                return $result;
                               }, array());

// so we now have array ( name => color, name2 => color2 ) 

$b = B::with("a", "name")->get(); // might recheck with laravel doc
foreach($b as $updateMe)
{
  if($updateMe->color != $aFolded[$b->name])
  {
   $updateMe->color = $aFolded[$b->name]; 
   $updateMe->save();
   unset($aFolded[$b->name]); 
  }
}
// after this $aFolded will only contain the names which do not exist in b so we can just insert them all (maybe the insert query could be better) 
foreach($aFolded as $name => $color)
{
 DB::table("B")->insert(array("name" => $name, "color" => $color));  
}
Qullbrune
  • 1,925
  • 2
  • 20
  • 20
  • This would surely work but could we do it another way without actually looping through all the records? – mr_abbasi Nov 19 '13 at 16:35
  • You could first get all names, which are already given and update them and than you could insert all other, but that wouldn´t work using eloquent, since it does not support "where in".. – Qullbrune Nov 22 '13 at 12:45
  • But you could do someting like: Table::whereIn('name', $names)->get(); Why not update? – mr_abbasi Nov 22 '13 at 13:49
  • If you want to do this perfectly you have to take all b where name is in a and where name of b != name of a, so you would just select all entries, which need to be updated, but i don´t think this is possible using eloquent – Qullbrune Nov 23 '13 at 13:27