How to create a `IN` clause in CakePHP query?

Question

How do you make it where in clause in new CakePHP? I'm trying:

$restaurants->find()->where(['id' => $r_ids])->all();

Where $r_ids is array of ids I need in my query, but this doesn't work as expected.

Filagree

Answer 1

With CakePHP 3.x it's now necessary to either indicate the data type, which for an array of values need to have [] appended to the type:

$query = $articles
    ->find()
    ->where(['id' => $ids], ['id' => 'integer[]']);

or to explicitly make use of the IN keyword:

$query = $articles
    ->find()
    ->where(['id IN' => $ids]);

See also

• Cookbook > Database Access & ORM > Query Builder > Automatically Creating IN Clauses

Lariat

If we have to check multiple columns in IN clause then how to do? What to do in this case : ->where(['id,name IN' => ??]); – Vanadous

@Vanadous You'll have to use the tuple comparison expression: https://mcmap.net/q/671494/-query-builder-in-clause-with-composite-columns – Lariat

tysm. This is what i was looking for. – Vanadous

Answer 2

Try this one for CakePHP 3.x

$query = $restaurants
    ->find()
    ->where(['id IN' => $r_ids]);
$query->all();

Lowboy

Answer 3

You can also use the short syntax:

    $result = $restaurants->find('all', 
            ['conditions' => ['id IN' =>$r_ids]]
        )->all();

Crank

Answer 4

In Cakephp 3.x, If you have multiple where conditions then your query will be as below:

return $this
        ->find()
        ->contain([
            'XYZ.Articles',
            'YYY.Managers',
            'ZZZ.Readers',
        ])
        ->where([
            'ID' => $ids,
            'READER.STATUS' => $xyz,
        ],[
            'ID' => 'integer[]'
        ])
        ;

Scarborough