Hi, first time posting.
I have a table with reservations containing fields including reservedFrom and reservedTo. I wish to create a find method that checks for reservations within a given period (say from today until next week).
I’m using BETWEEN condition on both reservedFrom and reservedTo and comparing against the given date period.
#in Controller
$this->ToolsReservations->find('reservationsInDates', ['from' => '2018-04-15', 'to' => '2018-04-20']);#in Model
public function findReservationsInDates($q, $options) {
/**
* Table
* column reservedFrom datetime
* column reservedTo datetime
*
* param $options['from'] datetime From period
* param $options['to'] datetime To period
*/
if(!isset($options['from']))$options['from'] = Time::now();
if(!isset($options['to']))$options['to'] = Time::now();
/** @var Query $q */
return $q->where(function($exp) use ($options) {
/** @var QueryExpression $exp */
$orCond = $exp->or_(function($or) use ($options) {
/** @var QueryExpression $or */
return $or
->between('reservedFrom', $options['from'], $options['to'], 'datetime')
->between('reservedTo', $options['from'], $options['to'], 'datetime');
});
return $exp->add($orCond);
});
}This generates the following query:
object(Cake\ORM\Query) {
'(help)' => 'This is a Query object, to get the results execute or iterate it.',
'sql' => 'SELECT ToolsReservations.id AS `ToolsReservations__id`, ToolsReservations.reservedFrom AS `ToolsReservations__reservedFrom`, ToolsReservations.reservedTo AS `ToolsReservations__reservedTo` FROM tools_reservations ToolsReservations WHERE (reservedFrom BETWEEN :c0 AND :c1 OR reservedTo BETWEEN :c2 AND :c3)',
'params' => [
':c0' => [
'value' => object(Cake\I18n\FrozenTime) {
'time' => '2018-04-16T00:00:00+02:00',
'timezone' => 'Europe/Oslo',
'fixedNowTime' => false
},
'type' => 'datetime',
'placeholder' => 'c0'
],
':c1' => [
'value' => object(Cake\I18n\FrozenTime) {
'time' => '2018-04-19T00:00:00+02:00',
'timezone' => 'Europe/Oslo',
'fixedNowTime' => false
},
'type' => 'datetime',
'placeholder' => 'c1'
],
':c2' => [
'value' => object(Cake\I18n\FrozenTime) {
'time' => '2018-04-16T00:00:00+02:00',
'timezone' => 'Europe/Oslo',
'fixedNowTime' => false
},
'type' => 'datetime',
'placeholder' => 'c2'
],
':c3' => [
'value' => object(Cake\I18n\FrozenTime) {
'time' => '2018-04-19T00:00:00+02:00',
'timezone' => 'Europe/Oslo',
'fixedNowTime' => false
},
'type' => 'datetime',
'placeholder' => 'c3'
]
],
'defaultTypes' => [
'ToolsReservations__id' => 'integer',
'ToolsReservations.id' => 'integer',
'id' => 'integer',
'ToolsReservations__user_id' => 'integer',
'ToolsReservations.user_id' => 'integer',
'user_id' => 'integer',
'ToolsReservations__tool_id' => 'integer',
'ToolsReservations.tool_id' => 'integer',
'tool_id' => 'integer',
'ToolsReservations__reservedFrom' => 'datetime',
'ToolsReservations.reservedFrom' => 'datetime',
'reservedFrom' => 'datetime',
'ToolsReservations__reservedTo' => 'datetime',
'ToolsReservations.reservedTo' => 'datetime',
'reservedTo' => 'datetime',
'ToolsReservations__reservedDate' => 'datetime',
'ToolsReservations.reservedDate' => 'datetime',
'reservedDate' => 'datetime',
'ToolsReservations__comment' => 'string',
'ToolsReservations.comment' => 'string',
'comment' => 'string'
],
'decorators' => (int) 0,
'executed' => false,
'hydrate' => true,
'buffered' => true,
'formatters' => (int) 0,
'mapReducers' => (int) 0,
'contain' => [],
'matching' => [],
'extraOptions' => [
'from' => object(Cake\I18n\FrozenTime) {
'time' => '2018-04-16T00:00:00+02:00',
'timezone' => 'Europe/Oslo',
'fixedNowTime' => false
},
'to' => object(Cake\I18n\FrozenTime) {
'time' => '2018-04-19T00:00:00+02:00',
'timezone' => 'Europe/Oslo',
'fixedNowTime' => false
}
],
'repository' => object(App\Model\Table\ToolsReservationsTable) {
'registryAlias' => 'ToolsReservations',
'table' => 'tools_reservations',
'alias' => 'ToolsReservations',
'entityClass' => 'App\Model\Entity\ToolsReservation',
'associations' => [
(int) 0 => 'users',
(int) 1 => 'tools'
],
'behaviors' => [
(int) 0 => 'Timestamp'
],
'defaultConnection' => 'default',
'connectionName' => 'default'
}
}Just the query:
SELECT ToolsReservations.id AS `ToolsReservations__id`, ToolsReservations.reservedFrom AS `ToolsReservations__reservedFrom`, ToolsReservations.reservedTo AS `ToolsReservations__reservedTo` FROM tools_reservations ToolsReservations WHERE (reservedFrom BETWEEN :c0 AND :c1 OR reservedTo BETWEEN :c2 AND :c3)As you can see in the generated query, the entire where clause is enveloped inside parentheses, however that returns no results. If I surround each between clause with parentheses it returns the correct values.
This is the query I want to have created.
SELECT ToolsReservations.id AS `ToolsReservations__id`, ToolsReservations.reservedFrom AS `ToolsReservations__reservedFrom`, ToolsReservations.reservedTo AS `ToolsReservations__reservedTo` FROM tools_reservations ToolsReservations WHERE ((reservedFrom BETWEEN :c0 AND :c1) OR (reservedTo BETWEEN :c2 AND :c3))Is there any way I can envelop each between clause within their own parentheses to create this query?
I altered the BetweenExpression class in CakePHP’s core to be able to do this, but that is not really something one should want to do, as the code will be broken if I update the core.
Here is what I changed in the core is the returned sprintf on the bottom of this function. Added parentheses on either side of the string.
#File: cakephp\cakephp\src\Database\Expression\BetweenExpression.php
#Line: From 72 to 99
/**
* Converts the expression to its string representation
*
* @param \Cake\Database\ValueBinder $generator Placeholder generator object
* @return string
*/
public function sql(ValueBinder $generator)
{
$parts = [
'from' => $this->_from,
'to' => $this->_to
];
$field = $this->_field;
if ($field instanceof ExpressionInterface) {
$field = $field->sql($generator);
}
foreach ($parts as $name => $part) {
if ($part instanceof ExpressionInterface) {
$parts[$name] = $part->sql($generator);
continue;
}
$parts[$name] = $this->_bindValue($part, $generator, $this->_type);
}
#old: return sprintf('%s BETWEEN %s AND %s', $field, $parts['from'], $parts['to']);
return sprintf('(%s BETWEEN %s AND %s)', $field, $parts['from'], $parts['to']);
}Is this something I should file as an issue on github, or is there another way I’ve yet to find?
Pastebin of table with columns and fields: https://pastebin.com/8kWfeJkZ