如何获取一年中12个月的就诊次数(it服务中有体检服务的就诊次数，总就诊次数)in Laravel

Question

我曾尝试使用下面的方法获得它，但它需要很长时间。有解决办法吗?

$clinic=$user->Clinics->first();
$userPerMonth= array();
for ($i=1; $i<=12; $i++){
$checkups = Visit::where('clinic_id', $clinic->id)
->whereMonth('created_at', date('m',strtotime('-'.$i.' month')))
->whereHas('VisitCheckUps')
->count();

$userPerMonth[$i]=['checkups'=>$checkups];
}
dump($userPerMonth);

Answer 1

您可以尝试以下操作。我在评论中包含了建议

// pull only required data from database, this will optimize your query and reduce latency between the database and your app
$clinic =   $user->Clinics
->select('id')
->first();
// reduce round trip time between the database and your app by consolidating your query into one that will pull your desired details
$usersPerMonth =    Visit::where('clinic_id', $clinic->id)
->whereHas('VisitCheckUps')
->groupBy(DB::raw("MONTH(created_at)"))
->select(
DB::raw("MONTH(created_at) as month_num"),
DB::raw("COUNT(created_at) as checkups_per_month")
)
->get()
//transforming your results in your desired data structure
->keyBy('month_num')
->map(function($record){
return [
'checkups'=>$record->checkups_per_month
];
})
->toArray();

让我知道这是否适合你。