Jan 17, 2022
0
Level 5
withCount() on nested relationship with where clause
The model structure is as follows
TestPackageMapping => (belongsTo) Test => (hasMany) UserTest
How can i load number of usertests (user attended tests) from TestPackageMapping Model using withCount() method.
flow is as follows,
- fetch package ids from user orders using pluck
- pass package ids to
TestPackageMappingto fetch assignedtest_id - use belongsTo relation to fetch test detail
- then using foreach i will display user purchased tests list with test name.
- I want to fetch weather currently logged in user attended that test or not.
- use hasMany relationsionship to UserTest model to fetch user attended tests.
so far i have tried like below,
$alltests = PackageTestMaping::whereHas('testdetail')
->with(['testdetail' => function($q) {
$q->where('test_active',1)
->whereIn('test_type',['Online Prelims','Integrated Prelims']);
}])
->with(['testdetail.completedusertestsretakes' => function($q) use($courseid){
$q->where('user_id',uid())
->where('course_id',$courseid);
}])
->join('tests', 'test_package_maping.test_id', '=', 'tests.test_id')->whereIn('test_package_maping.package_id',$packages)->orderBy('tests.test_schedule_date','ASC')->groupBy('test_package_maping.test_id')->paginate(20);
$itemsTransformed = $alltests->map(function($test) use($courseid,$user_id){
if($test->testdetail)
{
$returnarr['test_id'] = $test->testdetail->test_id;
$returnarr['test_code'] = $test->testdetail->test_code;
$returnarr['test_type'] = $test->testdetail->test_type;
$returnarr['csat'] = $test->testdetail->csat;
$returnarr['schedule_type'] = $test->testdetail->schedule_type;
$returnarr['test_name'] = $test->testdetail->test_name;
$returnarr['test_description'] = $test->testdetail->test_description;
$returnarr['test_duration'] = $test->testdetail->test_duration;
$returnarr['course'] = $test->testdetail->course;
$returnarr['test_active'] = $test->testdetail->test_active;
$returnarr['retakes'] = $test->testdetail->retakes;
$returnarr['test_active'] = $test->testdetail->test_active;
$returnarr['test_schedule_date'] = $test->testdetail->test_schedule_date;
$returnarr['total_retakes'] = $test->testdetail->completedusertestsretakes->count();
return $returnarr;
} else {
return false;
}
})->toArray();
$itemsTransformedAndPaginated = new \Illuminate\Pagination\LengthAwarePaginator(
$itemsTransformed,
$alltests->total(),
$alltests->perPage(),
$alltests->currentPage(), [
'path' => \Request::url(),
'query' => [
'page' => $alltests->currentPage()
]
]
);
How to directly add withCount() ?
Any way i can improve this query? Thank you
Please or to participate in this conversation.