Query optimization for applying multiple filters

@Tray2

$schools = School::where('is_active',1)
            ->whereBetween('tuition',[$min_tuition_fee,$max_tuition_fee])
            ->whereBetween('application_fee',[$min_application_fee,$max_application_fee])
         ->when($nationality_id,$education_country_id,$education_level_id,$grading_scheme_id,$exam_type_id,$grading_scale,$grading_avg,function ($q1)
            use($nationality_id,$education_country_id,$education_level_id,$grading_scheme_id,$exam_type_id,$grading_scale,$grading_avg){
                $q1->whereHas('eligibility',function ($q2) use($nationality_id,$education_country_id,$education_level_id,$grading_scheme_id,$exam_type_id,$grading_scale,$grading_avg){
                    $q2->when($nationality_id,function ($q3) use($nationality_id){
                        $q3->where('nationality_id',$nationality_id);
                    })->when($education_country_id,function ($q3) use($education_country_id){
                        $q3->where('education_country_id',$education_country_id);
                    })->when($education_level_id,function ($q3) use($education_level_id){
                        $q3->where('education_level_id',$education_level_id);
                    })->when($grading_scheme_id,function ($q3) use($grading_scheme_id){
                        $q3->where('grading_scheme_id',$grading_scheme_id);
                    })->when($exam_type_id,function ($q3) use($exam_type_id){
                        $q3->where('exam_type_id',$exam_type_id);
                    })->when($grading_scale,function ($q3) use($grading_scale){
                        $q3->where('grading_scale',$grading_scale);
                    })->when($grading_avg,function ($q3) use($grading_avg){
                        $q3->where('grading_avg',$grading_avg);
                    });
                });
            })
            ->when($country_ids,function ($q1) use($country_ids){
                $q1->whereHas('country',function ($q2) use ($country_ids){
                    $q2->whereIn('id',$country_ids);
                });
            })
            ->when($state_ids,function ($q1) use($state_ids){
                $q1->whereHas('state',function ($q2) use ($state_ids){
                    $q2->whereIn('id',$state_ids);
                });
            })
            ->when($city_ids,function ($q1) use($city_ids){
                $q1->whereHas('city',function ($q2) use ($city_ids){
                    $q2->whereIn('id',$city_ids);
                });
            })
            ->when($school_type_ids,function ($q1) use($school_type_ids,$school_ids){
                $q1->whereHas('type',function ($q2) use($school_type_ids){
                    $q2->whereIn('id',$school_type_ids);
                });
            })
            ->when($school_ids,function ($q1) use($school_ids){
                $q1->whereIn('id',$school_ids);
            })
            ->when($discipline_ids,function ($q1) use($discipline_ids){
                $q1->whereHas('disciplines',function ($q2) use($discipline_ids){
                    $q2->whereIn('id',$discipline_ids);
                });
            })
            ->latest()
            ->orderBy('title')
            ->paginate(10);

@echky47 you are passing multiple items to every when()? What's the point of that exactly?

->when($nationality_id, $education_country_id, $education_level_id,$grading_scheme_id, $exam_type_id,$grading_scale,$grading_avg, function ($q1)
             

@echky47 Man that is a huge query, I can't tell you how or if it affects your performance.

What you should do is a ->toSql() on it to get the pure SQL and then run an explain on it, and see what you might need an index on.

@Sinnbeck these are multiple items after passing them inside i need to filter on base of relationships....

@echky47 you should only give it 1 parameter to check. When x is true, run the callback

@echky47 In your case I wouldn't worry too much about the performance just yet, I'd worry more about readability and maintainability. Take care of that first then worry about the performance.