Laravel Eloquent: When search criteria are missing-CodePudding

It's about Laravel Eloquent. I have a search filter programmed with four search criteria. Now it can happen that one or more search criteria are not specified.

Currently the query only works if all search criteria are present. But how I can do that when one or more search criteria are missing I have not found on the Internet search. Can you help me maybe?


$hours = Sap_cm01::join('production_supervisors', 'production_scheduler', '=', 'pr_superv')
->where('year', $selectedYear)
->where('cell', $selectedDepartment)
->where('area', $selectedValueStream)
->where('sap_cm01.ap', $apData)
->sum('val');

dd($hours);

CodePudding user response：

You can use conditional clauses

Instead of ->where(...), you use ->when(condition, closure). You place the where part inside the closure.

I'm not sure how you check which criteria is specified. Here I'm going to use isset() but you could just compare to null as well. ->when($apData == null, ...)

$hours = Sap_cm01::join('production_supervisors', 'production_scheduler', '=', 'pr_superv')
->when(isset($selectedYear),        fn($q) => $q->where('year', $selectedYear))
->when(isset($selectedDepartment),  fn($q) => $q->where('cell', $selectedDepartment))
->when(isset($selectedValueStream), fn($q) => $q->where('area', $selectedValueStream))
->when(isset($apData),              fn($q) => $q->where('sap_cm01.ap', $apData))
->sum('val');

CodePudding user response：

You can check with conditions and build your query. If you get the values from request object you can use $request->has('selectedYear')).

Maybe you have to change the condition. it depends what are you in the vars (null, '', etc.).

$hours = Sap_cm01::join('production_supervisors', 'production_scheduler', '=', 'pr_superv')

if (isset($selectedYear)) {
    $hours->where('year', $selectedYear);
}

if (isset($selectedDepartment)) {
    $hours->where('cell', $selectedDepartment)
}

if ($selectedValueStream) {
    $hours->where('area', $selectedValueStream)
}

if ($apData) {
    $hours->where('sap_cm01.ap', $apData)
}

return $hours->sum('val');