如何在foreach循环内按start_time对分组预约数据排序？

问题 #

需要对分组后的预约数据，按每个分组对应的doctorScheduleDetail.start_time进行外层排序。当前已通过doctor_schedule_detail_id完成分组，但外层分组顺序未按start_time排列，此前尝试的map+sortBy仅对分组内的预约项排序，无法满足外层分组排序的需求。

现有代码 #

Controller代码

private function getPatientList($search = null)
{
    return Appointment::select('id', 'hospital_id', 'patient_id', 'serial_no', 'doctor_schedule_detail_id')
        ->whereHas('patient', function ($query) use ($search) {
            $query->select('id', 'name', 'mobile_no', 'gender');
            if ($search) {
                $query->where(function ($q) use ($search) {
                    $q->where('name_hash', hash('sha256', $search))
                        ->orWhere('mobile_no_hash', hash('sha256', $search));
                });
            }
        })->where('doctor_id', auth()->user()->doctor->id)
        ->where('hospital_id', auth()->user()->doctor->current_hospital_id)  
        ->where('status', '!=', 'Cancelled')
        ->whereDate('reporting_time', date('Y-m-d'))
        ->orderBy('reporting_time')->get()
        ->groupBy('doctor_schedule_detail_id')->all();
}

Model代码

public function doctorScheduleDetail()
{
    return $this->belongsTo(DoctorScheduleDetail::class);
}

视图代码

@foreach ($appointments as $doctorScheduleId => $appointmentsGroup)
    
    <div class="row row-cards patient-list mb-3">
        <h4 class="mb-0"><span class="">Slot {{ $loop->iteration }}</span> [{{ date("g:i A",
            strtotime($appointmentsGroup[0]->doctorScheduleDetail->start_time)) }} - {{ 
            date("g:i A", strtotime($appointmentsGroup[0]->doctorScheduleDetail->end_time)) }}]</h4>
        @foreach ($appointmentsGroup as $appointment)
            <div class="col-sm-6 col-lg-3">{{ $appointment?->patient?->name }}</div>
            --------
        @endforeach
    </div>
@endforeach

解决方案 #

方法一：数据库层面关联排序（推荐） #

通过关联doctor_schedule_details表，直接在数据库层面按start_time排序后再分组，性能更优，避免内存排序开销。

修改后的Controller代码：

private function getPatientList($search = null)
{
    return Appointment::select('appointments.id', 'appointments.hospital_id', 'appointments.patient_id', 'appointments.serial_no', 'appointments.doctor_schedule_detail_id')
        ->join('doctor_schedule_details', 'appointments.doctor_schedule_detail_id', '=', 'doctor_schedule_details.id')
        ->whereHas('patient', function ($query) use ($search) {
            $query->select('id', 'name', 'mobile_no', 'gender');
            if ($search) {
                $query->where(function ($q) use ($search) {
                    $q->where('name_hash', hash('sha256', $search))
                        ->orWhere('mobile_no_hash', hash('sha256', $search));
                });
            }
        })
        ->where('appointments.doctor_id', auth()->user()->doctor->id)
        ->where('appointments.hospital_id', auth()->user()->doctor->current_hospital_id)  
        ->where('appointments.status', '!=', 'Cancelled')
        ->whereDate('appointments.reporting_time', date('Y-m-d'))
        ->orderBy('doctor_schedule_details.start_time') // 按关联表的start_time排序分组顺序
        ->orderBy('appointments.reporting_time') // 保留原预约项的排序逻辑
        ->get()
        ->groupBy('doctor_schedule_detail_id')
        ->all();
}

方法二：集合层面排序（适合无法修改查询关联的场景） #

如果不需要修改数据库查询逻辑，可以在分组后，直接对分组集合按每个分组的start_time排序，同时预加载关联避免N+1查询。

修改后的Controller代码：

private function getPatientList($search = null)
{
    $appointments = Appointment::select('id', 'hospital_id', 'patient_id', 'serial_no', 'doctor_schedule_detail_id')
        ->with('doctorScheduleDetail') // 预加载关联，避免重复查询数据库
        ->whereHas('patient', function ($query) use ($search) {
            $query->select('id', 'name', 'mobile_no', 'gender');
            if ($search) {
                $query->where(function ($q) use ($search) {
                    $q->where('name_hash', hash('sha256', $search))
                        ->orWhere('mobile_no_hash', hash('sha256', $search));
                });
            }
        })
        ->where('doctor_id', auth()->user()->doctor->id)
        ->where('hospital_id', auth()->user()->doctor->current_hospital_id)  
        ->where('status', '!=', 'Cancelled')
        ->whereDate('reporting_time', date('Y-m-d'))
        ->orderBy('reporting_time')
        ->get()
        ->groupBy('doctor_schedule_detail_id');

    // 对分组后的集合按start_time排序
    $sortedAppointments = $appointments->sortBy(function ($group) {
        return strtotime($group->first()->doctorScheduleDetail->start_time);
    });

    return $sortedAppointments->all();
}

关键说明 #

• 此前的错误是用map对分组内的单个预约项排序，而需求是对整个分组的顺序排序，因此需要将sortBy直接作用在分组后的集合上。
• 方法一通过数据库关联排序，适合数据量较大的场景；方法二在内存中排序，适合小数据量场景，需注意预加载关联以优化性能。

内容的提问来源于stack exchange，提问作者Md. Shamvil Hossain