Laravel多对多关联查询利润计算错误排查

表与模型定义 #

1. Project #

// 表迁移
Schema::create('project', function (Blueprint $table) {
    $table->string('kode_proyek')->primary(); // 设置为主键
    $table->string('nama_proyek');
    $table->string('client');
    $table->string('lokasi');
    $table->string('pekerjaan');
    $table->string('status');
    $table->foreignId('vendor_id')->nullable()->default(null)->constrained('vendor')->onDelete('cascade');
    $table->double('budget_rab')->nullable()->default(null);
    $table->timestamps();
});

// 模型关联
public function vendor() {
    return $this->hasOne(Vendor::class, 'vendor_id');
}

public function detailPekerjaan() {
    return $this->hasMany(DetailPekerjaan::class, 'kode_proyek', 'kode_proyek');
}

public function projectCashflow() {
    return $this->hasMany(ProjectCashflow::class, 'kode_proyek', 'kode_proyek');
}

2. Vendor #

// 表迁移
Schema::create('vendor', function (Blueprint $table) {
    $table->id(); // 主键
    $table->string('nama_vendor');
    $table->timestamps(); // created_at 和 updated_at 字段
});

// 模型关联
public function detailPekerjaan() {
    return $this->belongsToMany(DetailPekerjaan::class, 'pekerjaan_vendor_rab', 'vendor_id', 'detail_pekerjaan_id')->withPivot('rab_item_id');
}

3. Detail Pekerjaan #

// 表迁移
Schema::create('detail_pekerjaan', function (Blueprint $table) {
    $table->id();
    $table->string('kode_proyek');
    $table->string('item');
    $table->string('ukuran');
    $table->string('keterangan');
    $table->decimal('harga_penawaran', 10, 2);
    $table->timestamps();

    // 定义外键关联 project 表的 kode_proyek
    $table->foreign('kode_proyek')->references('kode_proyek')->on('project')->onDelete('cascade');
});

// 模型关联
public function proyek()
{
    return $this->belongsTo(Project::class, 'kode_proyek');
}

public function vendors() {
    return $this->belongsToMany(Vendor::class, 'pekerjaan_vendor_rab', 'detail_pekerjaan_id', 'vendor_id')->withPivot('rab_item_id');
}

4. Rab Item #

// 表迁移
Schema::create('rab_item', function (Blueprint $table) {
    $table->id();
    $table->json('data');
    $table->integer('subtotal_material')->nullable()->default(0);
    $table->integer('subtotal_ongkos_kerja')->nullable()->default(0);
    $table->integer('total_material_ongkos_kerja')->default(DB::raw('(subtotal_material + subtotal_ongkos_kerja)'));
    $table->decimal('lain_lain_percent', 5, 2)->nullable()->default(0);
    $table->decimal('jasa_kontraktor_percent', 5, 2)->nullable()->default(0);
    $table->integer('grand_total')->default(DB::raw('(
        total_material_ongkos_kerja + 
        (total_material_ongkos_kerja * (lain_lain_percent / 100)) + 
        (total_material_ongkos_kerja * (jasa_kontraktor_percent / 100))
    )'));
    $table->timestamps();
});

// 模型关联
public function rabPivotItem() {
    return $this->belongsTo(RabPivotItem::class, 'rab_item_id');
}

5. Pekerjaan Vendor Rab（中间表） #

Schema::create('pekerjaan_vendor_rab', function (Blueprint $table) {
    $table->id();
    $table->foreignId('detail_pekerjaan_id')->constrained('detail_pekerjaan')->onDelete('cascade');
    $table->foreignId('vendor_id')->constrained('vendor')->onDelete('cascade');
    $table->foreignId('rab_item_id')->nullable()->constrained('rab_item')->onDelete('cascade');
});

数据添加流程 #

• 用户输入detail pekerjaan数据
• 数据存入detail_pekerjaan表：

$detailPekerjaan = DetailPekerjaan::create([
    'kode_proyek' => $kode_project,
    'item' => $item,
    'ukuran' => $validated['ukuran'][$index],
    'keterangan' => $validated['keterangan'][$index],
    'harga_penawaran' => $validated['harga_penawaran'][$index],
]);

• 按供应商数量在中间表生成关联数据，初始rab_item_id为null：

if ($detailPekerjaan) {
    foreach ($vendorData as $vendor) {
        $detailPekerjaan->vendors()->attach($vendor);
    }

    $successCount++;
}

尝试的代码 #

// 控制器代码
$projectData = Project::where('kode_proyek', $kode_project)->first();
$detailPekerjaan = $projectData->detailPekerjaan;
$vendors = Vendor::whereHas('detailPekerjaan', function ($query) use ($kode_project) {
    $query->where('kode_proyek', $kode_project);
})
->with('detailPekerjaan')
->get();

$groupedData = [];
$vendorProfit = [];

foreach ($vendors as $vendor) {
    $profitPercent = 0;
    $totalProfit = 0;

    foreach ($vendor->detailPekerjaan as $detail) {
        $groupedData[$vendor->id][$detail->id] = $detail->pivot->rab_item_id;

        $rabItemId = $groupedData[$vendor->id][$detail->id];
        $rabItem = RabItem::find($rabItemId); 
        
        if ($rabItem) {
            // 计算百分比和利润
            $percentage = round($detail->harga_penawaran / $rabItem->grand_total, 2); // 保留2位小数
            $profit = round($detail->harga_penawaran - $rabItem->grand_total, 2); // 保留2位小数

            $profitPercent += $percentage;
            $totalProfit += $profit;
        }
    }

    $vendorProfit[$vendor->id] = [
        'percentage' => $profitPercent,
        'total' => $totalProfit,
    ];
}

核心问题 #

计算profitPercent和totalProfit时，未过滤关联的detail_pekerjaan是否属于指定kode_proyek的项目，导致包含了其他项目的数据，计算结果错误。

解决方案 #

修改后的控制器代码 #

$kode_project = // 你的项目编码变量

// 1. 查询指定项目
$projectData = Project::where('kode_proyek', $kode_project)->firstOrFail();

// 2. 查询关联该项目的供应商，同时预加载**仅属于当前项目**的DetailPekerjaan和对应的RabItem
$vendors = Vendor::whereHas('detailPekerjaan', function ($query) use ($kode_project) {
    $query->where('kode_proyek', $kode_project);
})
->with([
    'detailPekerjaan' => function ($query) use ($kode_project) {
        // 过滤预加载的DetailPekerjaan，只保留当前项目的数据
        $query->where('kode_proyek', $kode_project);
    },
    // 通过中间表预加载RabItem，避免N+1查询
    'detailPekerjaan.pivot.rabItem' => function ($query) {
        $query->select('id', 'grand_total'); // 只取需要的字段，优化性能
    }
])
->get();

$vendorProfit = [];

foreach ($vendors as $vendor) {
    $profitPercent = 0;
    $totalProfit = 0;

    foreach ($vendor->detailPekerjaan as $detail) {
        $rabItem = $detail->pivot->rabItem;
        
        if ($rabItem) {
            // 计算单个明细的利润和百分比
            $profit = round($detail->harga_penawaran - $rabItem->grand_total, 2);
            $percentage = round(($profit / $rabItem->grand_total) * 100, 2); // 转为百分比格式（如10%而非0.1）

            $totalProfit += $profit;
            $profitPercent += $percentage;
        }
    }

    $vendorProfit[$vendor->id] = [
        'nama_vendor' => $vendor->nama_vendor, // 可选：添加供应商名称便于展示
        'total_profit' => $totalProfit,
        'profit_percent' => $profitPercent,
    ];
}

关键优化点 #

1. 过滤预加载数据：在with('detailPekerjaan')的闭包中添加where('kode_proyek', $kode_project)，确保预加载的明细仅属于当前项目，避免循环时处理无关数据。
2. 解决N+1查询问题：通过detailPekerjaan.pivot.rabItem预加载关联的RabItem，替代循环中的RabItem::find()，大幅提升查询效率。
3. 修正百分比计算逻辑：原代码中$detail->harga_penawaran / $rabItem->grand_total得到的是倍数，改为($profit / $rabItem->grand_total) * 100得到实际百分比（如利润为100，成本为500，则百分比为20%）。
4. 简化数据结构：移除冗余的groupedData数组，直接从detail->pivot获取rabItem数据。

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