CakePHP 3.0 ->查找条件之间 [英] CakePHP 3.0 -> Between find condition
问题描述
是否可以做一个BETWEEN ? AND ?"在 cakephp 2.5 中的条件 LIKE 在哪里?在 cakephp 2.5 中，我写了类似
Is it possible to do a "BETWEEN ? AND ?" where condition LIKE in cakephp 2.5? In cakephp 2.5 I write something like
'conditions' => ['start_date BETWEEN ? AND ?' => ['2014-01-01', '2014-12-32']]
我该如何迁移?
另外我会写一些像
'conditions' => [ '? BETWEEN start_date AND end_date'] => '2014-03-31']
推荐答案
表达式
开箱即用地支持之间的表达式，但是它们仅支持第一种情况而无需额外处理:
Expressions
Between expression are supported out of the box, however they only support the first case without additional fiddling:
$Query = $Table
->find()
->where(function($exp) {
return $exp->between('start_date', '2014-01-01', '2014-12-32', 'date');
});
如果您想通过 between 方法处理第二种情况，那么您必须将所有值作为表达式传递，这很容易出错，因为在这种情况下它们不会受到转义/参数绑定的影响，你必须自己做(这是不推荐的！请参阅 PDO::quote()) 的手册，内容大致如下:
If you'd wanted to handle the second case via the between method, then you'd have to pass all values as expressions, which can easily go wrong, as they will not be subject to escaping/parameter binding in that case, you'd have to do that on your own (which is anything but recommended! See the security notes in the manual for PDO::quote()), something along the lines of:
use CakeDatabaseExpressionIdentifierExpression;
use CakeDatabaseExpressionQueryExpression;
use CakeORMQuery;
// ...
$Query = $Table
->find()
->where(function(QueryExpression $exp, Query $query) {
return $exp->between(
$query->newExpr(
$query->connection()->driver()->quote(
'2014-03-31',
PDO::PARAM_STR
)
),
new IdentifierExpression('start_date'),
new IdentifierExpression('end_date')
);
});
对于这样一个由 CakePHP 附带的所有 SQL 方言支持的基本 SQL 表达式来说，这可能感觉有点不方便，因此您可能有理由在这里使用带有值 bindig 的原始 SQL 片段.
That might feel a little inconvenient for such a basic SQL expression that is supported by all SQL dialects that CakePHP ships with, so you may have a reason here to use a raw SQL snippet with value bindig instead.
然而应该注意的是，当涉及到例如跨方言支持时，表达式通常是更好的选择，因为它们可以(或多或少)在编译时轻松转换，请参阅 SqlDialectTrait:: 的实现_expressionTranslators().表达式通常也支持自动标识符引用.
It should be noted however that expressions are often the better choice when it comes to for example cross dialect support, as they can be (more or less) easily transformed at compile time, see the implementations of SqlDialectTrait::_expressionTranslators(). Also expressions usually support automatic identifier quoting.
通过手动值绑定，您几乎可以创建任何您喜欢的东西.但是应该注意的是，只要有可能，您应该使用表达式来代替，因为它们更容易移植，这对于很多表达式来说都是开箱即用的.
Via manual value binding you can pretty much create anything you like. It should however be noted that whenever possible, you should use expressions instead, as they are easier to port, which happens out of the box for quite a few expressions already.
$Query = $Table
->find()
->where([
'start_date BETWEEN :start AND :end'
])
->bind(':start', '2014-01-01', 'date')
->bind(':end', '2014-12-31', 'date');
这样第二种情况也很容易解决，比如:
That way the second case can also be solved very easily, like:
$Query = $Table
->find()
->where([
':date BETWEEN start_date AND end_date'
])
->bind(':date', '2014-03-31', 'date');
两者的混合(最安全和最兼容的方法)
也可以将两者混合使用，即使用使用自定义绑定的表达式，大致如下:
A mixture of both (safest and most compatible approach)
It's also possible to mix both, ie use an expression that makes use of custom bindings, something along the lines of this:
use CakeDatabaseExpressionIdentifierExpression;
use CakeDatabaseExpressionQueryExpression;
use CakeORMQuery;
// ...
$Query = $Table
->find()
->where(function(QueryExpression $exp, Query $query) {
return $exp->between(
$query->newExpr(':date'),
new IdentifierExpression('start_date'),
new IdentifierExpression('end_date')
);
})
->bind(':date', '2014-03-31', 'date');
这样您就可以使用可能的可移植表达式处理第二种情况，而不必担心手动引用/转义输入数据和标识符.
That way you could handle the second case using possibly portable expressions, and don't have to worry about quoting/escaping input data and identifiers manually.
说了这么多，最后 BETWEEN 与使用两个单独的简单条件是一样的:
All that being said, in the end BETWEEN is just the same as using two separate simple conditions like this:
$Query = $Table
->find()
->where([
'start_date >=' => '2014-01-01',
'start_date <=' => '2014-12-32',
]);
$Query = $Table
->find()
->where([
'start_date >=' => '2014-03-31',
'end_date <=' => '2014-03-31',
]);
但是不要生气，如果您一直阅读到这里，至少您了解了查询构建器的来龙去脉.
But don't be mad, if you read all the way down to here, at least you learned something about the ins and outs of the query builder.
目前好像只有两种选择. 内核现在支持开箱即用，以下仅供参考.
Currently there seems to be only two options. The core now supports this out of the box, the following is just kept for reference.
现在 ORM 查询构建器 (CakeORMQuery)，当在表对象上调用例如 find() 时正在检索的构建器，不会'不支持值绑定
For now the ORM query builder (CakeORMQuery), the one that is being retrived when invoking for example find() on a table object, doesn't support value binding
https://github.com/cakephp/cakephp/issues/4926
因此，为了能够使用绑定，您必须使用底层数据库查询构建器 (CakeDatabaseQuery)，例如可以通过 Connection::newQuery().
So, for being able to use bindings you'd have to use the underlying database query builder (CakeDatabaseQuery), which can for example be retrived via Connection::newQuery().
这是一个例子:
$conn = ConnectionManager::get('default');
$Query = $conn->newQuery();
$Query
->select('*')
->from('table_name')
->where([
'start_date BETWEEN :start AND :end'
])
->bind(':start', new DateTime('2014-01-01'), 'date')
->bind(':end', new DateTime('2014-12-31'), 'date');
debug($Query->execute()->fetchAll());
这将导致类似这样的查询
This would result in a query similar to this
SELECT
*
FROM
table_name
WHERE
start_date BETWEEN '2014-01-01' AND '2014-12-31'
自定义表达式类
另一种选择是自定义表达式 生成适当的 SQL 片段的类.这是一个例子.
A custom expression class
Another option would be a custom expression class that generates appropriate SQL snippets. Here's an example.
列名应该被包装到标识符表达式对象中，以便它们被自动引用(如果启用了自动引用)，键 >value 数组语法用于绑定值，其中数组键是实际值，数组值是数据类型.
Column names should be wrapped into identifier expression objects in order to them be auto quoted (in case auto quoting is enabled), the key > value array syntax is for binding values, where the array key is the actual value, and the array value is the datatype.
请注意，直接传递用户输入的列名是不安全的，因为它们没有被转义！使用白名单或类似名单来确保列名可以安全使用！
use AppDatabaseExpressionBetweenComparison;
use CakeDatabaseExpressionIdentifierExpression;
// ...
$between = new BetweenComparison(
new IdentifierExpression('created'),
['2014-01-01' => 'date'],
['2014-12-31' => 'date']
);
$TableName = TableRegistry::get('TableName');
$Query = $TableName
->find()
->where($between);
debug($Query->execute()->fetchAll());
这将生成一个类似于上面的查询.
This would generate a query similar to the one above.
use AppDatabaseExpressionBetweenComparison;
use CakeDatabaseExpressionIdentifierExpression;
// ...
$between = new BetweenComparison(
['2014-03-31' => 'date'],
new IdentifierExpression('start_date'),
new IdentifierExpression('end_date')
);
$TableName = TableRegistry::get('TableName');
$Query = $TableName
->find()
->where($between);
debug($Query->execute()->fetchAll());
另一方面，这将导致与此类似的查询
This on the other hand would result in a query similar to this
SELECT
*
FROM
table_name
WHERE
'2014-03-31' BETWEEN start_date AND end_date
表达式类
namespace AppDatabaseExpression;
use CakeDatabaseExpressionInterface;
use CakeDatabaseValueBinder;
class BetweenComparison implements ExpressionInterface {
protected $_field;
protected $_valueA;
protected $_valueB;
public function __construct($field, $valueA, $valueB) {
$this->_field = $field;
$this->_valueA = $valueA;
$this->_valueB = $valueB;
}
public function sql(ValueBinder $generator) {
$field = $this->_compilePart($this->_field, $generator);
$valueA = $this->_compilePart($this->_valueA, $generator);
$valueB = $this->_compilePart($this->_valueB, $generator);
return sprintf('%s BETWEEN %s AND %s', $field, $valueA, $valueB);
}
public function traverse(callable $callable) {
$this->_traversePart($this->_field, $callable);
$this->_traversePart($this->_valueA, $callable);
$this->_traversePart($this->_valueB, $callable);
}
protected function _bindValue($value, $generator, $type) {
$placeholder = $generator->placeholder('c');
$generator->bind($placeholder, $value, $type);
return $placeholder;
}
protected function _compilePart($value, $generator) {
if ($value instanceof ExpressionInterface) {
return $value->sql($generator);
} else if(is_array($value)) {
return $this->_bindValue(key($value), $generator, current($value));
}
return $value;
}
protected function _traversePart($value, callable $callable) {
if ($value instanceof ExpressionInterface) {
$callable($value);
$value->traverse($callable);
}
}
}
这篇关于CakePHP 3.0 ->查找条件之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
