@silencebringer Thank you for the help, it has been solved and updated query is,
return $testsdata = Test::with(['evaluatedcopies'=> function($query) use($from_date,$to_date){
$query->join('users', 'upload_answers.user_id', '=', 'users.user_id')->whereBetween('upload_answers.created_at', [$from_date, $to_date])
->select('upload_answers.*','users.name','users.email');
},'evaluatedpendingcopies'=> function($query) use($from_date,$to_date){
$query->join('users', 'upload_answers.user_id', '=', 'users.user_id')->whereBetween('upload_answers.created_at', [$from_date, $to_date])
->select('upload_answers.*','users.name','users.email');
}, 'newmainsupload'=> function($query) use($from_date,$to_date){
$query->join('users', 'upload_answers.user_id', '=', 'users.user_id')->whereBetween('upload_answers.created_at', [$from_date, $to_date])
->select('upload_answers.*','users.name','users.email');
}
])->where('tests.course',$course_det->course_id)
->whereIn('tests.test_id',$mappedtestids)
->whereIn('tests.test_type',['Integrated Mains','Mains'])
->join('upload_answers', fn ($join) => $join->on('upload_answers.test_id', 'tests.test_id')
->whereBetween('upload_answers.created_at', [$from_date, $to_date])
)
->join('users','upload_answers.user_id','=','users.user_id')
->select(
'tests.*','users.name','users.email',
DB::raw('count(upload_answers.id) as totalcount'),
DB::raw('count(case when upload_answers.status = 0 then 1 end) as newcount'),
DB::raw('count(case when upload_answers.evaluated_pdf IS NOT NULL then 1 end) as evaluatedcount'),
DB::raw('count(case when upload_answers.evaluated_pdf IS NULL then 1 end) as pendingcount'),
)
->groupBy('tests.test_id')
->with('coursedet')
->get()
->map(fn ($test) => [
'test_name' => $test->test_name,
'test_code' => $test->test_code,
'coursename' => optional($test->coursedet)->course_name,
'new_uploads' => $test->new_copies,
'totalcount' => $test->totalcount,
'newcount' => $test->newcount,
'evaluatedcount' => $test->evaluatedcount,
'pendingcount' => $test->pendingcount,
'evaluatedcopies' => $test->evaluatedcopies,
'evaluatedpendingcopies' => $test->evaluatedpendingcopies,
'newmainsupload' => $test->newmainsupload,
]);
Test.php Model
public function newmainsupload()
{
return $this->hasMany('App\UploadAnswer','test_id','test_id')->where('status',0);
}
public function evaluatedcopies()
{
return $this->hasMany('App\UploadAnswer','test_id','test_id')->whereNotNull('evaluated_pdf');
}
public function evaluatedpendingcopies()
{
return $this->hasMany('App\UploadAnswer','test_id','test_id')->whereNull('evaluated_pdf');
}
public function uploadcopies()
{
return $this->hasMany('App\UploadAnswer','test_id','test_id');
}
as of now it greatly improved in execution time. any suggestions from your end? this is fine? Once again thank you for the help as it helped me to optimize the query.
