Laravel 5.4: Directly Subtract number from column in database

Question

I want to subtract 100 number from credit column in database that is having int data type. I am looking for the way to directly subtract it with some Laravel query. But right now I first get the column value from the table and then subtract and then need to write update query to update it like below:

$subtractCredit = 100;

// Get Total Credit
$totalCredit = User::select('credit')->where(['username'=>$username])-
>first(); 

// Subtract 100 from Total Credit
$totalCredit = $totalCredit->credit - $subtractCredit;

// Update Credit in Table
User::where(['username'=>$username])->update(['credit' => $totalCredit]);

In the above code, I first get total credit from query and then subtract 100 from total credit and then update credit again with update query.

Please let me know the better approach of doing it.

Solution 1

There's a built-in function to decrement:

User::where('username', $username)->decrement('credit', 100);

Docs: https://laravel.com/docs/5.4/queries#increment-and-decrement

Note that it's sugar for the update statement, so you don't need to call save() or update() afterwards.

Solution 2

You can do it with raw query like,

User::where('username', $username)
->update(array(
    'credit' => DB::raw('credit - 100')
));

I hope you will understand.

Comments

[Amit Gupta]

Thanks! I am trying this in my code and will let you know shortly if it works.

[Amit Gupta]

Your alternative is also correct but I found decrement built-in function of laravel better.

[Sagar Gautam]

@AmitGupta Yes this is one solution but solution provided above is better and you can make variable amount of decrement/increment with above solution

[Joel Hinz]

I'm glad it helped. :)