How to add two columns value and perform where con

Question

This is my table:

id       remaining_amount     additional_amount
1             200                   0
2             100                -100
3             300                 100
4             200                 -50

I'm trying to get the rows which have a sum of remaining_amount + additional_amount > 0 .

$result = $this->model->where('remaining_amount' + 'total' > 0)->get();

it didn't work. Then I tried like this:

$result = DB::table('cash')->select(DB::raw('remaining_amount+additional_amount AS total'))
                ->where('total','>',0)
                ->get();

It doesn't work either.

Please show me the way to solve this.

Answer 1

Try this using whereRaw:

$query = DB::table('cash')
    ->whereRaw("(remaining_amount+additional_amount AS total) > 0)")
    ->get();

Answer 2

You can not perform where condition on calculated value. Try using filter from laravel collection to get the desired output.

$result = DB::table('cash')
    ->select(DB::raw('(remaining_amount+additional_amount) AS total'))
    ->get();

Performing filter

return collect($result)->filter(function($value){
    return $value->total > 0;
})->values()->all();

Answer 3

$result = DB::table('cash')->get();

$result = collect($result)->filter(function($value){
    return $value->remaining_amount + $value->additional_amount > 0;
    })->values()->all();

Got it. Thanks

Answer 4

I think you sohuld use aggregate method to sum as

$result = DB::table('cash')->SELECT SUM(remaining_amount)+additional_amount AS total')->where('total','>',0)->get();

Hope it will work for you