increase multiple columns in one query

Hi，i wanna to increase multiple column value in one query. below will do the trick


    $data = model::find(24);

            //     $data->Update([
            //     "col_1" => \DB::raw('col_1 + ' .$amount),
            //     "col_2" => \DB::raw('col_2 + ' .$amount),
            //     "col_3" => \DB::raw('col_3 + ' .$amount),
            // ]);

but the db:raw looks ugly , i know i can do it this way

    $data = ShopGoodsModel::find(24);
    $data->increment('col_1');
    $data->increment('col_2');
    $data->increment('col_3');

but this one fire 3 different update query.

are there any efficient and elegant way to handle it with in one query ?

thanks all

CorvS

5 years ago

Level 27

@andiliang You could assign the columns directly and call save() afterwards, that way you have one query.

$data->col_1 += $amount;
$data->col_2 += $amount;
$data->col_3 += $amount;
$data->save();

andiliang

5 years ago

Level 39

ya that is similar to the update method above

CorvS

5 years ago

Best Answer

Level 27

@andiliang Yes, alternatively you could write a function to make it more clean. Especially if you want to increment a lot of columns:

private function incrementColumns($model, $columns, $amount = 1)
{
    foreach ($columns as $column)
        $model->{$column} += $amount;

    $model->save();
}

That's without any checks if the column actually exists ofc.

andiliang

5 years ago

Level 39

thanks Corvs that is better

Please or to participate in this conversation.