[FIXED] get records with 2 not like conditions in cakephp3

Issue

This query doesnt work as expected. What happens it will select records with the word assessment in it. I have tried a few variations but as you can see the query should only get records with the word 'maths' and exclude records with 'scholarship' and 'assessment'.

I cant get this to work in cakephp3. What happens is that i get records with the words assessment or scholarship.

$subjectMaths = $this->Students->Subjects->find('list')
    ->select(['id', 'name'])
    ->where([   
        'Subjects.name not LIKE'  => '%assessment%' ,
        'Subjects.name LIKE'  => '%maths%', 
        'Subjects.name not LIKE'  => '%scholarship%' 
    ])  
    ->order(['Subjects.name' => 'asc']);


$subjectMaths = $this->Students->Subjects->find('list')
    ->select(['id', 'name'])
    ->where([   
        'Subjects.name LIKE'  => '%maths%', 
        'OR'=> [
            ['Subjects.name not LIKE'  => '%assessment%' ],
            [ 'Subjects.name not LIKE'  => '%scholarship%',] 
        ]
    ])  
    ->order(['Subjects.name' => 'asc']);

Solution

the proble here is that you have two identical array keys Subjects.name not LIKE. The second overwrites the first

So you havw to use AndWhere()

$subjectMaths = $this->Students->Subjects->find('list')
    ->select(['id', 'name'])
    ->where(['Subjects.name not LIKE'  => '%assessment%'])
    ->andWhere(['Subjects.name LIKE'  => '%maths%']) 
    ->andWhere(['Subjects.name not LIKE'  => '%scholarship%']) 
    ->order(['Subjects.name' => 'asc']);

Answered By - arilia