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']
To view records of database, we first need to get hold of a table using the TableRegistry class. We can fetch the instance out of registry using get() method. The get() method will take the name of the database table as argument. Now, this new instance is used to find records from database using find() method.
Add below line in your model where you want print query. $last_query = $ this ->ModelName->getLastQuery(); As we have saved last executed query in variable $last_query then use this to print last executed query.
To apply ordering, you can use the order method: $query = $articles->find() ->order(['title' => 'ASC', 'id' => 'ASC']);
A new addition to the CakePHP 1.2 core is the ContainableBehavior . This model behavior allows you to filter and limit model find operations. Using Containable will help you cut down on needless wear and tear on your database, increasing the speed and overall performance of your application.
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 (which is anything but recommended! See the security notes in the manual for PDO::quote()
), 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 a raw SQL snippet with value bindig instead.
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');
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.
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.
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'
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!
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.
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
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);
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With