php - query builder: IN clause with composite columns

Question

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.

Answer 1

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 > CakeDatabaseExpressionComparison::_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 CakeDatabaseExpressionTupleComparison($fields, $values, $types, 'IN')
);

Source > CakeDatabaseExpressionTupleComparison