多对多关系下两表统计值相减：计算产品库存余量

现有两张数据表：

1. products表：存储产品采购记录，同一产品名称可能有多条记录，结构如下：

2. InvoiceItems表：存储上述产品的销售发票记录。

已定义Laravel模型关系，并通过以下查询分别统计了各产品的总采购量和总销售量：

$table1= DB::table('products')
    ->groupBy('products.name')
    ->select(DB::raw('products.name, sum(products.qty) as totalqty'))
    ->get();

$table2 = DB::table('invoice_items')
    ->groupBy('invoice_items.name')
    ->select(DB::raw('invoice_items.name, sum(invoice_items.product_qty) as soldqty')) 
    ->get();

现在需要计算产品库存余量：Balance Qty = totalqty - soldqty，如何实现两表统计结果的差值计算？

先修正模型关系错误 #

原InvoiceItem模型的products方法使用了belongsToMany，不符合业务逻辑——一个发票明细项应归属单个产品，正确的关联关系应为belongsTo：

class InvoiceItem extends Model
{
    public function product()
    {
        return $this->belongsTo(\App\Models\Products::class, 'product_id', 'id');
    }
}

Products模型的soldproducts关联是正确的，保持不变：

class Products extends Model
{
    public function soldproducts(): HasMany
    {
        return $this->hasMany(InvoiceItem::class, 'product_id','id');
    }
}

方案一：SQL左连接一次性查询（推荐） #

直接通过数据库关联查询完成统计与计算，避免多次查询后再处理数据，效率更高：

$inventory = DB::table('products')
    ->select(
        'products.name',
        DB::raw('SUM(products.qty) as totalqty'),
        DB::raw('COALESCE(SUM(invoice_items.product_qty), 0) as soldqty'),
        DB::raw('SUM(products.qty) - COALESCE(SUM(invoice_items.product_qty), 0) as balanceqty')
    )
    ->leftJoin('invoice_items', 'products.name', '=', 'invoice_items.name')
    ->groupBy('products.name')
    ->get();

• LEFT JOIN确保所有采购过的产品都能被统计到，即使没有销售记录；
• COALESCE函数处理无销售记录的场景，将soldqty默认设为0，避免NULL值导致计算错误。

方案二：Laravel集合处理已有查询结果 #

如果已经获取了$table1和$table2的结果，可以通过集合方法合并计算：

// 将销售统计集合转换为「产品名 => 销售量」的映射
$soldMap = $table2->pluck('soldqty', 'name');

// 遍历采购统计集合，计算每个产品的库存余量
$inventory = $table1->map(function ($item) use ($soldMap) {
    $item->soldqty = $soldMap->get($item->name, 0);
    $item->balanceqty = $item->totalqty - $item->soldqty;
    return $item;
});

• pluck('soldqty', 'name')快速构建产品与销售量的关联映射；
• map遍历采购数据，通过产品名匹配销售量，无匹配时默认取0，最终计算余量。

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