[FIXED] Generate an OR SQL statement in CakePHP 3

Issue

I am converting some finds from cakephp2 to cakephp3. I need to search on a first name and surname on a tutors table that are stored on different columns. According to the docs I needed to have a LEFT join bewtween the tables. I have used an or condition with 2 fields but it works like and 'and' condition if both parameters have a value. My issue is

   q1) I cant get the data with just the first name only , and the surname is null.
   q2) I need to pass both first name and surname to get just those data with that name.
       Not sure how to do this in cakephp3. 

eg $a5='fred'; //just want all first names like fred
   $a6=null; //sometimes this will be filled
   $a3='2015-05-30';
   $a4='2016-06-01';

$query3 = $this->Lessons->find()
           ->contain(['Tutors'])
           ->select(['lessons.id','lessons.lesson_date','tutors.id','tutors.first_name','tutors.last_name' ])      
          ->where(['Lessons.lesson_date >' => $a3,'Lessons.lesson_date <' => $a4,
'OR' => [['tutors.first_name like' => '%'.$a5.'%'], ['tutors.last_name like' => '%'.$a6.'%']],

         ]);

      foreach ( $query3 as $row) {

                  debug($row->toArray());


            }

I didnt understand the docs on this point. http://book.cakephp.org/3.0/en/orm/query-builder.html#advanced-conditions

UPDATE- tried this and this also just gives all the data with either 'at' or 'to' but it should be any names with both 'at' and 'to' in them.

 $query3 = $this->Lessons->find()
             ->contain(['Tutors','Subjects', 'TutoringTypes','Terms','Students'])
              ->select(['lessons.id','lessons.lesson_date','tutors.id','tutors.first_name','tutors.last_name',
                  'subjects.id','subjects.name','terms.id','terms.title'])           
    ->where(['Lessons.lesson_date >' => $a3,'Lessons.lesson_date <' => $a4])
    ->orWhere(function ($exp) {
        return $exp->and_([
            'tutors.first_name like' => '%an%',
            'tutors.last_name like' => '%to%',
        ]);
    }); 

Solution

Pay attention to the generated SQL. You either see it in DebugKit or debug it by calling $query->sql(). You're building a wrong query:

You generate OR ((... AND ...)) because you're using an and_. You probably want ... OR ....

->orWhere(function ($exp) {
    return $exp->and_([
        'tutors.first_name like' => '%an%',
        'tutors.last_name like' => '%to%',
    ]);
});

You probably want OR. But passing the array directly to orWhere() would work as well.

Answered By - floriank