Expression

Between expressions are supported out of the box, however, they only support the first case without additional hassle:

$Query = $Table
    ->find()
    ->where(function($exp) {
        return $exp->between('start_date', '2014-01-01', '2014-12-32', 'date');
    });

      

        
        
        
      

    

If you wanted to handle the second case with the between method, then you need to pass all the values ​​as expressions, which can easily get wrong, since in that case they won't be subject to escaping / parameter binding, you have to do it yourself (that's nothing but recommendations! See security notes in manual forPDO::quote()

) something like:

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

      

        
        
        
      

    

This might seem a little awkward for such a basic SQL expression that is supported by all the SQL dialects that CakePHP ships with, so you might have a reason to use bindig instead.

It should be noted, however, that expressions are generally the best choice when it comes to cross-dialect support as they can (more or less) easily transform at compile time, see implementations SqlDialectTrait::_expressionTranslators()

. Also expressions usually support quoting an automatic identifier.

Linking values

With manual binding, you can pretty much create anything. It should be noted, however, that when possible, you should use expressions instead, as they are easier to carry around, which comes out of the box for multiple expressions already.

$Query = $Table
    ->find()
    ->where([
        'start_date BETWEEN :start AND :end'
    ])
    ->bind(':start', '2014-01-01', 'date')
    ->bind(':end',   '2014-12-31', 'date');

      

        
        
        
      

    

So the second case can also be solved very easily, for example:

$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 is also possible to mix both, i.e. use an expression that uses custom bindings, something like 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');

      

        
        
        
      

    

This way you can handle the second case using possibly portable expressions, and you don't have to worry about manually quoting / escaping the input and identifiers.

see also

• Cookbook> Database and ORM Access> Query Builder> Advanced Conditions
• API> \ Cake \ Database \ Query :: bind ()

Currently, there appear to be only two options. The kernel now supports this out of the box, the following is for reference only.

Linking values ​​(via the database query builder)

Currently the ORM ( Cake\ORM\Query

) query builder , the one that is retrieved when called on, for example, find()

a table object, does not support value binding

https://github.com/cakephp/cakephp/issues/4926

So, to be able to use the bindings, you will need to use the database query builder ( Cake\Database\Query

), which can, for example, be restored with . 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 will result in a query like this

SELECT 
    *
FROM
    table_name
WHERE
    start_date BETWEEN '2014-01-01' AND '2014-12-31'

      

        
        
        
      

    

Custom expression class

Another option would be a custom expression class that generates the appropriate SQL snippets. Here's an example.

Column names must be wrapped in identifier expression objects to be automatically referenced (in case auto-quoting is enabled), array keys> values ​​syntax for binding values, where the array key is the actual value and the array value is the data type.

Note that it is not safe to directly pass user input for column names, as they are not 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 will create a query similar to the query 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 will result in a query like this

SELECT 
    *
FROM
    table_name
WHERE
    '2014-03-31' BETWEEN start_date AND end_date

      

        
        
        
      

    

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

}