如何用Laravel Eloquent的with方法获取多对多关联指定最新记录？

需求 #

从表a中选择指定记录，以及其关联表a_b中在指定日期前的最新关联表b的记录。

示例表结构 #

表(a) #

表(b) #

表(a_b) #

期望输出（指定日期为2022-09-09前的最新记录） #

已实现的纯SQL #

select a.id,a.name,b.name,a_b.date
from a
LEFT JOIN a_b on a_b.a_id = a.id
INNER JOIN b on b.id = a_b.b_id

where a.id = 1
AND a_b.date = (
select max(a_b.date) from a
LEFT JOIN a_b on a_b.a_id = a.id
INNER JOIN b on b.id = a_b.b_id
WHERE a.id = 1 AND a_b.date <= '2022-09-09')

问题 #

如何使用Laravel Eloquent的with方法实现相同效果？现有代码框架如下：

A::with([
'B'=> function ($query) use ($date) {
/// 此处应编写什么逻辑
}])->get()

解决方案 #

第一步：配置模型关联 #

首先确保Eloquent模型正确配置关联关系，考虑到中间表a_b包含额外的date字段，定义多对多关联并携带中间表字段：

// A 模型（对应表a）
class A extends Model
{
    protected $table = 'a';

    public function B()
    {
        return $this->belongsToMany(B::class, 'a_b', 'a_id', 'b_id')
                    ->withPivot('date'); // 携带中间表的date字段
    }
}

// B 模型（对应表b）
class B extends Model
{
    protected $table = 'b';
}

第二步：编写with闭包逻辑 #

通过子查询获取指定日期内的最新关联记录，填充到闭包中：

$targetDate = '2022-09-09';

$records = A::with(['B' => function ($query) use ($targetDate) {
    // 子查询：获取当前A记录下，a_b表中指定日期前的最大日期
    $maxDateSubquery = DB::table('a_b')
        ->whereColumn('a_id', 'a.id')
        ->where('date', '<=', $targetDate)
        ->selectRaw('MAX(date) as latest_date');

    // 筛选符合条件的B关联记录：日期<=指定日期，且等于该A下的最大日期
    $query->wherePivot('date', '<=', $targetDate)
          ->wherePivot('date', '=', $maxDateSubquery)
          ->withPivot('date'); // 确保返回date字段
}])->find(1); // 对应原SQL中的a.id=1，若要查询所有A记录可改为get()

替代实现方式（使用子查询关联） #

如果需要更贴近原SQL的逻辑，也可以通过子查询关联实现：

$targetDate = '2022-09-09';

$records = A::with(['B' => function ($query) use ($targetDate) {
    // 子查询：分组获取每个a_id和b_id对应的最新日期
    $latestAbSubquery = DB::table('a_b')
        ->where('date', '<=', $targetDate)
        ->groupBy('a_id', 'b_id')
        ->select('a_id', 'b_id', DB::raw('MAX(date) as latest_date'));

    // 关联子查询筛选出符合条件的记录
    $query->joinSub($latestAbSubquery, 'latest_ab', function ($join) {
            $join->on('a_b.a_id', '=', 'latest_ab.a_id')
                 ->on('a_b.b_id', '=', 'latest_ab.b_id')
                 ->on('a_b.date', '=', 'latest_ab.latest_date');
          })
          ->where('a_b.a_id', '=', DB::raw('a.id'))
          ->select('b.*', 'a_b.date');
}])->find(1);

以上两种方式都能实现和原SQL一致的查询效果，返回指定A记录关联的、在指定日期前的最新B记录及其关联日期。

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