查询生成器:带有复合列的 IN 子句

问题描述

我需要在多列上创建一个 IN 条件，就像这样

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)
)

从这样的数组开始:

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

使用 cake3 查询构建器.我试过

using cake3 query builder. I tried with

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

但我收到一个错误:

无法将值转换为字符串

我知道手动创建操作数组的查询并不难，但我想知道是否有一种方法可以做到这一点.

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.

推荐答案

AFAICT 这是不可能的(还)使用数组语法或正则比较表达式，负责转换的代码仅处理单个字段和平面数组，请参阅

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

来源>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')
);

来源> CakeDatabaseExpressionTupleComparison