SQL Query for Updating Rows with New Values from Temporary Table in Laravel/Eloquent

Question

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.

Qullbrune · Accepted Answer · 2013-11-23 13:42:05Z

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

edited Nov 23, 2013 at 13:42

answered Nov 19, 2013 at 9:43

Qullbrune

1,9452 gold badges20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mr_abbasi Over a year ago

This would surely work but could we do it another way without actually looping through all the records?

Qullbrune Over a year ago

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"..

mr_abbasi Over a year ago

But you could do someting like: Table::whereIn('name', $names)->get(); Why not update?

Qullbrune Over a year ago

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

Collectives™ on Stack Overflow

SQL Query for Updating Rows with New Values from Temporary Table in Laravel/Eloquent

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related