查询生成器:具有复合列的IN子句 [英] query builder: IN clause with composite columns

问题描述

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

...
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查询构建器.我尝试过

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

但出现错误:

无法将值转换为字符串

我知道手动创建操纵数组的查询并不难，但是我想知道是否有轻松的方法.

解决方案

AFAICT使用数组语法或正则比较表达式尚无法(目前)，负责转换的代码仅处理单个字段和平面数组，请参见

来源> \ Cake \ Database \ Expression \ Comparison :: _ stringExpression()

但是，使用元组比较表达式很可能做到这一点，该表达式支持开箱即用地处理元组集.在内部，协会使用它来处理组合键.

$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')
);

来源> \ Cake \ Database \ Expression \ TupleComparison

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.

解决方案

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

这篇关于查询生成器:具有复合列的IN子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！