Laravel HasOne relation is eager loading all models into memory rather than the specified oldest()-CodePudding

I am trying to load the next due appointment from a relation. The relation is HasMany so my function looks like this:

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

This is working fine however, locally, I have debugdhbar installed and the SQL query this is doing is a where in. This results in ALL the appointments being eager loaded into memory and I end up seeing that over 100 appointments are being loaded for a simple check against the latest. The SQL looks like this:

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

How can I optimize this relation so it only loads the latest appointment into memory? The controller looks like this:

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

CodePudding user response：

I think you want to use oldestOfMany() Link to Docs

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