how to use a find with an 'or' condition for belongsToMany, belongsTo Table

Question

I dont know how to get data with an 'or' conditon over a belongstomany table. I have a lessons table, tutors table and a students table . I pass a variable for the firstname. For example if I pass the name 'fred' then I want all of the students and tutors names with the firstname 'fred'. I tried a matching clause but that reduces the data from the primary model so that cant be correct. I use a orwhere but that didnt work as I believe I cant use this for the belongstomany relationship. How do I do this please

query got a This Column not found: 1054 Unknown column 'Students.first_name' in 'where clause'

 ///lesson model
     $this->belongsTo('Tutors', [
            'foreignKey' => 'tutor_id',
            'joinType' => 'LEFT'
        ]);
 $this->belongsToMany('Students', [
            'foreignKey' => 'lesson_id',
            'targetForeignKey' => 'student_id',
            'joinTable' => 'lessons_students'
        ]);


$query3 = $this->find()
              ->contain(['Tutors','Subjects', 'TutoringTypes','Terms','Students'])
              ->select(['Lessons.id','Lessons.lesson_date','Tutors.id','Tutors.first_name','Tutors.last_name',
                 // 'Students.id','Students.first_name','Students.last_name',
                  'Subjects.name','TutoringTypes.value'])     
               ->where(['Lessons.lesson_date >' => $a3 ,  'Tutors.first_name like' => '%'.$firstname.'%',
                   'Tutors.last_name like' => '%'.$lastname.'%' ])
               ->orWhere(['Students.first_name like' => '%'.$firstname.'%'  ])   
              ->order(['Lessons.lesson_date' => 'ASC'])   
              ->hydrate(true);

    /*
            $query3->matching('Students', function ($q) use ($firstname,$lastname) {

                 return $q
                   // ->select(['Students.id','Students.first_name','Students.last_name']) 
                  ->where(['Students.first_name like' =>'%'.$firstname.'%','Students.last_name like' =>'%'.$lastname.'%', ]);
                }); 
          */
                return $query3;


    SELECT 
      Lessons.id AS `Lessons__id`, 
      Lessons.lesson_date AS `Lessons__lesson_date`, 
      Tutors.id AS `Tutors__id`, 
      Tutors.first_name AS `Tutors__first_name`, 
      Tutors.last_name AS `Tutors__last_name`, 
      Subjects.name AS `Subjects__name`, 
      TutoringTypes.value AS `TutoringTypes__value` 
    FROM 
      lessons Lessons 
      LEFT JOIN tutors Tutors ON Tutors.id = (Lessons.tutor_id) 
      LEFT JOIN subjects Subjects ON Subjects.id = (Lessons.subject_id) 
      LEFT JOIN tutoring_types TutoringTypes ON TutoringTypes.id = (Lessons.tutoring_type_id) 
      LEFT JOIN terms Terms ON Terms.id = (Lessons.term_id) 
    WHERE 
      (
        Students.first_name like '%fred%' 
        OR (
          Lessons.lesson_date > '2016-01-28' 
          AND Tutors.first_name like '%fred%' 
          AND Tutors.last_name like '%%'
        )
      ) 
    ORDER BY 
      Lessons.lesson_date ASC 
    LIMIT 
      20 OFFSET 0

    http://book.cakephp.org/3.0/en/orm/query-builder.html


   //generated query from the below answer 
    SELECT 
      Lessons.id AS `Lessons__id`, 
      Lessons.lesson_date AS `Lessons__lesson_date`, 
      Tutors.id AS `Tutors__id`, 
      Tutors.first_name AS `Tutors__first_name`, 
      Tutors.last_name AS `Tutors__last_name`, 
      Subjects.name AS `Subjects__name`, 
      TutoringTypes.value AS `TutoringTypes__value` 
    FROM 
      lessons Lessons 
      LEFT JOIN tutors Tutors ON Tutors.id = (Lessons.tutor_id) 
      LEFT JOIN subjects Subjects ON Subjects.id = (Lessons.subject_id) 
      LEFT JOIN tutoring_types TutoringTypes ON TutoringTypes.id = (Lessons.tutoring_type_id) 
      LEFT JOIN terms Terms ON Terms.id = (Lessons.term_id) 
    WHERE 
      (
        Lessons.lesson_date > '2016-01-28' 
        AND (
          Students.first_name like '%%' 
          OR Tutors.first_name like '%%' 
          OR Tutors.last_name like '%%'
        )
      ) 
    ORDER BY 
      Lessons.lesson_date ASC 
    LIMIT 
      20 OFFSET 0

Answer 1

I found the answer by removing the belongstomany relationship for students and changed it to a hasmany. It was quite a change but this relationship of belongstomany is providing to be a bit of a headache at times.

 $query3 = $this->find()
              ->contain(['Tutors','Subjects', 'TutoringTypes','Terms','Students'])
              ->select(['Lessons.id','Lessons.lesson_date','Tutors.id','Tutors.first_name','Tutors.last_name',
                     'Students.id','Students.first_name','Students.last_name',
                  'Subjects.name','TutoringTypes.value'])     
               ->where(['Lessons.lesson_date >' => $a3 , 
                    'OR' => [[ 'Tutors.first_name like' => '%'.$firstname.'%',  'Tutors.last_name like' => '%'.$lastname.'%'],
                             ['Students.first_name like' =>  '%'.$firstname.'%', 'Students.last_name like' => '%'.$lastname.'%']]
                 ])
              ->order(['Lessons.lesson_date' => 'ASC'])   
              ->hydrate(true);