[FIXED] query builder: IN clause with composite columns

Issue

I need to create a IN conditions on multiple columns, like this

...
WHERE 
(order_date, order_number) IN (
    ('2016-03-11', 3455453), 
    ('2016-03-18', 83545454), 
    ('2016-06-17', 5354544)
)

starting from an array like this:

$orders = [
    ['2016-03-11', 3455453], 
    ['2016-03-18', 83545454], 
    ['2016-06-17', 5354544]
];

using cake3 query builder. I tried with

->where(['(order_date, order_number) IN' => $orders]);

but I get an error:

Cannot convert value to string

I know it's not hard to manually create the query manipulating the array, but I'd like to know if there is a cake way to do it.

Solution

AFAICT this is not possible (yet) using the array syntax or regular comparison expressions, the code responsible for transforming only handles single fields and flat arrays, see

Source > \Cake\Database\Expression\Comparison::_stringExpression()

However, this is very well possible using a tuple comparison expression, which supports handling sets of tuples out of the box. Internally it is used by associations for handling composite keys.

$fields = ['order_date', 'order_number'];
$types = ['date', 'integer'];
$values = [
    ['2016-03-11', 3455453], 
    ['2016-03-18', 83545454], 
    ['2016-06-17', 5354544]
];

$query->where(
    new \Cake\Database\Expression\TupleComparison($fields, $values, $types, 'IN')
);

Source > \Cake\Database\Expression\TupleComparison

Answered By - ndm