Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hi, so we have a page where we have to filter a list of students based on filters selected by the user in the front end.
Filters in the front end
Now Location & Gender are part of student table, so it is very easy to use these filters with simple query but subjects is a totally different table
Check the attached table
Query current we have
$student = Student::select('*')
->where(function ($query) use ($request) {
if (!empty($request->location)) {
$query->whereIn('location', $request->location);
} else if (!empty($request->gender)) {
$query->where('gender', $request->gender);
} else if (!empty($request->subjects)) {
// selecting students by id who enrolled for a particular subject
// end user can select multiple subjects
$query->whereIn('id', function($subjectQuery) use ($request) {
$subjectQuery->select('student_id')
->whereIn('subject_id', [$request->subjects])
->from('student_subjects')
->get();
});
})->get();
when passing {"subject": [201, 205]}
getting following error
Nested arrays may not be passed to whereIn method
But when passing {"subject": [201]}
This return empty result,
What are we doing wrong? or what we can do to improve this query?
@hellopeter13 I don't think you can use closure in whereIn(). I think it's a problem.
https://github.com/laravel/framework/blob/9.x/src/Illuminate/Database/Query/Builder.php#L1001
Instead you can use this:
$query->whereIn('id', DB::table('student_subjects')->whereIn('subject_id', [$request->subjects])->pluck('student_id'));
@tisuchi what is the alternative then?
@hellopeter13 I updated my answer. Please check it 👆
The whereIn method cannot accept a nested array as a parameter. you're using a closure in the whereIn method to build another query to select student IDs based on the subject IDs. But, the get method returns a Collection, not a scalar value, so it can't be used as the parameter of whereIn. What about this:
$student = Student::select('*')->where(function ($query) use ($request) {
if (!empty($request->location)) {
$query->whereIn('location', $request->location);
} else if (!empty($request->gender)) {
$query->where('gender', $request->gender);
} else if (!empty($request->subjects)) {
$query->whereIn('id', function($subjectQuery) use ($request) {
$subjectQuery->select('student_id')
->whereIn('subject_id', $request->subjects)
->from('student_subjects');
})->get();
})->get();
@azimidev for some weird reasons, this is giving 0 results but when I run this query outside else if, it works fine
Assuming $request->subjects is an array, maybe something like this:
$student = Student::select('*')->where(function ($query) use ($request) {
if (!empty($request->location)) {
$query->whereIn('location', $request->location);
} else if (!empty($request->gender)) {
$query->where('gender', $request->gender);
} else if (!empty($request->subjects)) {
$placeholders = implode(',', array_fill(0, count($request->subjects), '?'));
$query->whereRaw(
"id in (select student_id from student_subjects where subject_id in ($placeholders)",
$request->subjets);
})->get();
You could use implode(',' $request->subjects) instead of placeholders but that's a bad idea since that's user input and you would be open to sql injection when using the whereRaw() method
To prevent that, you have to use bindings and in order to work , you need as many '?' as elements the $request->subject array has
For example, if the $request->subject is [201,205] then the string for the whereRaw() clause has to be whereRaw( "id in (select student_id from student_subjects where subject_id in (?,?)",[201,205])
I hope that makes sense
BTW, are these filters mutually exclusive ? Because the way the where clause in the Student model is written that's what is happening.
Please or to participate in this conversation.