Suppose I have the following relationship in a legacy system, that could not change it.
What I want is to take provinces with hospitals and take one last year monitoring of each hospital.
Note: One last year means not from current date, it means we should take last monitoring date of each hospital and then calculate from that date for taking 12 month up to that date
Suppose hospital A has been monitored in 2020-11-01 for that last time, it should take all of monitoring between 2020-11-01 up to 2019-11-01. And same for other hospitals.
Already implemented with Laravel resource, but it's a bit slow, and also don't know how we can implement the orderBy feature after resource returned the data, because I need pagination and sorting based on monitoring which now I process it via resource.
Currently I'm using hasManyThrough relationship and after get I'm then passing data to resource collection and processing the get of monitoring. But with this can't implement sort and pagination.
$data = Province::with(['districts.hospitalTypes])->get();
Then I pass that data to resource and get monitoring, but how I can apply sorting and pagination, because sorting is based on monitoring. And using resource is a bit slower than eager loading. I'm searching for a way to use eager load with those criteria's.
class ProvinceResource extends JsonResource
{
/**
* Transform the resource into an array.
*
* @param \Illuminate\Http\Request $request
* @return array
*/
public function toArray($request)
{
return [
'id' => $this->id,
'name' => $this->name,
'districts' => DistrictResource::collection($this->whenLoaded("districts"))
];
}
}
class DistrictResource extends JsonResource
{
/**
* Transform the resource into an array.
*
* @param \Illuminate\Http\Request $request
* @return array
*/
public function toArray($request)
{
return [
'id' => $this->id,
'name' => $this->name,
'hospitals' => HospitalTypeResource::collection($this->whenLoaded("hospital_types"))
];
}
}
class HospitalTypeResource extends JsonResource
{
/**
* Transform the resource into an array.
*
* @param \Illuminate\Http\Request $request
* @return array
*/
public function toArray($request)
{
$district = $this->village->district;
$oneLastYearRateLog = $this->hospital->last12MonthRageLogs();
$rateLogCount = $oneLastYearRateLog->count();
$oneLastYearMonitoringCount = $this->hospital->last12MonthMonitors()->count();
return [
"id" => $this->hospital->id,
"code" => $this->code,
"name" => $this->name,
"overallRating"=> $rateLogCount == 0 ? 0 : $oneLastYearRateLog->sum('rate') / $rateLogCount,
"ratingsCount" => $oneLastYearMonitoringCount
];
}
}
And bellow is my relationship in hospital model to take monitoring.
class Hospital extends Model
{
/**
* Get hospital monitors
*/
public function monitors()
{
return $this->hasMany(Monitoring::class, 'hospital_id', 'id');
}
public function last12MonthMonitors()
{
$lastMonitoring = $this->lastMonitoring();
if($lastMonitoring) {
return $this->monitors()->whereRaw('monitoring_date >= "'. $lastMonitoring->monitoring_date.'" - INTERVAL 12 month');
}
return $this->monitors();
}
private function lastMonitoring()
{
return $this->monitors()->select('monitoring_date')->latest('monitoring_date')->first();
}
/**
* Get rate logs
*/
public function rateLogs()
{
return $this->hasManyThrough(RateLog::class, Monitoring::class, 'hospital_id')
->where('rate', '!=', 'NA');
}
/**
* Get last 12 rate logs
*/
public function last12MonthRageLogs()
{
$lastMonitoring = $this->lastMonitoring();
if($lastMonitoring) {
return $this->rateLogs()->whereRaw('monitoring.monitoring_date >= "'.$lastMonitoring->monitoring_date.'" - INTERVAL 12 month');
}
return $this->rateLogs();
}
You don't appear to be eager loading the hospital relationship. I would start by adding this in so that you're not having to run a single query for each HospitalType:
$data = Province::with(['districts.hospitalTypes.hospital'])->get();
I believe one of the reasons you query may be running a little bit slower is because of your lastMonitoring check. Firstly, this query will be getting run for each Hospital and secondly, you don't appear to be storing/caching that value so it's actually going to be queries twice.
If you update to Laravel 8 (min v8.4.2), you'll be able to make use of the Has one of many relationship for your lastMonitoring method e.g.:
public function latestMonitoring()
{
return $this->hasOne(Monitoring::class)->latestOfMany('monitoring_date');
}
Then you'd be able to eager-load that relationship as well:
$data = Province::with(['districts.hospitalTypes.hospital.latestMonitoring'])->get();
You would also need to update the some of the other method in your HospitalType model:
public function monitors()
{
return $this->hasMany(Monitoring::class, 'hospital_id', 'id');
}
/**
* Get rate logs
*/
public function rateLogs()
{
return $this->hasManyThrough(RateLog::class, Monitoring::class, 'hospital_id')
->where('rate', '!=', 'NA');
}
public function latestMonitoring()
{
return $this->hasOne(Monitoring::class, 'hospital_id', 'id')->latestOfMany('monitoring_date');
}
public function last12MonthMonitors()
{
return $this->monitors()->when($this->latestMonitoring, function ($query) {
$query->where('monitoring_date', '>=', $this->latestMonitoring->monitoring_date->subYear());
});
}
/**
* Get last 12 rate logs
*/
public function last12MonthRageLogs()
{
return $this->rateLogs()->when($this->latestMonitoring, function ($query) {
$query->where('monitoring.monitoring_date', '>=', $this->latestMonitoring->monitoring_date->subYear());
});
}