Laravel子查询问题：正确计算商品可用库存并转为模型查询

先修正原SQL的null问题 #

原SQL中当in或out类型无交易记录时，sum(qty)会返回null，导致最终available_qty为null。可以用COALESCE函数将null转为0，修正后的SQL如下：

SELECT 
    name,
    (
        COALESCE((SELECT sum(qty) FROM transactions WHERE items.id = transactions.item_id AND type='in'), 0)
        -
        COALESCE((SELECT sum(qty) FROM transactions WHERE items.id = transactions.item_id AND type='out'), 0)
    ) AS available_qty
FROM `items`;

Laravel模型中的实现方式 #

方式一：使用addSelect结合子查询 #

对应修正后的SQL逻辑，在Item模型的查询中通过addSelect添加计算字段：

use Illuminate\Support\Facades\DB;

$items = Item::query()
    ->addSelect([
        'available_qty' => DB::raw('
            COALESCE((SELECT SUM(qty) FROM transactions WHERE transactions.item_id = items.id AND type = "in"), 0)
            -
            COALESCE((SELECT SUM(qty) FROM transactions WHERE transactions.item_id = items.id AND type = "out"), 0)
        ')
    ])
    ->get();

方式二：使用leftJoin分组聚合（性能更优） #

通过关联交易表并分组聚合，避免多次子查询，性能更好：

$items = Item::query()
    ->leftJoin('transactions', 'items.id', '=', 'transactions.item_id')
    ->select([
        'items.*',
        DB::raw('
            COALESCE(SUM(CASE WHEN transactions.type = "in" THEN transactions.qty ELSE 0 END), 0)
            -
            COALESCE(SUM(CASE WHEN transactions.type = "out" THEN transactions.qty ELSE 0 END), 0) AS available_qty
        ')
    ])
    ->groupBy('items.id')
    ->get();

方式三：利用模型关联定义访问器（适合单条数据场景） #

如果需要在获取单个Item实例时直接获取可用库存，可以在Item模型中定义访问器：

// Item模型
public function getAvailableQtyAttribute()
{
    $inQty = $this->transactions()->where('type', 'in')->sum('qty') ?? 0;
    $outQty = $this->transactions()->where('type', 'out')->sum('qty') ?? 0;
    return $inQty - $outQty;
}

// 使用时直接访问属性
$item = Item::find(1);
echo $item->available_qty;

注意：方式三适合获取单条数据，批量获取数据时推荐用方式一或方式二，避免N+1查询问题。

内容的提问来源于stack exchange，提问作者Rex Zednelab