skip to Main Content

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


  1. Chosen as BEST ANSWER

    It seems it's a MySQL rule:

    If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE

    Found here: MySQL WHERE EXISTS not working


  2. I am not sure whether this will help you understand your problem, but here goes.

    Working from the inside to the outside:

    1. Get all students in Milan (students.id 1)
    2. Get all teachers_students records for students.id = 1 (teachers_students.id 1)
    3. Get all teachers records for teachers.id 1 (teachers.id 1)
    4. Get all teachers_students records for teachers.id 1 (teachers_students.id 1 & 2)

    Here’s a db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search