[FIXED] How to apply subtraction in the query in laravel?

Issue

I have one array which contains all the records. I am passing all those records to a loop and there I am getting the sum of the clear column which has null upto.

This part is working fine, now my requirement is I want to add one more check which is based on the one more column refund.

If any column contains refund=1 that amount should be subtracted from the total, can you please help me to achieve the scenario

foreach ($row_data as $key => $value) {
    $data_arr[$key]['total'] = ['Price'=> $value->whereIn('clear', null)->sum('amount')];
}

Data

data = [
    {'amount' => 55, 'clear' => 'null', 'refund' => '0'},
    {'amount' => 5,  'clear' => 'null', 'refund' => '1'},
    {'amount' => 10, 'clear' => 'null', 'refund' => '0'},
];

Expected result is :-60

ACtual Result is :- 70

Solution

reduce should help.

$data = [
    ['amount' => 55, 'clear' => null, 'refund' => '0'],
    ['amount' => 5,  'clear' => null, 'refund' => '1'],
    ['amount' => 10, 'clear' => null, 'refund' => '0'],
];

You can be very explicit

// sum if refund is 0, substract if refund is 1
$sum = collect($data)
    ->where('clear', null)
    ->reduce(function ($carry, $item) {
        if ($item['refund'] == '0') {
            return $carry + $item['amount'];
        } elseif ($item['refund'] == '1') {
            return $carry - $item['amount'];
        }
    }, 0);

Or write it a bit shorter

// sum if refund is 0, substract otherwise
$sum = collect($data)
    ->where('clear', null)
    ->reduce(fn($carry, $item) => ($item['refund'] == '0')
        ? $carry + $item['amount']
        : $carry - $item['amount']);

You can even do it without collections using php's array_reduce and array_filter functions. Works pretty much in the same way.

$sum = array_reduce(
    array_filter($data, function ($item) {
        return $item['clear'] == null;
    }),
    function($carry, $item) {
        return $item['refund'] == '0'
            ? $carry + $item['amount']
            : $carry - $item['amount'];
});

$sum = array_reduce(
    array_filter($data, fn($item) => $item['clear'] == null)
    fn($carry, $item) => $item['refund'] == '0'
        ? $carry + $item['amount']
        : $carry - $item['amount']
);

Answered By - IGP