Laravel Eloquent/MySQL如何获取各医院最新监测日期倒推1年的关联记录

核心问题定位 #

• 原代码存在严重N+1查询问题：Resource中每个医院单独查询最新监测记录、近12个月监测数据、评分日志，数据量越大性能越差
• 从上层Province开始查询的结构天然不支持直接对下层monitoring字段做排序和分页，必须调整查询逻辑

第一步：优化Hospital模型关联 #

修改Hospital模型的关联定义，支持预加载，同时修复原代码中直接拼接SQL字符串的注入风险：

class Hospital extends Model
{
    // 新增单个最新监测记录关联，支持预加载
    public function latestMonitoring()
    {
        return $this->hasOne(Monitoring::class)->latestOfMany('monitoring_date');
    }

    public function monitors()
    {
        return $this->hasMany(Monitoring::class, 'hospital_id', 'id');
    }

    // 近12个月监测关联，预加载时传入提前查好的各医院最新监测日期做筛选
    public function last12MonthMonitors($cutoffDate = null)
    {
        if ($cutoffDate) {
            return $this->monitors()->whereRaw('monitoring_date >= ? - INTERVAL 12 month', [$cutoffDate]);
        }
        return $this->monitors();
    }

    public function rateLogs()
    {
        return $this->hasManyThrough(RateLog::class, Monitoring::class, 'hospital_id')
            ->where('rate', '!=', 'NA');
    }

    // 近12个月评分日志关联，修复原方法名拼写错误Rage->Rate
    public function last12MonthRateLogs($cutoffDate = null)
    {
        if ($cutoffDate) {
            return $this->rateLogs()->whereRaw('monitoring.monitoring_date >= ? - INTERVAL 12 month', [$cutoffDate]);
        }
        return $this->rateLogs();
    }
}

第二步：按monitoring排序分页的实现 #

如果需要基于monitoring字段排序分页，直接从monitoring表作为查询入口，反向关联上层数据，性能最优：

// 先查询符合近12个月条件的monitoring，排序、分页直接在这层处理
$monitorings = Monitoring::query()
    // 关联筛选：只保留对应医院最新监测日期倒推12个月内的记录
    ->whereRaw('monitoring_date >= (
        SELECT MAX(monitoring_date) FROM monitorings m WHERE m.hospital_id = monitorings.hospital_id
    ) - INTERVAL 12 month')
    // 按需修改排序字段和顺序
    ->orderBy('monitoring_date', 'desc')
    // 预加载所有上层关联和需要的统计数据
    ->with([
        'hospital.latestMonitoring',
        'hospital.hospitalType.village.district.province',
        'rateLogs'
    ])
    // 原生分页，性能远高于集合分页
    ->paginate(20);

// 如果需要保持原有省份嵌套的返回结构，对分页结果做分组整理即可
$provinces = $monitorings->groupBy(function($item) {
    return $item->hospital->hospitalType->village->district->province->id;
})->values()->map(function($provinceMonitors) {
    return $provinceMonitors[0]->hospital->hospitalType->village->district->province;
});

// 返回的时候把分页元数据和省份数据一起返回即可
return [
    'data' => ProvinceResource::collection($provinces),
    'current_page' => $monitorings->currentPage(),
    'last_page' => $monitorings->lastPage(),
    'total' => $monitorings->total()
];

第三步：优化Resource计算逻辑 #

所有数据已经预加载完成，直接用集合方法计算统计值，不会产生新查询：

class HospitalTypeResource extends JsonResource
{
    public function toArray($request)
    {
        $hospital = $this->hospital;
        $cutoffDate = optional($hospital->latestMonitoring)->monitoring_date;
        $rateLogs = $hospital->rateLogs->when($cutoffDate, function($collection) use ($cutoffDate) {
            return $collection->where('monitoring.monitoring_date', '>=', $cutoffDate->copy()->subYear());
        });
        $monitors = $hospital->monitors->when($cutoffDate, function($collection) use ($cutoffDate) {
            return $collection->where('monitoring_date', '>=', $cutoffDate->copy()->subYear());
        });

        $rateLogCount = $rateLogs->count();
        return [
            "id" => $hospital->id,
            "code" => $this->code,
            "name" => $this->name,
            "overallRating"=> $rateLogCount == 0 ? 0 : $rateLogs->sum('rate') / $rateLogCount,
            "ratingsCount" => $monitors->count()
        ];
    }
}

优化效果 #

• 总查询次数从N+1降到固定6次左右，性能提升明显
• 排序分页直接走MySQL原生查询，支持任意monitoring字段排序，分页参数和Laravel原生分页完全兼容
• 保留原有Resource返回结构，无需修改前端适配逻辑

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