Laravel关联查询中如何按中间表pivot的fee字段对主查询结果排序

问题原因 #

whereHas 仅用于筛选存在对应关联的主表记录，子查询内的排序逻辑不会作用于主查询结果集，也不会将中间表字段带入主查询的排序上下文，因此原有写法无法实现需求。

解决方案 #

方案1：使用join关联中间表实现主查询排序（兼容性最好） #

这是最通用的实现方式，直接将中间表关联到主查询中，即可直接使用中间表字段排序：

Doctor::with('doctor_hospital_settings')
->when(request('sort-by') == 'highest-fee', function ($query) {
    $query->join('doctor_hospitals', 'doctors.id', '=', 'doctor_hospitals.doctor_id')
          ->select('doctors.*')
          // 按中间表fee降序
          ->orderBy('doctor_hospitals.fee', 'DESC');
    
    // --- 如果1个医生关联多家医院，存在多个fee值，要按最高fee排序用下面的代码替换上面三行 ---
    // $query->join('doctor_hospitals', 'doctors.id', '=', 'doctor_hospitals.doctor_id')
    //       ->selectRaw('doctors.*, MAX(doctor_hospitals.fee) as max_fee')
    //       ->groupBy('doctors.id')
    //       ->orderBy('max_fee', 'DESC');
})
->orderBy('id', 'ASC')
->get();

方案2：关联内记录排序（补充场景） #

如果你不需要主查询的Doctor按fee排序，只是需要每个Doctor下关联的doctor_hospital_settings按fee降序排列，可以用with的回调实现：

Doctor::with(['doctor_hospital_settings' => function ($relation) {
    $relation->orderBy('pivot.fee', 'DESC');
}])
->when(request('sort-by') == 'highest-fee', function ($query) {
    // 保留原来的whereHas筛选逻辑，只保留有至少一个关联医院的医生
    $query->whereHas('doctor_hospital_settings');
})
->orderBy('id', 'ASC')
->get();

注意事项 #

• 如果中间表doctor_hospitals有软删除逻辑，需要在join后补充->whereNull('doctor_hospitals.deleted_at')过滤已删除的关联记录
• 多关联场景下必须加groupBy('doctors.id')避免同一个医生被返回多次
• 主查询排序的优先级高于默认的id排序，符合你需要的当sort-by参数匹配时优先按fee排序的需求

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