Laravel：如何用Eloquent计算不同表多组乘积的总和？

嘿，我来帮你搞定这个问题！首先咱们明确核心需求：从pricings和carts两个表中，计算多组字段（比如shirt×quantity_shirt、pants×quantity_pants等）的乘积总和。下面分两种常用场景给你演示实现方式：

一、先搞定模型关联 #

首先得给Pricing和Cart模型建立对应关联，匹配你原生查询里的sp_id关联条件：

在Cart模型中定义关联： #

// app/Models/Cart.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Cart extends Model
{
    // 关联到对应sp_id的Pricing记录
    public function pricing()
    {
        return $this->belongsTo(Pricing::class, 'sp_id', 'sp_id');
    }
}

在Pricing模型中定义关联： #

// app/Models/Pricing.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Pricing extends Model
{
    // 关联到该sp_id下的所有Cart记录
    public function carts()
    {
        return $this->hasMany(Cart::class, 'sp_id', 'sp_id');
    }
}

二、场景1：单条Cart记录的多组乘积总和 #

如果是针对单个cart_id计算多组乘积总和，用Eloquent关联查询会非常直观：

public function cartreview(Request $request, $sp_id, $service_id, $cart_id)
{
    // 找到指定购物车并预加载关联的定价数据
    $cart = Cart::with('pricing')
        ->where('id', $cart_id)
        ->where('sp_id', $sp_id)
        ->first();

    // 处理购物车不存在的情况
    if (!$cart) {
        abort(404, '购物车不存在');
    }

    // 计算每组的乘积（用?? 0兜底避免null值影响）
    $totalShirt = ($cart->pricing->shirt ?? 0) * ($cart->quantity_shirt ?? 0);
    $totalPants = ($cart->pricing->pants ?? 0) * ($cart->quantity_pants ?? 0);
    $totalShoes = ($cart->pricing->shoes ?? 0) * ($cart->quantity_shoes ?? 0);

    // 计算总总和
    $grandTotal = $totalShirt + $totalPants + $totalShoes;

    // 返回分组明细和总总和
    return response()->json([
        'breakdown' => [
            'shirt' => $totalShirt,
            'pants' => $totalPants,
            'shoes' => $totalShoes
        ],
        'grand_total' => $grandTotal
    ]);
}

三、场景2：多条记录的多组乘积总和（数据库层面计算） #

如果需要批量计算多条记录的总和，直接用Eloquent查询构造器结合DB::raw在数据库层面计算会更高效（避免把所有数据拉到PHP内存处理）：

方式1：仅计算所有组的总总和 #

public function cartreview(Request $request, $sp_id, $service_id, $cart_id)
{
    $total = Pricing::join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
        ->select(DB::raw('
            SUM(COALESCE(pricings.shirt, 0) * COALESCE(carts.quantity_shirt, 0)) +
            SUM(COALESCE(pricings.pants, 0) * COALESCE(carts.quantity_pants, 0)) +
            SUM(COALESCE(pricings.shoes, 0) * COALESCE(carts.quantity_shoes, 0)) AS grand_total
        '))
        ->where('pricings.sp_id', $sp_id)
        ->where('carts.id', $cart_id)
        ->first();

    // 取出总总和（兜底避免null）
    $grandTotal = $total->grand_total ?? 0;
}

方式2：同时获取每组明细总和+总总和 #

public function cartreview(Request $request, $sp_id, $service_id, $cart_id)
{
    $breakdown = Pricing::join('carts', 'carts.sp_id', '=', 'pricings.sp_id')
        ->select(
            DB::raw('SUM(COALESCE(pricings.shirt, 0) * COALESCE(carts.quantity_shirt, 0)) AS total_shirt'),
            DB::raw('SUM(COALESCE(pricings.pants, 0) * COALESCE(carts.quantity_pants, 0)) AS total_pants'),
            DB::raw('SUM(COALESCE(pricings.shoes, 0) * COALESCE(carts.quantity_shoes, 0)) AS total_shoes'),
            DB::raw('
                SUM(COALESCE(pricings.shirt, 0) * COALESCE(carts.quantity_shirt, 0)) +
                SUM(COALESCE(pricings.pants, 0) * COALESCE(carts.quantity_pants, 0)) +
                SUM(COALESCE(pricings.shoes, 0) * COALESCE(carts.quantity_shoes, 0)) AS grand_total
            ')
        )
        ->where('pricings.sp_id', $sp_id)
        ->where('carts.id', $cart_id)
        ->first();

    // 直接访问各字段：$breakdown->total_shirt、$breakdown->grand_total等
}

这里用COALESCE是为了处理字段值为null的情况，避免乘积结果变成null影响总和计算。

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