Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Deekshith's avatar

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,

  1. fetch package ids from user orders using pluck
  2. pass package ids to TestPackageMapping to fetch assigned test_id
  3. use belongsTo relation to fetch test detail
  4. then using foreach i will display user purchased tests list with test name.
  5. I want to fetch weather currently logged in user attended that test or not.
  6. 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

0 likes
0 replies

Please or to participate in this conversation.