In my company we have a three user roles: admin, physician and client. All of them can view one of the records table where we have about 1 million rows and we are in need of caching the results from database.
I've read 10's of posts on Stack and else but I am still trying to figure out the proper way of how to caching.
What I've read is that the proper way is to cache per page, so I cache page 1, page 2 etc based on user page selection. This all works fine.
BUT each user role sees different datasets with different filters selected by them and this is where the problem starts. I cache the results and then filtering the paginated 10 rows seems kind of redundant.
I don't know if I should cache results for each user role with the selected parameters?
Or should I cache all the results first, then load the needed relationships and filter the collection with the parameters from user and then create pagination?
Or shouldn't I be using cache at all in this example and just use simple pagination?
// Set the cache time
$time_in_minutes = 5 * 60;
// Request page and if not set then default page is 1
$page = $paginationObject['page'];
// Set items per page
$per_page = $paginationObject['perpage'] ? $paginationObject['perpage'] : 10;
// Set the cache key based on country
$cache_key = "l04ax_pct_dispensing_forms_{$request->get('country')}_page_{$page}_per_page_$per_page";
// Cache::forget($cache_key);
// Set base query for results
$baseQuery = $this->model->with(['details', 'patient']);
// Assign appropriate relations based on user role
if (Auth::user()->isPhysician()) {
$baseQuery->physicianData();
}
else if (Auth::user()->isManufacturer()) {
$baseQuery->manufacturerData();
}
else if (Auth::user()->isSuperAdmin() || Auth::user()->isAdmin()) {
$baseQuery->adminData();
}
//--------------------------------------
// Add filtering params from request
// -------------------------------------
$baseQuery->when($request->has('atc_code'), function ($query) use ($request) {
if ($request->get('atc_code') === NULL) {
throw new RequestParameterEmpty('atc_code');
}
$query->whereHas('details', function ($subQuery) use ($request) {
$subQuery->where('atc_code', $request['atc_code']);
});
})
->when($request->has('id'), function ($query) use ($request) {
if ($request->get('id') === NULL) {
throw new RequestParameterEmpty('id');
}
$query->where('l04ax_dispensing_forms.id', $request['id']);
})
->when($request->has('pct_patients_hematology_id'), function ($query) use ($request) {
if ($request->get('patient_id') === NULL) {
throw new RequestParameterEmpty('patient_id');
}
$query->where('patient_id', $request['patient_id']);
})
->when($request->has('physician_id'), function ($query) use ($request) {
if ($request->get('physician_id') === NULL) {
throw new RequestParameterEmpty('physician_id');
}
$query->where('physician_id', $request['physician_id']);
})
->when($request->has('date'), function ($query) use ($request) {
if ($request->get('date') === NULL) {
throw new RequestParameterEmpty('date');
}
$query->whereDate('created_at', Carbon::parse($request->get('date'))->toDateString());
})
->when($request->has('deleted'), function ($query) use ($request) {
if ($request->get('only_deleted') === NULL) {
throw new RequestParameterEmpty('only_deleted');
}
$query->onlyTrashed();
})
->when($request->has('withTrashed'), function ($query) use ($request) {
if ($request->get('withTrashed') === NULL) {
throw new RequestParameterEmpty('withTrashed');
}
$query->withTrashed();
});
// Remember results per page into cache
return Cache::remember($cache_key, $time_in_minutes, function () use ($baseQuery, $per_page, $page) {
return new L0axPctDispensingFormsCollection($baseQuery->paginate($per_page, ['*'], 'page', $page));
});
In this example the results are cached per page, but when different user logs in, then the results are wrong.
What would be the best way to approach this?
