Laravel HasOne关联预加载全部模型，如何优化仅加载最早未完成预约？

问题场景 #

原本SomeModel与Appointment是HasMany关联，为获取每个SomeModel对应的最早未完成预约，定义了HasOne关联方法。该方法功能正常，但预加载时会通过WHERE IN查询所有符合条件的Appointment记录（本地测试加载100+条），而非每个模型仅加载一条目标数据。

当前关联代码 #

public function nextAppointment(): HasOne
{
    return $this->hasOne(Appointment::class, 'some_model_id')
        ->whereNull('completed_at')
        ->oldest('starts_at');
}

生成的SQL #

select * from "appointments" where "completed_at" is null and "appointments"."some_model_id" in ('00cb2664-2aec-4600-a3ca-873dbb5f81f3', '04b62cc7-9ec7-4613-af13-3bc53f9b3538', '109fce77-0fd4-4478-b30d-0c95468d1037', '11b28a27-020d-46f8-b498-51ec152192a2', '11dee373-ec59-4804-897e-2bc5094a3785', '15614002-2414-488d-b639-6410f1c32004', '19d43627-10c5-4708-861d-9394a6ee9b69', 'fffc6b29-fbac-4b6a-80bf-781a2e720c38') order by "starts_at" asc

控制器代码 #

return SomeModel::with(['nextAppointment'])->paginate(request()->input('size', 10));

优化方案 #

核心思路是在数据库层面完成筛选，而非查询所有记录后在内存中匹配，从而减少内存占用并提升查询效率。

方法一：子查询匹配最早预约时间 #

通过子查询找到每个some_model_id对应的最早未完成预约时间，再关联筛选出对应记录：

public function nextAppointment(): HasOne
{
    $subquery = Appointment::selectRaw('MIN(starts_at)')
        ->whereColumn('some_model_id', 'appointments.some_model_id')
        ->whereNull('completed_at');

    return $this->hasOne(Appointment::class, 'some_model_id')
        ->whereNull('completed_at')
        ->whereColumn('starts_at', $subquery);
}

也可使用原生SQL写法：

public function nextAppointment(): HasOne
{
    return $this->hasOne(Appointment::class, 'some_model_id')
        ->whereNull('completed_at')
        ->whereRaw('starts_at = (SELECT MIN(starts_at) FROM appointments WHERE some_model_id = appointments.some_model_id AND completed_at IS NULL)');
}

方法二：窗口函数筛选分组第一条 #

利用MySQL 8.0+支持的窗口函数ROW_NUMBER()，按some_model_id分组并按starts_at排序，取每组的第一条记录：

public function nextAppointment(): HasOne
{
    return $this->hasOne(Appointment::class, 'some_model_id')
        ->selectRaw('appointments.*, ROW_NUMBER() OVER (PARTITION BY some_model_id ORDER BY starts_at ASC) as rn')
        ->whereNull('completed_at')
        ->having('rn', 1);
}

原理说明 #

原方案中，Laravel预加载HasOne关联时，会先查询所有主模型ID，再通过WHERE IN拉取所有符合条件的关联记录，最后在内存中为每个主模型匹配第一条。优化方案则直接在数据库层面完成分组筛选，返回结果仅包含每个主模型对应的目标预约记录，大幅减少内存占用。

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