CakePHP 3.0 - >在找到条件 [英] CakePHP 3.0 -> Between find condition
问题描述
可以做一个BETWEEN？AND？ where condition LIKE in cakephp 2.5？
在cakephp 2.5中我写的是
'conditions'=> ['start_date BETWEEN？ AND？'=> ['2014-01-01'，'2014-12-32']]
我如何
'条件'=> ['？ BETWEEN start_date AND end_date'] => '2014-03-31']
h2>
表达式之间支持开箱即用，但它们只支持第一种情况，而不需要额外的fiddling：
$ Query = $ Table
- > find（）
- > where（function（$ exp）{
return $ exp-& start_date'，'2014-01-01'，'2014-12-32'，'date'）;
}）;
如果你想通过between方法处理第二种情况，将所有的值作为表达式传递，这很容易出错，因为在这种情况下它们不会被转义/参数绑定，你必须自己这样做，例如：
使用Cake \Database\Expression\IdentifierExpression;
使用Cake \Database\Expression\QueryExpression;
使用Cake\ORM\Query;
// ...
$ Query = $ Table
- > find（）
- >其中（function（QueryExpression $ exp ，查询$ query）{
return $ exp-> between（
$ query-> newExpr（
$ query-> connection（） - > driver quote（
'2014-03-31'，
\ PDO :: PARAM_STR
）
），
new IdentifierExpression（'start_date'），
new IdentifierExpression（'end_date'）
）;
}）;
对于这样一个基本的SQL表达式，可能会感觉有点不方便，所有的SQL方言都支持CakePHP
但是，应当注意，表达式通常是更好的选择，当谈到跨方言支持，因为他们可以（或多或少）在编译时容易转换，请参阅 SqlDialectTrait :: _ expressionTranslators（）的实现。此外，表达式通常支持自动标识符引用。
值绑定
通过手动值绑定，你喜欢。然而，应该注意，只要有可能，您应该使用表达式，因为它们更容易端口，这发生在很多表达式的框外。
$ Query = $ Table
- > find（）
- >其中b $ b'start_date BETWEEN：start AND：end'
]）
- > bind（'：start'，'2014-01-01'，'date'）
- > ; bind（'：end'，'2014-12-31'，'date'）;
这样第二种情况也很容易解决，例如：
$ Query = $ Table
- > find（）
- >其中（[
'：date BETWEEN start_date AND end_date'
]）
- > bind（'：date'，'2014-03-31'，'date'）;
两者的混合
可以混合使用，也就是使用一个使用自定义绑定的表达式，这样的代码如下：
\\ Database \Expression\IdentifierExpression;
使用Cake \Database\Expression\QueryExpression;
使用Cake\ORM\Query;
// ...
$ Query = $ Table
- > find（）
- >其中（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'）;
这样你可以使用可能的便携式表达式来处理第二种情况，而不必担心
另请参阅
目前似乎只有两个选项。核心现在支持这种开箱即用的以下仅供参考。
值绑定（通过数据库查询构建器）
现在，ORM查询构建器c> Cake \ ORM \Query ），在表对象上调用例如 find（）时，不支持值绑定
https://github.com/cakephp/cakephp/issues/4926
因此，为了能够使用绑定必须使用基础数据库查询构建器（ Cake \Database \Query ），例如可以通过 Connection :: newQuery c $ c> 。
以下是一个示例：
$ conn = ConnectionManager :: get（'default'）;
$ Query = $ conn-> newQuery（）;
$ Query
- > select（'*'）
- > from（'table_name'）
- > where（[
'start_date BETWEEN：开始AND：结束'
]）
- > bind（'：start'，new \DateTime（'2014-01-01'），'date'）
- > bind（'：end'，new \DateTime（'2014-12-31'），'date'）;
debug（$ Query-> execute（） - > fetchAll（））;
这将导致类似于
的查询 SELECT
*
FROM
table_name
WHERE
start_date BETWEEN'2014-01-01'AND'2014- 12-31'
自定义表达式类< h1>
另一种选择是自定义 表达式 ，以生成相应的SQL代码段。这里是一个例子。
列名应该被包装到标识符表达式对象中，以便自动引用它们（如果启用自动引用），键>值数组语法是用于绑定值，其中数组键是实际值，数组值是数据类型。
请注意，直接传递列名的用户输入，因为它们没有被转义！使用白名单或类似方法确保列名安全使用！
值之间的字段
使用App\Database\Expression\BetweenComparison;
使用Cake \Database\Expression\IdentifierExpression;
// ...
$ 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（））;
这将生成类似上面的查询。
字段之间的值
使用App\Database\Expression\BetweenComparison;
使用Cake \Database\Expression\IdentifierExpression;
// ...
$ 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（））;
另一方面，这将导致类似于
的查询 SELECT
*
FROM
table_name
WHERE
'2014-03-31'BETWEEN start_date AND end_date
表达式类
命名空间App\Database\Expression;
使用Cake \Database \ExpressionInterface;
使用Cake \Database\ValueBinder;
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 ）;
} 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）;
}
}
}
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']]
how can I migrate that?
additionally I would write something like
'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');
});
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, something along the lines of:
use Cake\Database\Expression\IdentifierExpression;
use Cake\Database\Expression\QueryExpression;
use Cake\ORM\Query;
// ...
$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')
);
});
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 value bindig instead.
It should be noted however that expressions are generally the better choice when it comes to 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.
Value binding
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
It's also possible to mix both, ie use an expression that makes use of custom bindings, something along the lines of this:
use Cake\Database\Expression\IdentifierExpression;
use Cake\Database\Expression\QueryExpression;
use Cake\ORM\Query;
// ...
$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.
See also
- Cookbook > Database Access & ORM > Query Builder > Advanced Conditions
- API > \Cake\Database\Query::bind()
Currently there seems to be only two options. The core now supports this out of the box, the following is just kept for reference.
Value binding (via the database query builder)
For now the ORM query builder (Cake\ORM\Query), 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
So, for being able to use bindings you'd have to use the underlying database query builder (Cake\Database\Query), which can for example be retrived via Connection::newQuery().
Here's an example:
$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'
A custom expression class
Another option would be a custom expression class that generates appropriate SQL snippets. Here's an example.
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.
Please note that it's not safe to directly pass user input for column names, as they are not being escaped! Use a whitelist or similar to make sure the column name is safe to use!
Field between values
use App\Database\Expression\BetweenComparison;
use Cake\Database\Expression\IdentifierExpression;
// ...
$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.
Value between fields
use App\Database\Expression\BetweenComparison;
use Cake\Database\Expression\IdentifierExpression;
// ...
$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
The expression class
namespace App\Database\Expression;
use Cake\Database\ExpressionInterface;
use Cake\Database\ValueBinder;
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屋！
