how to write mysql AND/OR in cakephp find() method

Question

I wanted to find out a way to write the following code into cakephp find() method but the didn't find related resource on the cakebook.

my code is

SELECT * FROM Customers
WHERE 
       (Country='Germany' AND City='München')
    OR (Country='Germany' AND CustomerName='München');

please share a way to write this accordingly in find() method. Thanks

Answer 1

You can do this using the OR key when using where:

$query = $this->Customers
    ->find()
    ->where([
        'OR' => [
            [
                'City' => 'München',
                'Country' => 'Germany'
            ],
            [
                'Country' => 'Germany',
                'CustomerName' => 'München'
            ]
        ]
    ]);

This could be simplified to:

$query = $this->Customers
    ->find()
    ->where([
        'Country' => 'Germany',
        'OR' => [
            ['City' => 'München'],
            ['CustomerName' => 'München']
        ]
    ]);

See http://book.cakephp.org/3.0/en/orm/query-builder.html#advanced-conditions, I find using the andWhere and orWhere functions in combination so just stick to where!

Answer 2

Try

$query = $this->Customers->find(
    'all',
    [
        'conditions' => [
            'Country' => 'Germany',
            'OR' => [
                [
                    'City' => 'München',

                ],
                [
                    'CustomerName' => 'München'
                ]
            ]
        ]
    ]
);

In the cookbook it says to wrap the or conditions in arrays if they are pertaining to the same field http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#complex-find-conditions

Answer 3

If you want to paginate results you can try this:

public function yourFunctionName() {

  $this->paginate['conditions']['and'][] = ['Country' => 'Germany'];
  $this->paginate['conditions']['and']['or'][] = ['City' => 'München'];
  $this->paginate['conditions']['and']['or'][] = ['CustomerName' => 'München'];

  $customers = $this->paginate($this->Customers);

  $this->set(compact('customers'));
  $this->set('_serialize', ['customers']);
}