join and orwhere ends to AND

Question

I have the follwing code:

$_duplicates = $this->find()
    ->innerJoin(
        ['c' => 'contacts'],    //alias
        [
            'Contacts.contactname != ' => '',
            'Contacts.id < c.id',
            'c.id > ' => 0
        ]
        )
    ->select(['Contacts.id', 'Contacts.contactname', 'Contacts.legalname',
              'c.id', 'c.contactname', 'c.legalname'])
    ->orWhere([
        'LEVENSHTEIN(Contacts.contactname, c.contactname) <= ' => $distance,
        'LEVENSHTEIN(Contacts.contactname, c.legalname) <= ' => $distance,
        'LEVENSHTEIN(Contacts.legalname, c.contactname) <= ' => $distance,
        'LEVENSHTEIN(Contacts.legalname, c.legalname) <= ' => $distance
        ]);
debug($_duplicates);

The debug there gives this output:

SELECT Contacts.id AS `Contacts__id`, Contacts.contactname AS `Contacts__contactname`, 
  Contacts.legalname AS `Contacts__legalname`, c.id AS `c__id`,
  c.contactname AS `c__contactname`, c.legalname AS `c__legalname` 
FROM contacts Contacts 
INNER JOIN contacts c 
ON (Contacts.contactname != :c0 AND Contacts.id < c.id AND c.id > :c1)   
WHERE (
   Contacts.active = :c2 
   AND (
      LEVENSHTEIN(Contacts.contactname, c.contactname) <= :c3 
      AND LEVENSHTEIN(Contacts.contactname, c.legalname) <= :c4 
      AND LEVENSHTEIN(Contacts.legalname, c.contactname) <= :c5 
      AND LEVENSHTEIN(Contacts.legalname, c.legalname) <= :c6
    )
  )

Any ide why I get AND-s at LEVENSHTEIN calls and not OR? orWhere supposed to create OR relations there, right?

Answer 1

Because that's not how orWhere() is supposed to work, the OR condition is being used for combining with the previous conditions that have been defined via where/andWhere/orWhere(), ie

->where(['a' => 'b'])
->orWhere(['c' => 'd', 'e' => 'f'])

results in

(c = d AND e = f) OR a = b

If you need to combine all your conditions via OR, then you could either use multiple orWhere() calls

->orWhere(['a' => 'b'])
->orWhere(['c' => 'd'])
->orWhere(['e' => 'f'])

the OR key

->where([
    'OR' => [
        'a' => 'b'
        'c' => 'd'
        'e' => 'f'
    ]
])

or expressions

->where(function (\Cake\Database\Expression\QueryExpression $exp) {
    return
        $exp->or_([
            'a' => 'b'
            'c' => 'd'
            'e' => 'f'
        ]);
})

See also Cookbook > Database Access & ORM > Query Builder > Advanced Conditions