convert one to many relation raw query to query builder

i have tables like below, tests

test_id, test_name,test_code, schedule_date, test_type, test_active

user_tests

user_test_id, test_id, mark_scored, user_id,user_test_status

i am fetching all tests and also i need count of below statement

get count of user_tests if user attended the tests for currently logged in user

so i have raw query like below,

$sql = "SELECT t.test_code,t.test_id,t.test_name, (SELECT COUNT(*) FROM user_tests WHERE test_id = t.test_id  ) AS testCount FROM tests t WHERE t.test_type IN (".$testypestr.") AND t.test_id IN (".$testidstr.")";

return $alltests = DB::select($sql);

i want to add pagination so i am trying to convert this to query builder. can i use selectRaw and put count(*) part query or can i use join . any suggestions?

Sergiu17

4 years ago

Level 60

https://laravel.com/docs/8.x/eloquent-relationships#relationship-counting-and-custom-select-statements

Deekshith

4 years ago

Level 5

@Sergiu17 Thank you for the reply. i have eloquent query like below which is working fine,

 $alltests = Test::withCount(['completedusertestsretakes' => function($q) {
            $q->where('user_id',uid());
        }])->whereIn('test_id',$mappedtestidspluck)->where('test_active',1)
                                 ->whereIn('test_type',['Online Prelims','Integrated Prelims'])
                                 ->orderBy('test_schedule_date','ASC')
                                 ->groupBy('test_id')
                                 ->paginate(20);

Test.php

public function completedusertestsretakes()
    {
        return $this->hasMany('App\UserTest','test_id','test_id')->where('attempt','>',1)->where('user_test_status',4);
    }

i want to get this in query builder too.

Please or to participate in this conversation.