CakePHP 3.0 -> Between find condition

cakephp orm cakephp-3.0
mdlouhy picture mdlouhy · Oct 17, 2014 · Viewed 16.8k times · Source

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']

Answer

ndm picture ndm · Oct 20, 2014

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 (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.

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 (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 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.

Regular comparison using array syntax

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.

See also

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

}

Related questions

CakePHP 3 contain select fields

I have the following DB tables: items, users, groups, itemImages. There are many to many relations between (items, groups) and (users, groups), and one to many between (items, itemImages). All the appropriate foreign keys have been setup in CakePHP as …

Read More
Cakephp 3 NOT IN query

I think this is a common pattern, but I can't find the elegant CakePHP way of doing it. The idea is to remove the values from a list which have already been chosen. To use an example from the Cake …

Read More
how using SQL IN operator in find method of cakephp ORM

i am beginner in cakephp , and i want use SQL IN operator in find method , i have words table. my code is : $this->Word->find('Word.wordid in (83,82)'); , and this code create this query : SELECT `Userword`.`userwordid`, `…

Read More