Laravel多对多关联：如何获取中间表（pivot）的最新记录

问题描述 #

需要从customertypes表获取列作为表头，仅展示customerprices表中每个pricelevels_id+customertypes_id组合的最新金额数据，如何在Laravel中实现获取该中间表的最新记录？

数据表结构 #

customertypes表

pricelevels表

customerprices表

预期结果 #

当前代码 #

$price_levels = PriceLevel::with('customerprice')->get();

解决方案 #

1. 调整模型关联，筛选最新记录 #

在PriceLevel模型中定义关联时，通过子查询筛选出每个pricelevels_id+customertypes_id组合的最新记录：

// app/Models/PriceLevel.php
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

public function latestCustomerPrices()
{
    // 先子查询获取每组的最新创建时间
    $latestSubquery = CustomerPrice::select(
        'pricelevels_id',
        'customertypes_id',
        DB::raw('MAX(created_at) as latest_created')
    )->groupBy('pricelevels_id', 'customertypes_id');

    return $this->hasMany(CustomerPrice::class)
        ->joinSub($latestSubquery, 'latest_prices', function ($join) {
            $join->on('customerprices.pricelevels_id', '=', 'latest_prices.pricelevels_id')
                 ->on('customerprices.customertypes_id', '=', 'latest_prices.customertypes_id')
                 ->on('customerprices.created_at', '=', 'latest_prices.latest_created');
        });
}

2. 拉取数据并转换为预期格式 #

获取数据后，将关联数据映射成目标表格结构：

use App\Models\PriceLevel;
use App\Models\CustomerType;

// 获取所有客户类型，用于生成表头
$customerTypes = CustomerType::pluck('name', 'id')->toArray();

// 获取价格等级及其最新关联价格
$priceLevels = PriceLevel::with('latestCustomerPrices')->get();

// 转换为预期的表格格式
$formattedResult = $priceLevels->map(function ($level) use ($customerTypes) {
    $row = ['等级名称' => $level->name];
    
    foreach ($customerTypes as $typeId => $typeName) {
        // 匹配当前客户类型的最新价格
        $matchedPrice = $level->latestCustomerPrices->firstWhere('customertypes_id', $typeId);
        $row[$typeName] = $matchedPrice ? $matchedPrice->amount : null;
    }
    
    return $row;
});

// 查看结果
dd($formattedResult);

3. 高性能替代：原生SQL查询 #

如果数据量较大，直接使用原生SQL可以提升性能：

use Illuminate\Support\Facades\DB;
use App\Models\CustomerType;

$customerTypes = CustomerType::pluck('name', 'id')->toArray();
// 生成动态列，对应每个客户类型的金额
$dynamicColumns = collect($customerTypes)->map(function ($name, $id) {
    return "MAX(CASE WHEN cp.customertypes_id = {$id} THEN cp.amount END) as `{$name}`";
})->implode(',');

$query = "
    SELECT pl.name as `等级名称`, {$dynamicColumns}
    FROM pricelevels pl
    LEFT JOIN (
        SELECT pricelevels_id, customertypes_id, amount
        FROM customerprices cp
        WHERE (pricelevels_id, customertypes_id, created_at) IN (
            SELECT pricelevels_id, customertypes_id, MAX(created_at)
            FROM customerprices
            GROUP BY pricelevels_id, customertypes_id
        )
    ) cp ON pl.id = cp.pricelevels_id
    GROUP BY pl.id, pl.name
";

$result = DB::select($query);

内容的提问来源于stack exchange，提问作者Ye Htun Z