Get 50k rows faster with subqueries - Laravel 5.6

The below query is to get the candidate's details from the table which has 50k rows. Including jobs, regions, and employment types. A candidate has basic details with employment type, jobs, regions are in another table with foreign key relation.

$candidates =  DB::table('candidates')
->join('role_users', function($join) use($datas) {
    $join->on('candidates.user_id', '=', 'role_users.user_id');
    $join->where('role_users.role_id', 6);

    if (isset($datas->login_time) && $datas->login_time != "") {
        $type = $datas->login_time;
        $dateRanges = getDateRanges($datas->login_time);
        if ($type == 1) {
            $join->whereNull('role_users.login_at');
        } else if ($type == 6) {
            $join->whereDate('role_users.login_at', '<', $dateRanges['start']);
        } else if ($type != 1 && $type != 6) {
            $join->whereBetween('role_users.login_at', [$dateRanges['start'], $dateRanges['end']]);
        }
    }
})
->join('candidate_statuses', 'candidates.candidate_status_id', '=', 'candidate_statuses.id')
->join('employment_types', 'candidates.employment_types_id', '=', 'employment_types.id')
->select(
    'candidates.id', 
    'candidates.user_id', 
    'candidates.candidate_code', 
    'candidates.first_name', 
    'candidates.last_name', 
    'candidates.full_name as name', 
    'candidates.profile_img',
    'candidates.employment_types_id', 
    'employment_types.title AS employment_type',
    DB::raw("(SELECT GROUP_CONCAT(candidate_jobs.job_id SEPARATOR ',') FROM candidate_jobs WHERE candidate_jobs.candidate_id = candidates.id) as job_ids"),
    DB::raw("(SELECT GROUP_CONCAT(regions.name SEPARATOR ',') FROM candidate_regions INNER JOIN regions ON regions.id=candidate_regions.region_id WHERE candidate_regions.candidate_id = candidates.id) as regions"),
    'candidates.formatted_mobile_number',
    'candidates.place',
    'candidates.post_code',
    'role_users.email',
    'role_users.login_at',
    'role_users.email',
    'candidates.has_access',
    'candidates.is_deleted')
->where(function ($query) use($datas, $request, $regCandidates, $jobCandidates, $status){
    
    if ($datas->employment_types) {
        $query->whereIn('candidates.employment_types_id', $datas->employment_types);
    }
    if ($status) {
        $query->whereIn('candidates.candidate_status_id', $status);
    }
    if ($datas->access === 1 || $datas->access=== 0) {
        $access = strval($datas->access);
        $query->where('candidates.has_access', $access);
    }
    if ($datas->candidates) {
        $query->whereIn('candidates.id', $datas->candidates);
    }
    if ($regCandidates) {
        $query->whereIn('candidates.id', $regCandidates);
    }
    if ($jobCandidates) {
        $query->whereIn('candidates.id', $jobCandidates);
    }
    if ($request->search) {
        $query->where('candidates.full_name', 'like', "%{$request->search}%");
        $query->orWhere('candidates.place', 'like', "%{$request->search}%");
        $query->orWhere('candidates.post_code', 'like', "%{$request->search}%");
        $query->orWhere('candidates.candidate_code', 'like', "%{$request->search}%");
        $query->orWhere('candidates.formatted_mobile_number', 'like', "%{$request->search}%");
    }
})
->where('candidates.candidate_status_id', '!=' , 6)
->where('candidates.is_deleted', $request->is_deleted)
->orderBy('candidates.first_name')
->groupBy('candidates.id')
->paginate($request->total);

if (count($candidates) > 0) {
    $candidates->map(function ($candidate) { 
        $job_ids = $candidate->job_ids != null 
                            ? explode(',', $candidate->job_ids)
                            : []; 
        $candidate->jobs = [];
        $candidate->has_access = $candidate->has_access == 1 ? true : false;
        $candidate->login_at = $candidate->login_at ? date('d-m-Y h:i A', strtotime($candidate->login_at)) : 'Never logged in';
        if($job_ids){
            $jobs = DB::table('jobs')->whereIn('id', $job_ids);
            $candidate->jobs = $jobs->get(['name', 'color']);
            $candidate->csvjobs = $jobs->pluck('name')->all();
        }
        $candidate->regions = $candidate->regions != null 
                            ? explode(',', $candidate->regions)
                            : []; 
        return $candidate;
    });
}

It takes 2/ 3 seconds to get the result in my local machine but in production takes too long time. Can anyone please help?

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/3jq7VGy
via IFTTT