CODEWITHSUNDEEP
phpcakephpcakephp-3.0
J.B.J.
J.B.J.

Reputation: 430

CakePHP 3 - Find with multiple conditions

I am trying to make a query to find all rows with a certain set or ID's.

For instance I wanna find all with a device_id field that has one of the following values: 1 3 5

I tried adding it as an array, but I get an error saying: Cannot convert value to integer

Here is my code:

$revenuesQuery = $revenues->find('all', [
        'conditions' => [
            'device_id' => [1, 3, 5],
            'date' => date('Y-m-d')
        ]
    ]);

As you see, the device_id is an array of integers (3 and 4) and I wan't rows that has one of these (either a 3 or a 4). If I change the code to not use an array but a hard integer like this:

$revenuesQuery = $revenues->find('all', [
        'conditions' => [
            'device_id' => 3,
            'date' => date('Y-m-d')
        ]
    ]);

It works just fine?

Here is my query as debugged:

'(help)' => 'This is a Query object, to get the results execute or iterate it.',
'sql' => 'SELECT Revenues.id AS `Revenues__id`, Revenues.device_id AS `Revenues__device_id`, Revenues.revenue AS `Revenues__revenue`, Revenues.date AS `Revenues__date` FROM revenues Revenues WHERE (device_id = :c0 AND date = :c1)',
'params' => [
    ':c0' => [
        'value' => [
            (int) 0 => (int) 3,
            (int) 1 => (int) 4
        ],
        'type' => 'integer',
        'placeholder' => 'c0'
    ],
    ':c1' => [
        'value' => '2017-12-17',
        'type' => 'date',
        'placeholder' => 'c1'
    ]
]

Thanks in advance.

Upvotes: 3

Views: 5179

Answers (2)

gdm
gdm

Reputation: 7928

Take a look at Query Builder of Cake 3: Case statements

You can do as follows:

   $devIds = [1,2,3];
   $revenuesQuery = $this->revenues->find()
            ->where(function (QueryExpression $exp) use ($devIds) {
                return $exp
                        ->in('device_id', $devIds)
                        ->eq('date', q->func()->now() );
            });

Upvotes: 0

Reactgular
Reactgular

Reputation: 54771

What you're looking for is called the IN operator.

Add the word IN to the end of the field name.

$revenuesQuery = $revenues->find('all', [
    'conditions' => [
        'device_id IN' => [1, 3, 5],
        'date' => date('Y-m-d')
    ]
]);

Alternatively the query expression builder is more verbose, and my preferred way of creating queries in CakePHP.

$q = $revenues->query();
$revenuesQuery = $revenues->find('all')
    ->where([
        $q->newExp()->in('device_id', [1, 3, 5]),
        $q->newExp()->eq('date', $q->func()->now())
    ]);

Upvotes: 2

Related Questions