Update different rows with different values in one query!

Some example:

$commentsOderdListData = [
  ['id'=>1,'sortOrder'=> 0],
  ['id'=>2,'sortOrder'=> 2],
  ['id'=>3,'sortOrder'=> 1],
  ['id'=>4,'sortOrder'=> 3]
];

I know maybe something like this will work:

$postID = 2;
$post =  $this->model->where('id',  $postID )->firstOrFail();

foreach($commentsOderdListData as $data){
   $post->comments()->where('id'=>$data['id'])->update(['sortOrder'=> $data['sortOrder']]);
}

Is there a way to just update this data in one query, for best performance ?

Tray2

7 years ago

Level 73

Check this thread https://laracasts.com/discuss/channels/laravel/how-to-renumber-order-by-column-in-database-with-laravel

Ranx99

7 years ago

Level 2

OK I have done it , In tow ways..

1 ( this one is what I was looking for ) : thanks to @Tray2

$ids_array = array_pluck($commentsOderdListData, 'id');
$ids_strings = implode(',', $ids_array);

DB::statement(DB::raw('set @rownum=0'));

$result = $post->comments()->whereIn('id', $ids_array)
->orderByRaw("FIELD(id, {$ids_strings})")
->update([
    'sortOrder' => DB::raw('@rownum  := @rownum  + 1')
]);

2 ( which uses CASE Function) => set different rows with different values

$postID = 3;
$cases = [];
$ids = [];
$params = [];

foreach ($commentsOderdListDataas $index => $array) {
    $id = (int) $array['id'];
    $cases[] = "WHEN `id` = {$id} then ?";
    $params[] = $index;
    $ids[] = $id;
}

//array to string
$ids = implode(',', $ids);

//add a space between each case
$cases = implode(' ', $cases);

return DB::statement("UPDATE `comments` SET `sortOrder` = (CASE {$cases} END) WHERE `post_id` = {$postID} AND `id` IN ({$ids})", $params);

Please or to participate in this conversation.