I’m having a problem with multi-level whereHas
because the restriction / condition of city
is not being respected & the query returns all records.
I know I’m performing an unnecessary nested whereHas
, I know I could simplify the query, yet, I’m trying to understand why doesn’t it work.
My eloquent query (doesn’t work – returns all the results, ignores the city
condition) – See SQLFiddle:
$teachersStudents = TeachersStudents::whereHas('teacher', function($query)
{
$query->where('subscription_id', 1)
->where('school_id', 11)
->whereHas('students', function($subQuery)
{
$subQuery->whereHas('student', function($subSubQuery)
{
$subSubQuery->whereIn('city', ['Milan'])
->whereNotNull('city');
});
});
})
->get();
Which translates to:
select
`teachers_students`.*
from
`teachers_students`
where
exists (
select
*
from
`teachers`
where
`teachers_students`.`teacher_id` = `teachers`.`id`
and `subscription_id` = 1
and `school_id` = 11
and exists (
select
*
from
`teachers_students`
where
`teachers`.`id` = `teachers_students`.`teacher_id`
and exists (
select
*
from
`students`
where
`teachers_students`.`student_id` = `students`.`id`
and `city` in ('Milan')
and `city` is not null
)
)
)
The simpler version (works – restricts the city
) – See SQLFiddle:
$teachersStudents = TeachersStudents::whereHas('teacher', function($query)
{
$query->where('subscription_id', 1)
->where('school_id', 11);
})
->whereHas('student', function($query)
{
$query->whereIn('city', ['Milan'])
->whereNotNull('city');
})
->get();
Which translates to:
select
`teachers_students`.*
from
`teachers_students`
where
exists (
select
*
from
`teachers`
where
`teachers_students`.`teacher_id` = `teachers`.`id`
and `subscription_id` = 1
and `school_id` = 11
)
and exists
(
select
*
from
`students`
where
`teachers_students`.`student_id` = `students`.`id`
and `city` in ('Milan')
and `city` is not null
)
Why does the first query does not respect the city
field?
2
Answers
It seems it's a MySQL rule:
Found here: MySQL WHERE EXISTS not working
I am not sure whether this will help you understand your problem, but here goes.
Working from the inside to the outside:
students
in Milan (students.id 1)teachers_students
records for students.id = 1 (teachers_students.id 1)teachers
records for teachers.id 1 (teachers.id 1)teachers_students
records for teachers.id 1 (teachers_students.id 1 & 2)Here’s a db<>fiddle