Laravel withSum of two columns with additional where clause-CodePudding

I have one to many relationship between Warehouse and Sale models

warehouse.php

public function sales () {
    return $this->hasMany(Sale::class);
}

The sales table has product_price, quantity and status columns.

I want to calculate product_price * quantity based on the status column.

If status is equal to 1 then sum = product_price * quantity and if it's to 2 then sum-=product_price * quantity

The status is basically tells if the products are delivered or returned

I tried to do it the eloquent using withSum but I didn't seem to get anywhere. I'm wondering if anyone has a solution for that.

CodePudding user response：

You can do it at the collection level after getting all related sales from a Warehouse like so:

$sales = $warehouse->sales()->select([
  'id', 'product_price', 'quantity', 'status'
])->get();

$sum = 0;

$sales->each(function($sale) use(&$sum) {
    $amount = $sale->product_price * $sale->quantity;
    $sale->status == 1 ? $sum  = $amount : $sum -= $amount;
});

CodePudding user response：

$wharehouse = Warehouse::findOrFail($warehouse_id)->with('sales')->first();
$sum = 0;
foreach($wharehouse->sales as $sale){
   $amount = $sale->product_price * $sale->quantity;
   $sale->status == 1 ? $sum  = $amount : $sum -= $amount;
}
return $sum;