orm - CakePHP 3.0 -> Between find condition -

orm - CakePHP 3.0 -> Between find condition -

is possible "between ? , ?" status in cakephp 2.5? in cakephp 2.5 write like

'conditions' => ['start_date between ? , ?' => ['2014-01-01', '2014-12-32']]

how can migrate that?

additionally write

'conditions' => [ '? between start_date , end_date'] => '2014-03-31']

update in mean time between look added core, supports first case:

$query = $table ->find('all') ->where([function($exp) { homecoming $exp->between('start_date', '2014-01-01', '2014-12-32', 'date'); }]);

also value binding works fine orm query builder too:

$query = $table ->find('all') ->where([ 'start_date between :start , :end' ]) ->bind(':start', new \datetime('2014-01-01'), 'date') ->bind(':end', new \datetime('2014-12-31'), 'date'); debug($query->all()->toarray());

currently there seems 2 options.

value binding (via database query builder)

for orm query builder (cake\orm\query), 1 beingness retrived when invoking illustration find() on table object, doesn't back upwards value binding

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

so, beingness able utilize bindings you'd have utilize underlying database query builder (cake\database\query), can illustration retrived via connection::newquery().

here's example:

$conn = connectionmanager::get('default'); $query = $conn->newquery(); $query ->select('*') ->from('table_name') ->where([ 'start_date between :start , :end' ]) ->bind(':start', new \datetime('2014-01-01'), 'date') ->bind(':end', new \datetime('2014-12-31'), 'date'); debug($query->execute()->fetchall());

this result in query similar this

select * table_name start_date between '2014-01-01' , '2014-12-31' a custom look class

another alternative custom expression class generates appropriate sql snippets. here's example.

column names should wrapped identifier look objects in order them auto quoted (in case auto quoting enabled), key > value array syntax binding values, array key actual value, , array value datatype.

please note it's not safe straight pass user input column names, not beingness escaped! utilize whitelist or similar create sure column name safe use!

field between values use app\database\expression\betweencomparison; utilize 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('all') ->where($between); debug($query->execute()->fetchall());

this generate query similar 1 above.

value between fields use app\database\expression\betweencomparison; utilize 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('all') ->where($between); debug($query->execute()->fetchall());

this on other hand result in query similar this

select * table_name '2014-03-31' between start_date , end_date the look class namespace app\database\expression; utilize cake\database\expressioninterface; utilize 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); homecoming sprintf('%s between %s , %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); homecoming $placeholder; } protected function _compilepart($value, $generator) { if ($value instanceof expressioninterface) { homecoming $value->sql($generator); } else if(is_array($value)) { homecoming $this->_bindvalue(key($value), $generator, current($value)); } homecoming $value; } protected function _traversepart($value, callable $callable) { if ($value instanceof expressioninterface) { $callable($value); $value->traverse($callable); } } }

cakephp orm cakephp-3.0