When using arrays, each entry will be joined to the rest of the conditions using
an AND operator. Consecutive calls to this function will also join the new
conditions specified using the AND operator. Additionally, values can be
expressed using expression objects which can include other query objects.
Any conditions created with this methods can be used with any SELECT, UPDATE
and DELETE type of queries.
### Conditions using operators:
$query->where([
'posted >=' => new DateTime('3 days ago'),
'title LIKE' => 'Hello W%',
'author_id' => 1,
], ['posted' => 'datetime']);
The previous example produces:
WHERE posted >= 2012-01-27 AND title LIKE 'Hello W%' AND author_id = 1
Second parameter is used to specify what type is expected for each passed
key. Valid types can be used from the mapped with Database\Type class.
### Nesting conditions with conjunctions:
$query->where([
'author_id !=' => 1,
'OR' => ['published' => true, 'posted <' => new DateTime('now')],
'NOT' => ['title' => 'Hello']
], ['published' => boolean, 'posted' => 'datetime']
The previous example produces:
WHERE author_id = 1 AND (published = 1 OR posted < '2012-02-01') AND NOT (title = 'Hello')
You can nest conditions using conjunctions as much as you like. Sometimes, you
may want to define 2 different options for the same key, in that case, you can
wrap each condition inside a new array:
$query->where(['OR' => [['published' => false], ['published' => true]])
Keep in mind that every time you call where() with the third param set to false
(default), it will join the passed conditions to the previous stored list using
the AND operator. Also, using the same array key twice in consecutive calls to
this method will not override the previous value.
### Using expressions objects:
$exp = $query->newExpr()->add(['id !=' => 100, 'author_id' != 1])->tieWith('OR');
$query->where(['published' => true], ['published' => 'boolean'])->where($exp);
The previous example produces:
WHERE (id != 100 OR author_id != 1) AND published = 1
Other Query objects that be used as conditions for any field.
### Adding conditions in multiple steps:
You can use callable functions to construct complex expressions, functions
receive as first argument a new QueryExpression object and this query instance
as second argument. Functions must return an expression object, that will be
added the list of conditions for the query using the AND operator.
$query
->where(['title !=' => 'Hello World'])
->where(function ($exp, $query) {
$or = $exp->or_(['id' => 1]);
$and = $exp->and_(['id >' => 2, 'id <' => 10]);
return $or->add($and);
});
* The previous example produces:
WHERE title != 'Hello World' AND (id = 1 OR (id > 2 AND id < 10))
### Conditions as strings:
$query->where(['articles.author_id = authors.id', 'modified IS NULL']);
The previous example produces:
WHERE articles.author_id = authors.id AND modified IS NULL
Please note that when using the array notation or the expression objects, all
values will be correctly quoted and transformed to the correspondent database
data type automatically for you, thus securing your application from SQL injections.
If you use string conditions make sure that your values are correctly quoted.
The safest thing you can do is to never use string conditions.
| public where ( string | array | Cake\Database\ExpressionInterface | callable | null $conditions = null, array $types = [], boolean $overwrite = false ) | ||
| $conditions | string | array | Cake\Database\ExpressionInterface | callable | null | The conditions to filter on. |
| $types | array | associative array of type names used to bind values to query |
| $overwrite | boolean | whether to reset conditions with passed list or not |
public function findCursor(Query $query)
{
$current = $this->request->query('cursor');
$limit = $this->request->query('limit') ?: 10;
if ($current) {
$query->where(['id >' => $current]);
}
$query->limit($limit);
return $query;
}
/**
* Tests that empty values don't set where clauses.
*
* @return void
*/
public function testWhereEmptyValues()
{
$query = new Query($this->connection);
$query->from('comments')->where('');
$this->assertCount(0, $query->clause('where'));
$query->where([]);
$this->assertCount(0, $query->clause('where'));
}
public function findPublished(Query $query, array $options)
{
return $query->where(['published' => true]);
}
/**
* Generate a paging subquery for older versions of SQLserver.
*
* Prior to SQLServer 2012 there was no equivalent to LIMIT OFFSET, so a subquery must
* be used.
*
* @param \Cake\Database\Query $original The query to wrap in a subquery.
* @param int $limit The number of rows to fetch.
* @param int $offset The number of rows to offset.
* @return \Cake\Database\Query Modified query object.
*/
protected function _pagingSubquery($original, $limit, $offset)
{
$field = '_cake_paging_._cake_page_rownum_';
$order = $original->clause('order') ?: new OrderByExpression('(SELECT NULL)');
$query = clone $original;
$query->select(['_cake_page_rownum_' => new UnaryExpression('ROW_NUMBER() OVER', $order)])->limit(null)->offset(null)->order([], true);
$outer = new Query($query->connection());
$outer->select('*')->from(['_cake_paging_' => $query]);
if ($offset) {
$outer->where(["{$field} > " . (int) $offset]);
}
if ($limit) {
$value = (int) $offset + (int) $limit;
$outer->where(["{$field} <= {$value}"]);
}
// Decorate the original query as that is what the
// end developer will be calling execute() on originally.
$original->decorateResults(function ($row) {
if (isset($row['_cake_page_rownum_'])) {
unset($row['_cake_page_rownum_']);
}
return $row;
});
return $outer;
}
/**
* Generate a paging subquery for older versions of Oracle Server.
*
* Prior to Oracle 12 there was no equivalent to LIMIT OFFSET,
* so a subquery must be used.
*
* @param \Cake\Database\Query $original The query to wrap in a subquery.
* @param int $limit The number of rows to fetch.
* @param int $offset The number of rows to offset.
* @return \Cake\Database\Query Modified query object.
*/
protected function _pagingSubquery($original, $limit, $offset)
{
$field = 'cake_paging_out."_cake_page_rownum_"';
$query = clone $original;
$query->limit(null)->offset(null);
$outer = new Query($query->connection());
$outer->select(['cake_paging.*', '_cake_page_rownum_' => new SimpleExpression('ROWNUM')])->from(['cake_paging' => $query]);
$outer2 = new Query($query->connection());
$outer2->select('*')->from(['cake_paging_out' => $outer]);
if ($offset) {
$outer2->where(["{$field} > " . (int) $offset]);
}
if ($limit) {
$value = (int) $offset + (int) $limit;
$outer2->where(["{$field} <= {$value}"]);
}
$original->decorateResults(function ($row) {
if (isset($row['_cake_page_rownum_'])) {
unset($row['_cake_page_rownum_']);
}
return $row;
});
return $outer2;
}
protected function _addBooleanCondition(Query $query, $fieldName, $value, array $options = array())
{
if (in_array($value, [0, 1, true, false, 'true', 'false'])) {
$query->where([$fieldName => $value]);
}
}
/**
* Available options are:
*
* - for: The id of the record to read.
* - direct: Boolean, whether to return only the direct (true), or all (false) children,
* defaults to false (all children).
* - order : The order to apply on found nodes. Default on 'model_sort_fieldname' config
*
* If the direct option is set to true, only the direct children are returned (based upon the parent_id field)
*
* @param \Cake\ORM\Query $query
* @param array $options Array of options as described above
* @return \Cake\ORM\Query
*/
public function findChildren(Query $query, array $options)
{
$default_options = ['direct' => false, 'sort' => []];
$options = array_merge($default_options, $options);
$for = isset($options['for']) ? $options['for'] : null;
if (empty($for)) {
throw new \InvalidArgumentException("The 'for' key is required for find('children')");
}
if ($options['direct']) {
/*
* Add order clause if not already set
*/
if ($query->clause('order') === null) {
$sort = !empty($options['sort']) ? $options['sort'] : [$this->config('model_sort_fieldname') => 'asc'];
$query->order($sort);
}
$query->where([$this->config('model_parent_id_fieldname') => $for]);
return $query;
} else {
/*
SELECT nodes.*, t2.max_level as level
FROM nodes
INNER JOIN
(
SELECT nac.node_id, MAX(level) as max_level
FROM nodes_ancestors nac
INNER JOIN
(
SELECT node_id
FROM nodes_ancestors
WHERE ancestor_id = 1
) t ON t.node_id = nac.node_id
GROUP BY node_id
) t2 ON nodes.id = t2.node_id
ORDER BY max_level ASC, sort ASC
*/
$ancestorTable = $this->getAncestorTable($this->_table);
$subquery2 = $ancestorTable->find()->select(['nac_node_id' => 'node_id'])->where(['ancestor_id' => $for]);
$subquery1 = $ancestorTable->find()->select(['node_id' => 'nac_node_id', 'max_level' => $subquery2->func()->max('level')])->join(['table' => $subquery2, 'alias' => 't', 'type' => 'INNER', 'conditions' => 't.nac_node_id = Ancestors.node_id'])->group(['node_id']);
$selected_fields = $this->_table->schema()->columns();
$selected_fields['level'] = 't2.max_level';
$query->select($selected_fields)->join(['table' => $subquery1, 'alias' => 't2', 'type' => 'INNER', 'conditions' => $this->_table->alias() . '.id = t2.node_id'])->order(['max_level' => 'ASC', 'sort' => 'ASC']);
return $query;
// /*
// SELECT n2.*
// FROM nodes n1
// INNER JOIN nodes_ancestors ON ancestor_id = n1.id
// INNER JOIN nodes n2 ON node_id = n2.id
// WHERE ancestor_id = 1
// ORDER BY level ASC, n1.sort ASC
// */
// /*
// * 1) Find all nodes linked to the ancestors that are under the searched item
// * 2) Create a new collection based on the items as we don't want a Collection of ancestors
// * 3) if $options['multilevel'] is true -> organize items as a multilevel array
// */
// $ancestor_table = $this->getAncestorTable($this->_table);
// $model_alias = $this->_table->alias();
// $ancestor_table->belongsTo($model_alias, [
// 'className' => $model_alias,
// 'foreignKey' => 'node_id',
// 'propertyName' => 'linked_node'
// ]);
// $order = [];
// $order['level'] = 'ASC';
// if(isset($options['sort']))
// {
// $order = $order + $options['sort'];
// }
// $query = $ancestor_table->find();
// $query->contain([$model_alias]);
// $query->order($order);
// $query->where(['ancestor_id' => $for]);
// $nodes = [];
// foreach($query as $ancestor_entity){
// $nodes[] = $ancestor_entity->linked_node;
// }
// return new \Cake\Collection\Collection($nodes);
}
}
