Laravel用一个条件计算两列之和
我有一个从数据库中获得的仓库集合
[
{
"id": 1,
"warehouse": "India"
"sales": [
{
"id": 1,
"warehouse_id": 1,
"price": "120.00",
"quantity": 1000,
"status": 1
},
{
"id": 2,
"warehouse_id": 1,
"price": "20.00",
"quantity": 100,
"status": 1
},
{
"id": 3,
"warehouse_id": 1,
"price": "40.00",
"quantity": 1000,
"status": 2
}
]
},
{
"id": 2,
"warehouse": "Malaysia"
"sales": [
{
"id": 4,
"warehouse_id": 2,
"price": "160.00",
"quantity": 100,
"status": 1
}
]
}
]
我想计算每个仓库的总收入
总收入根据销售额status
属性计算
如果Status=1,则产品已交付,因此应将price * quantity
添加到总收入
如果status=2,则返回产品,因此应从总收入中减去price * quantity
印度仓库基本示例:
total_income = 120*1000 + 20*100 - 40*1000
马来西亚:
total_income = 160*100
我尝试使用Warehouse::withSum();
,但没有任何效果。
我想知道有没有收藏的好办法
解决方案
您只需向集合的sum()
方法传递几个回调:
$warehouses_collection->map(function ($warehouse) {
return (object) [
'id' => $warehouse->id,
'warehouse' => $warehouse->warehouse,
'total_income' => collect($warehouse->sales)->sum(function ($sale) {
((int) $sale->price) * $sale->quantity * ($sale->status == 1 ? 1 : -1)
})
];
});
WithSum
在这里使用有点麻烦,但调用withAggregate
可以。
Warehouse::withAggregate(
'sales as total_income',
'sum(case when status = 1 then price * quantity when status = 2 then price * quantity * -1 else 0 end)'
)->get()
相关文章