<?php
/**
* CakePHP(tm) : Rapid Development Framework (https://cakephp.org)
* Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
*
* Licensed under The MIT License
* For full copyright and license information, please see the LICENSE.txt
* Redistributions of files must retain the above copyright notice.
*
* @copyright Copyright (c) Cake Software Foundation, Inc. (https://cakefoundation.org)
* @link https://cakephp.org CakePHP(tm) Project
* @since 3.0.0
* @license https://opensource.org/licenses/mit-license.php MIT License
*/
namespace Cake\Database;
use Cake\Database\Expression\IdentifierExpression;
use Cake\Database\Expression\OrderByExpression;
use Cake\Database\Expression\OrderClauseExpression;
use Cake\Database\Expression\QueryExpression;
use Cake\Database\Expression\ValuesExpression;
use Cake\Database\Statement\CallbackStatement;
use Cake\Datasource\QueryInterface;
use InvalidArgumentException;
use IteratorAggregate;
use RuntimeException;
/**
* This class represents a Relational database SQL Query. A query can be of
* different types like select, update, insert and delete. Exposes the methods
* for dynamically constructing each query part, execute it and transform it
* to a specific SQL dialect.
*/
class Query implements ExpressionInterface, IteratorAggregate
{
use TypeMapTrait;
/**
* Connection instance to be used to execute this query.
*
* @var \Cake\Database\Connection
*/
protected $_connection;
/**
* Type of this query (select, insert, update, delete).
*
* @var string
*/
protected $_type = 'select';
/**
* List of SQL parts that will be used to build this query.
*
* @var array
*/
protected $_parts = [
'delete' => true,
'update' => [],
'set' => [],
'insert' => [],
'values' => [],
'select' => [],
'distinct' => false,
'modifier' => [],
'from' => [],
'join' => [],
'where' => null,
'group' => [],
'having' => null,
'order' => null,
'limit' => null,
'offset' => null,
'union' => [],
'epilog' => null,
];
/**
* Indicates whether internal state of this query was changed, this is used to
* discard internal cached objects such as the transformed query or the reference
* to the executed statement.
*
* @var bool
*/
protected $_dirty = false;
/**
* A list of callback functions to be called to alter each row from resulting
* statement upon retrieval. Each one of the callback function will receive
* the row array as first argument.
*
* @var array
*/
protected $_resultDecorators = [];
/**
* Statement object resulting from executing this query.
*
* @var \Cake\Database\StatementInterface|null
*/
protected $_iterator;
/**
* The object responsible for generating query placeholders and temporarily store values
* associated to each of those.
*
* @var \Cake\Database\ValueBinder|null
*/
protected $_valueBinder;
/**
* Instance of functions builder object used for generating arbitrary SQL functions.
*
* @var \Cake\Database\FunctionsBuilder|null
*/
protected $_functionsBuilder;
/**
* Boolean for tracking whether or not buffered results
* are enabled.
*
* @var bool
*/
protected $_useBufferedResults = true;
/**
* The Type map for fields in the select clause
*
* @var \Cake\Database\TypeMap
*/
protected $_selectTypeMap;
/**
* Tracking flag to disable casting
*
* @var bool
*/
protected $typeCastEnabled = true;
/**
* Constructor.
*
* @param \Cake\Database\Connection $connection The connection
* object to be used for transforming and executing this query
*/
public function __construct($connection)
{
$this->setConnection($connection);
}
/**
* Sets the connection instance to be used for executing and transforming this query.
*
* @param \Cake\Database\Connection $connection Connection instance
* @return $this
*/
public function setConnection($connection)
{
$this->_dirty();
$this->_connection = $connection;
return $this;
}
/**
* Gets the connection instance to be used for executing and transforming this query.
*
* @return \Cake\Database\Connection
*/
public function getConnection()
{
return $this->_connection;
}
/**
* Sets the connection instance to be used for executing and transforming this query
* When called with a null argument, it will return the current connection instance.
*
* @deprecated 3.4.0 Use setConnection()/getConnection() instead.
* @param \Cake\Database\Connection|null $connection Connection instance
* @return $this|\Cake\Database\Connection
*/
public function connection($connection = null)
{
deprecationWarning(
'Query::connection() is deprecated. ' .
'Use Query::setConnection()/getConnection() instead.'
);
if ($connection !== null) {
return $this->setConnection($connection);
}
return $this->getConnection();
}
/**
* Compiles the SQL representation of this query and executes it using the
* configured connection object. Returns the resulting statement object.
*
* Executing a query internally executes several steps, the first one is
* letting the connection transform this object to fit its particular dialect,
* this might result in generating a different Query object that will be the one
* to actually be executed. Immediately after, literal values are passed to the
* connection so they are bound to the query in a safe way. Finally, the resulting
* statement is decorated with custom objects to execute callbacks for each row
* retrieved if necessary.
*
* Resulting statement is traversable, so it can be used in any loop as you would
* with an array.
*
* This method can be overridden in query subclasses to decorate behavior
* around query execution.
*
* @return \Cake\Database\StatementInterface
*/
public function execute()
{
$statement = $this->_connection->run($this);
$this->_iterator = $this->_decorateStatement($statement);
$this->_dirty = false;
return $this->_iterator;
}
/**
* Executes the SQL of this query and immediately closes the statement before returning the row count of records
* changed.
*
* This method can be used with UPDATE and DELETE queries, but is not recommended for SELECT queries and is not
* used to count records.
*
* ## Example
*
* ```
* $rowCount = $query->update('articles')
* ->set(['published'=>true])
* ->where(['published'=>false])
* ->rowCountAndClose();
* ```
*
* The above example will change the published column to true for all false records, and return the number of
* records that were updated.
*
* @return int
*/
public function rowCountAndClose()
{
$statement = $this->execute();
try {
return $statement->rowCount();
} finally {
$statement->closeCursor();
}
}
/**
* Returns the SQL representation of this object.
*
* This function will compile this query to make it compatible
* with the SQL dialect that is used by the connection, This process might
* add, remove or alter any query part or internal expression to make it
* executable in the target platform.
*
* The resulting query may have placeholders that will be replaced with the actual
* values when the query is executed, hence it is most suitable to use with
* prepared statements.
*
* @param \Cake\Database\ValueBinder|null $generator A placeholder object that will hold
* associated values for expressions
* @return string
*/
public function sql(ValueBinder $generator = null)
{
if (!$generator) {
$generator = $this->getValueBinder();
$generator->resetCount();
}
return $this->getConnection()->compileQuery($this, $generator);
}
/**
* Will iterate over every specified part. Traversing functions can aggregate
* results using variables in the closure or instance variables. This function
* is commonly used as a way for traversing all query parts that
* are going to be used for constructing a query.
*
* The callback will receive 2 parameters, the first one is the value of the query
* part that is being iterated and the second the name of such part.
*
* ### Example:
* ```
* $query->select(['title'])->from('articles')->traverse(function ($value, $clause) {
* if ($clause === 'select') {
* var_dump($value);
* }
* }, ['select', 'from']);
* ```
*
* @param callable $visitor A function or callable to be executed for each part
* @param string[] $parts The query clauses to traverse
* @return $this
*/
public function traverse(callable $visitor, array $parts = [])
{
$parts = $parts ?: array_keys($this->_parts);
foreach ($parts as $name) {
$visitor($this->_parts[$name], $name);
}
return $this;
}
/**
* Adds new fields to be returned by a `SELECT` statement when this query is
* executed. Fields can be passed as an array of strings, array of expression
* objects, a single expression or a single string.
*
* If an array is passed, keys will be used to alias fields using the value as the
* real field to be aliased. It is possible to alias strings, Expression objects or
* even other Query objects.
*
* If a callable function is passed, the returning array of the function will
* be used as the list of fields.
*
* By default this function will append any passed argument to the list of fields
* to be selected, unless the second argument is set to true.
*
* ### Examples:
*
* ```
* $query->select(['id', 'title']); // Produces SELECT id, title
* $query->select(['author' => 'author_id']); // Appends author: SELECT id, title, author_id as author
* $query->select('id', true); // Resets the list: SELECT id
* $query->select(['total' => $countQuery]); // SELECT id, (SELECT ...) AS total
* $query->select(function ($query) {
* return ['article_id', 'total' => $query->count('*')];
* })
* ```
*
* By default no fields are selected, if you have an instance of `Cake\ORM\Query` and try to append
* fields you should also call `Cake\ORM\Query::enableAutoFields()` to select the default fields
* from the table.
*
* @param array|\Cake\Database\ExpressionInterface|string|callable $fields fields to be added to the list.
* @param bool $overwrite whether to reset fields with passed list or not
* @return $this
*/
public function select($fields = [], $overwrite = false)
{
if (!is_string($fields) && is_callable($fields)) {
$fields = $fields($this);
}
if (!is_array($fields)) {
$fields = [$fields];
}
if ($overwrite) {
$this->_parts['select'] = $fields;
} else {
$this->_parts['select'] = array_merge($this->_parts['select'], $fields);
}
$this->_dirty();
$this->_type = 'select';
return $this;
}
/**
* Adds a `DISTINCT` clause to the query to remove duplicates from the result set.
* This clause can only be used for select statements.
*
* If you wish to filter duplicates based of those rows sharing a particular field
* or set of fields, you may pass an array of fields to filter on. Beware that
* this option might not be fully supported in all database systems.
*
* ### Examples:
*
* ```
* // Filters products with the same name and city
* $query->select(['name', 'city'])->from('products')->distinct();
*
* // Filters products in the same city
* $query->distinct(['city']);
* $query->distinct('city');
*
* // Filter products with the same name
* $query->distinct(['name'], true);
* $query->distinct('name', true);
* ```
*
* @param array|\Cake\Database\ExpressionInterface|string|bool $on Enable/disable distinct class
* or list of fields to be filtered on
* @param bool $overwrite whether to reset fields with passed list or not
* @return $this
*/
public function distinct($on = [], $overwrite = false)
{
if ($on === []) {
$on = true;
} elseif (is_string($on)) {
$on = [$on];
}
if (is_array($on)) {
$merge = [];
if (is_array($this->_parts['distinct'])) {
$merge = $this->_parts['distinct'];
}
$on = $overwrite ? array_values($on) : array_merge($merge, array_values($on));
}
$this->_parts['distinct'] = $on;
$this->_dirty();
return $this;
}
/**
* Adds a single or multiple `SELECT` modifiers to be used in the `SELECT`.
*
* By default this function will append any passed argument to the list of modifiers
* to be applied, unless the second argument is set to true.
*
* ### Example:
*
* ```
* // Ignore cache query in MySQL
* $query->select(['name', 'city'])->from('products')->modifier('SQL_NO_CACHE');
* // It will produce the SQL: SELECT SQL_NO_CACHE name, city FROM products
*
* // Or with multiple modifiers
* $query->select(['name', 'city'])->from('products')->modifier(['HIGH_PRIORITY', 'SQL_NO_CACHE']);
* // It will produce the SQL: SELECT HIGH_PRIORITY SQL_NO_CACHE name, city FROM products
* ```
*
* @param array|\Cake\Database\ExpressionInterface|string $modifiers modifiers to be applied to the query
* @param bool $overwrite whether to reset order with field list or not
* @return $this
*/
public function modifier($modifiers, $overwrite = false)
{
$this->_dirty();
if ($overwrite) {
$this->_parts['modifier'] = [];
}
$this->_parts['modifier'] = array_merge($this->_parts['modifier'], (array)$modifiers);
return $this;
}
/**
* Adds a single or multiple tables to be used in the FROM clause for this query.
* Tables can be passed as an array of strings, array of expression
* objects, a single expression or a single string.
*
* If an array is passed, keys will be used to alias tables using the value as the
* real field to be aliased. It is possible to alias strings, ExpressionInterface objects or
* even other Query objects.
*
* By default this function will append any passed argument to the list of tables
* to be selected from, unless the second argument is set to true.
*
* This method can be used for select, update and delete statements.
*
* ### Examples:
*
* ```
* $query->from(['p' => 'posts']); // Produces FROM posts p
* $query->from('authors'); // Appends authors: FROM posts p, authors
* $query->from(['products'], true); // Resets the list: FROM products
* $query->from(['sub' => $countQuery]); // FROM (SELECT ...) sub
* ```
*
* @param array|string $tables tables to be added to the list. This argument, can be
* passed as an array of strings, array of expression objects, or a single string. See
* the examples above for the valid call types.
* @param bool $overwrite whether to reset tables with passed list or not
* @return $this|array
*/
public function from($tables = [], $overwrite = false)
{
if (empty($tables)) {
deprecationWarning('Using Query::from() to read state is deprecated. Use clause("from") instead.');
return $this->_parts['from'];
}
$tables = (array)$tables;
if ($overwrite) {
$this->_parts['from'] = $tables;
} else {
$this->_parts['from'] = array_merge($this->_parts['from'], $tables);
}
$this->_dirty();
return $this;
}
/**
* Adds a single or multiple tables to be used as JOIN clauses to this query.
* Tables can be passed as an array of strings, an array describing the
* join parts, an array with multiple join descriptions, or a single string.
*
* By default this function will append any passed argument to the list of tables
* to be joined, unless the third argument is set to true.
*
* When no join type is specified an `INNER JOIN` is used by default:
* `$query->join(['authors'])` will produce `INNER JOIN authors ON 1 = 1`
*
* It is also possible to alias joins using the array key:
* `$query->join(['a' => 'authors'])` will produce `INNER JOIN authors a ON 1 = 1`
*
* A join can be fully described and aliased using the array notation:
*
* ```
* $query->join([
* 'a' => [
* 'table' => 'authors',
* 'type' => 'LEFT',
* 'conditions' => 'a.id = b.author_id'
* ]
* ]);
* // Produces LEFT JOIN authors a ON a.id = b.author_id
* ```
*
* You can even specify multiple joins in an array, including the full description:
*
* ```
* $query->join([
* 'a' => [
* 'table' => 'authors',
* 'type' => 'LEFT',
* 'conditions' => 'a.id = b.author_id'
* ],
* 'p' => [
* 'table' => 'publishers',
* 'type' => 'INNER',
* 'conditions' => 'p.id = b.publisher_id AND p.name = "Cake Software Foundation"'
* ]
* ]);
* // LEFT JOIN authors a ON a.id = b.author_id
* // INNER JOIN publishers p ON p.id = b.publisher_id AND p.name = "Cake Software Foundation"
* ```
*
* ### Using conditions and types
*
* Conditions can be expressed, as in the examples above, using a string for comparing
* columns, or string with already quoted literal values. Additionally it is
* possible to use conditions expressed in arrays or expression objects.
*
* When using arrays for expressing conditions, it is often desirable to convert
* the literal values to the correct database representation. This is achieved
* using the second parameter of this function.
*
* ```
* $query->join(['a' => [
* 'table' => 'articles',
* 'conditions' => [
* 'a.posted >=' => new DateTime('-3 days'),
* 'a.published' => true,
* 'a.author_id = authors.id'
* ]
* ]], ['a.posted' => 'datetime', 'a.published' => 'boolean'])
* ```
*
* ### Overwriting joins
*
* When creating aliased joins using the array notation, you can override
* previous join definitions by using the same alias in consequent
* calls to this function or you can replace all previously defined joins
* with another list if the third parameter for this function is set to true.
*
* ```
* $query->join(['alias' => 'table']); // joins table with as alias
* $query->join(['alias' => 'another_table']); // joins another_table with as alias
* $query->join(['something' => 'different_table'], [], true); // resets joins list
* ```
*
* @param array|string|null $tables list of tables to be joined in the query
* @param array $types associative array of type names used to bind values to query
* @param bool $overwrite whether to reset joins with passed list or not
* @see \Cake\Database\Type
* @return $this|array
*/
public function join($tables = null, $types = [], $overwrite = false)
{
if ($tables === null) {
deprecationWarning('Using Query::join() to read state is deprecated. Use Query::clause("join") instead.');
return $this->_parts['join'];
}
if (is_string($tables) || isset($tables['table'])) {
$tables = [$tables];
}
$joins = [];
$i = count($this->_parts['join']);
foreach ($tables as $alias => $t) {
if (!is_array($t)) {
$t = ['table' => $t, 'conditions' => $this->newExpr()];
}
if (!is_string($t['conditions']) && is_callable($t['conditions'])) {
$t['conditions'] = $t['conditions']($this->newExpr(), $this);
}
if (!($t['conditions'] instanceof ExpressionInterface)) {
$t['conditions'] = $this->newExpr()->add($t['conditions'], $types);
}
$alias = is_string($alias) ? $alias : null;
$joins[$alias ?: $i++] = $t + ['type' => QueryInterface::JOIN_TYPE_INNER, 'alias' => $alias];
}
if ($overwrite) {
$this->_parts['join'] = $joins;
} else {
$this->_parts['join'] = array_merge($this->_parts['join'], $joins);
}
$this->_dirty();
return $this;
}
/**
* Remove a join if it has been defined.
*
* Useful when you are redefining joins or want to re-order
* the join clauses.
*
* @param string $name The alias/name of the join to remove.
* @return $this
*/
public function removeJoin($name)
{
unset($this->_parts['join'][$name]);
$this->_dirty();
return $this;
}
/**
* Adds a single `LEFT JOIN` clause to the query.
*
* This is a shorthand method for building joins via `join()`.
*
* The table name can be passed as a string, or as an array in case it needs to
* be aliased:
*
* ```
* // LEFT JOIN authors ON authors.id = posts.author_id
* $query->leftJoin('authors', 'authors.id = posts.author_id');
*
* // LEFT JOIN authors a ON a.id = posts.author_id
* $query->leftJoin(['a' => 'authors'], 'a.id = posts.author_id');
* ```
*
* Conditions can be passed as strings, arrays, or expression objects. When
* using arrays it is possible to combine them with the `$types` parameter
* in order to define how to convert the values:
*
* ```
* $query->leftJoin(['a' => 'articles'], [
* 'a.posted >=' => new DateTime('-3 days'),
* 'a.published' => true,
* 'a.author_id = authors.id'
* ], ['a.posted' => 'datetime', 'a.published' => 'boolean']);
* ```
*
* See `join()` for further details on conditions and types.
*
* @param string|string[] $table The table to join with
* @param string|array|\Cake\Database\ExpressionInterface $conditions The conditions
* to use for joining.
* @param array $types a list of types associated to the conditions used for converting
* values to the corresponding database representation.
* @return $this
*/
public function leftJoin($table, $conditions = [], $types = [])
{
return $this->join($this->_makeJoin($table, $conditions, QueryInterface::JOIN_TYPE_LEFT), $types);
}
/**
* Adds a single `RIGHT JOIN` clause to the query.
*
* This is a shorthand method for building joins via `join()`.
*
* The arguments of this method are identical to the `leftJoin()` shorthand, please refer
* to that methods description for further details.
*
* @param string|array $table The table to join with
* @param string|array|\Cake\Database\ExpressionInterface $conditions The conditions
* to use for joining.
* @param array $types a list of types associated to the conditions used for converting
* values to the corresponding database representation.
* @return $this
*/
public function rightJoin($table, $conditions = [], $types = [])
{
return $this->join($this->_makeJoin($table, $conditions, QueryInterface::JOIN_TYPE_RIGHT), $types);
}
/**
* Adds a single `INNER JOIN` clause to the query.
*
* This is a shorthand method for building joins via `join()`.
*
* The arguments of this method are identical to the `leftJoin()` shorthand, please refer
* to that methods description for further details.
*
* @param string|string[] $table The table to join with
* @param string|array|\Cake\Database\ExpressionInterface $conditions The conditions
* to use for joining.
* @param array $types a list of types associated to the conditions used for converting
* values to the corresponding database representation.
* @return $this
*/
public function innerJoin($table, $conditions = [], $types = [])
{
return $this->join($this->_makeJoin($table, $conditions, QueryInterface::JOIN_TYPE_INNER), $types);
}
/**
* Returns an array that can be passed to the join method describing a single join clause
*
* @param string|string[] $table The table to join with
* @param string|array|\Cake\Database\ExpressionInterface $conditions The conditions
* to use for joining.
* @param string $type the join type to use
* @return array
*/
protected function _makeJoin($table, $conditions, $type)
{
$alias = $table;
if (is_array($table)) {
$alias = key($table);
$table = current($table);
}
return [
$alias => [
'table' => $table,
'conditions' => $conditions,
'type' => $type,
],
];
}
/**
* Adds a condition or set of conditions to be used in the WHERE clause for this
* query. Conditions can be expressed as an array of fields as keys with
* comparison operators in it, the values for the array will be used for comparing
* the field to such literal. Finally, conditions can be expressed as a single
* string or an array of strings.
*
* 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]])`
*
* Would result in:
*
* `WHERE (published = false) OR (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.
* The keys however, are not treated as unsafe input, and should be sanitized/whitelisted.
*
* 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.
*
* @param string|array|\Cake\Database\ExpressionInterface|callable|null $conditions The conditions to filter on.
* @param array $types associative array of type names used to bind values to query
* @param bool $overwrite whether to reset conditions with passed list or not
* @see \Cake\Database\Type
* @see \Cake\Database\Expression\QueryExpression
* @return $this
*/
public function where($conditions = null, $types = [], $overwrite = false)
{
if ($overwrite) {
$this->_parts['where'] = $this->newExpr();
}
$this->_conjugate('where', $conditions, 'AND', $types);
return $this;
}
/**
* Convenience method that adds a NOT NULL condition to the query
*
* @param array|string|\Cake\Database\ExpressionInterface $fields A single field or expressions or a list of them that should be not null
* @return $this
*/
public function whereNotNull($fields)
{
if (!is_array($fields)) {
$fields = [$fields];
}
$exp = $this->newExpr();
foreach ($fields as $field) {
$exp->isNotNull($field);
}
return $this->where($exp);
}
/**
* Convenience method that adds a IS NULL condition to the query
*
* @param array|string|\Cake\Database\ExpressionInterface $fields A single field or expressions or a list of them that should be null
* @return $this
*/
public function whereNull($fields)
{
if (!is_array($fields)) {
$fields = [$fields];
}
$exp = $this->newExpr();
foreach ($fields as $field) {
$exp->isNull($field);
}
return $this->where($exp);
}
/**
* Adds an IN condition or set of conditions to be used in the WHERE clause for this
* query.
*
* This method does allow empty inputs in contrast to where() if you set
* 'allowEmpty' to true.
* Be careful about using it without proper sanity checks.
*
* Options:
* - `types` - Associative array of type names used to bind values to query
* - `allowEmpty` - Allow empty array.
*
* @param string $field Field
* @param array $values Array of values
* @param array $options Options
* @return $this
*/
public function whereInList($field, array $values, array $options = [])
{
$options += [
'types' => [],
'allowEmpty' => false,
];
if ($options['allowEmpty'] && !$values) {
return $this->where('1=0');
}
return $this->where([$field . ' IN' => $values], $options['types']);
}
/**
* Adds a NOT IN condition or set of conditions to be used in the WHERE clause for this
* query.
*
* This method does allow empty inputs in contrast to where() if you set
* 'allowEmpty' to true.
* Be careful about using it without proper sanity checks.
*
* @param string $field Field
* @param array $values Array of values
* @param array $options Options
* @return $this
*/
public function whereNotInList($field, array $values, array $options = [])
{
$options += [
'types' => [],
'allowEmpty' => false,
];
if ($options['allowEmpty'] && !$values) {
return $this->where([$field . ' IS NOT' => null]);
}
return $this->where([$field . ' NOT IN' => $values], $options['types']);
}
/**
* Connects any previously defined set of conditions to the provided list
* using the AND operator. This function accepts the conditions list in the same
* format as the method `where` does, hence you can use arrays, expression objects
* callback functions or strings.
*
* It is important to notice that when calling this function, any previous set
* of conditions defined for this query will be treated as a single argument for
* the AND operator. This function will not only operate the most recently defined
* condition, but all the conditions as a whole.
*
* When using an array for defining conditions, creating constraints form each
* array entry will use the same logic as with the `where()` function. This means
* that each array entry will be joined to the other using the AND operator, unless
* you nest the conditions in the array using other operator.
*
* ### Examples:
*
* ```
* $query->where(['title' => 'Hello World')->andWhere(['author_id' => 1]);
* ```
*
* Will produce:
*
* `WHERE title = 'Hello World' AND author_id = 1`
*
* ```
* $query
* ->where(['OR' => ['published' => false, 'published is NULL']])
* ->andWhere(['author_id' => 1, 'comments_count >' => 10])
* ```
*
* Produces:
*
* `WHERE (published = 0 OR published IS NULL) AND author_id = 1 AND comments_count > 10`
*
* ```
* $query
* ->where(['title' => 'Foo'])
* ->andWhere(function ($exp, $query) {
* return $exp
* ->or(['author_id' => 1])
* ->add(['author_id' => 2]);
* });
* ```
*
* Generates the following conditions:
*
* `WHERE (title = 'Foo') AND (author_id = 1 OR author_id = 2)`
*
* @param string|array|\Cake\Database\ExpressionInterface|callable $conditions The conditions to add with AND.
* @param array $types associative array of type names used to bind values to query
* @see \Cake\Database\Query::where()
* @see \Cake\Database\Type
* @return $this
*/
public function andWhere($conditions, $types = [])
{
$this->_conjugate('where', $conditions, 'AND', $types);
return $this;
}
/**
* Connects any previously defined set of conditions to the provided list
* using the OR operator. This function accepts the conditions list in the same
* format as the method `where` does, hence you can use arrays, expression objects
* callback functions or strings.
*
* It is important to notice that when calling this function, any previous set
* of conditions defined for this query will be treated as a single argument for
* the OR operator. This function will not only operate the most recently defined
* condition, but all the conditions as a whole.
*
* When using an array for defining conditions, creating constraints form each
* array entry will use the same logic as with the `where()` function. This means
* that each array entry will be joined to the other using the OR operator, unless
* you nest the conditions in the array using other operator.
*
* ### Examples:
*
* ```
* $query->where(['title' => 'Hello World')->orWhere(['title' => 'Foo']);
* ```
*
* Will produce:
*
* `WHERE title = 'Hello World' OR title = 'Foo'`
*
* ```
* $query
* ->where(['OR' => ['published' => false, 'published is NULL']])
* ->orWhere(['author_id' => 1, 'comments_count >' => 10])
* ```
*
* Produces:
*
* `WHERE (published = 0 OR published IS NULL) OR (author_id = 1 AND comments_count > 10)`
*
* ```
* $query
* ->where(['title' => 'Foo'])
* ->orWhere(function ($exp, $query) {
* return $exp
* ->or(['author_id' => 1])
* ->add(['author_id' => 2]);
* });
* ```
*
* Generates the following conditions:
*
* `WHERE (title = 'Foo') OR (author_id = 1 OR author_id = 2)`
*
* @param string|array|\Cake\Database\ExpressionInterface|callable $conditions The conditions to add with OR.
* @param array $types associative array of type names used to bind values to query
* @see \Cake\Database\Query::where()
* @see \Cake\Database\Type
* @return $this
* @deprecated 3.5.0 This method creates hard to predict SQL based on the current query state.
* Use `Query::where()` instead as it has more predicatable and easier to understand behavior.
*/
public function orWhere($conditions, $types = [])
{
deprecationWarning(
'Query::orWhere() is deprecated as it creates hard to predict SQL based on the ' .
'current query state. Use `Query::where()` instead.'
);
$this->_conjugate('where', $conditions, 'OR', $types);
return $this;
}
/**
* Adds a single or multiple fields to be used in the ORDER clause for this query.
* Fields can be passed as an array of strings, array of expression
* objects, a single expression or a single string.
*
* If an array is passed, keys will be used as the field itself and the value will
* represent the order in which such field should be ordered. When called multiple
* times with the same fields as key, the last order definition will prevail over
* the others.
*
* By default this function will append any passed argument to the list of fields
* to be selected, unless the second argument is set to true.
*
* ### Examples:
*
* ```
* $query->order(['title' => 'DESC', 'author_id' => 'ASC']);
* ```
*
* Produces:
*
* `ORDER BY title DESC, author_id ASC`
*
* ```
* $query->order(['title' => 'DESC NULLS FIRST'])->order('author_id');
* ```
*
* Will generate:
*
* `ORDER BY title DESC NULLS FIRST, author_id`
*
* ```
* $expression = $query->newExpr()->add(['id % 2 = 0']);
* $query->order($expression)->order(['title' => 'ASC']);
* ```
*
* and
*
* ```
* $query->order(function ($exp, $query) {
* return [$exp->add(['id % 2 = 0']), 'title' => 'ASC'];
* });
* ```
*
* Will both become:
*
* `ORDER BY (id %2 = 0), title ASC`
*
* Order fields/directions are not sanitized by the query builder.
* You should use a whitelist of fields/directions when passing
* in user-supplied data to `order()`.
*
* If you need to set complex expressions as order conditions, you
* should use `orderAsc()` or `orderDesc()`.
*
* @param array|\Cake\Database\ExpressionInterface|callable|string $fields fields to be added to the list
* @param bool $overwrite whether to reset order with field list or not
* @return $this
*/
public function order($fields, $overwrite = false)
{
if ($overwrite) {
$this->_parts['order'] = null;
}
if (!$fields) {
return $this;
}
if (!$this->_parts['order']) {
$this->_parts['order'] = new OrderByExpression();
}
$this->_conjugate('order', $fields, '', []);
return $this;
}
/**
* Add an ORDER BY clause with an ASC direction.
*
* This method allows you to set complex expressions
* as order conditions unlike order()
*
* Order fields are not suitable for use with user supplied data as they are
* not sanitized by the query builder.
*
* @param string|\Cake\Database\Expression\QueryExpression $field The field to order on.
* @param bool $overwrite Whether or not to reset the order clauses.
* @return $this
*/
public function orderAsc($field, $overwrite = false)
{
if ($overwrite) {
$this->_parts['order'] = null;
}
if (!$field) {
return $this;
}
if (!$this->_parts['order']) {
$this->_parts['order'] = new OrderByExpression();
}
$this->_parts['order']->add(new OrderClauseExpression($field, 'ASC'));
return $this;
}
/**
* Add an ORDER BY clause with a DESC direction.
*
* This method allows you to set complex expressions
* as order conditions unlike order()
*
* Order fields are not suitable for use with user supplied data as they are
* not sanitized by the query builder.
*
* @param string|\Cake\Database\Expression\QueryExpression $field The field to order on.
* @param bool $overwrite Whether or not to reset the order clauses.
* @return $this
*/
public function orderDesc($field, $overwrite = false)
{
if ($overwrite) {
$this->_parts['order'] = null;
}
if (!$field) {
return $this;
}
if (!$this->_parts['order']) {
$this->_parts['order'] = new OrderByExpression();
}
$this->_parts['order']->add(new OrderClauseExpression($field, 'DESC'));
return $this;
}
/**
* Adds a single or multiple fields to be used in the GROUP BY clause for this query.
* Fields can be passed as an array of strings, array of expression
* objects, a single expression or a single string.
*
* By default this function will append any passed argument to the list of fields
* to be grouped, unless the second argument is set to true.
*
* ### Examples:
*
* ```
* // Produces GROUP BY id, title
* $query->group(['id', 'title']);
*
* // Produces GROUP BY title
* $query->group('title');
* ```
*
* Group fields are not suitable for use with user supplied data as they are
* not sanitized by the query builder.
*
* @param array|\Cake\Database\ExpressionInterface|string $fields fields to be added to the list
* @param bool $overwrite whether to reset fields with passed list or not
* @return $this
*/
public function group($fields, $overwrite = false)
{
if ($overwrite) {
$this->_parts['group'] = [];
}
if (!is_array($fields)) {
$fields = [$fields];
}
$this->_parts['group'] = array_merge($this->_parts['group'], array_values($fields));
$this->_dirty();
return $this;
}
/**
* Adds a condition or set of conditions to be used in the `HAVING` clause for this
* query. This method operates in exactly the same way as the method `where()`
* does. Please refer to its documentation for an insight on how to using each
* parameter.
*
* Having fields are not suitable for use with user supplied data as they are
* not sanitized by the query builder.
*
* @param string|array|\Cake\Database\ExpressionInterface|callable|null $conditions The having conditions.
* @param array $types associative array of type names used to bind values to query
* @param bool $overwrite whether to reset conditions with passed list or not
* @see \Cake\Database\Query::where()
* @return $this
*/
public function having($conditions = null, $types = [], $overwrite = false)
{
if ($overwrite) {
$this->_parts['having'] = $this->newExpr();
}
$this->_conjugate('having', $conditions, 'AND', $types);
return $this;
}
/**
* Connects any previously defined set of conditions to the provided list
* using the AND operator in the HAVING clause. This method operates in exactly
* the same way as the method `andWhere()` does. Please refer to its
* documentation for an insight on how to using each parameter.
*
* Having fields are not suitable for use with user supplied data as they are
* not sanitized by the query builder.
*
* @param string|array|\Cake\Database\ExpressionInterface|callable $conditions The AND conditions for HAVING.
* @param array $types associative array of type names used to bind values to query
* @see \Cake\Database\Query::andWhere()
* @return $this
*/
public function andHaving($conditions, $types = [])
{
$this->_conjugate('having', $conditions, 'AND', $types);
return $this;
}
/**
* Connects any previously defined set of conditions to the provided list
* using the OR operator in the HAVING clause. This method operates in exactly
* the same way as the method `orWhere()` does. Please refer to its
* documentation for an insight on how to using each parameter.
*
* Having fields are not suitable for use with user supplied data as they are
* not sanitized by the query builder.
*
* @param string|array|\Cake\Database\ExpressionInterface|callable $conditions The OR conditions for HAVING.
* @param array $types associative array of type names used to bind values to query.
* @see \Cake\Database\Query::orWhere()
* @return $this
* @deprecated 3.5.0 This method creates hard to predict SQL based on the current query state.
* Use `Query::having()` instead as it has more predicatable and easier to understand behavior.
*/
public function orHaving($conditions, $types = [])
{
deprecationWarning('Query::orHaving() is deprecated. Use Query::having() instead.');
$this->_conjugate('having', $conditions, 'OR', $types);
return $this;
}
/**
* Set the page of results you want.
*
* This method provides an easier to use interface to set the limit + offset
* in the record set you want as results. If empty the limit will default to
* the existing limit clause, and if that too is empty, then `25` will be used.
*
* Pages must start at 1.
*
* @param int $num The page number you want.
* @param int|null $limit The number of rows you want in the page. If null
* the current limit clause will be used.
* @return $this
* @throws \InvalidArgumentException If page number < 1.
*/
public function page($num, $limit = null)
{
if ($num < 1) {
throw new InvalidArgumentException('Pages must start at 1.');
}
if ($limit !== null) {
$this->limit($limit);
}
$limit = $this->clause('limit');
if ($limit === null) {
$limit = 25;
$this->limit($limit);
}
$offset = ($num - 1) * $limit;
if (PHP_INT_MAX <= $offset) {
$offset = PHP_INT_MAX;
}
$this->offset((int)$offset);
return $this;
}
/**
* Sets the number of records that should be retrieved from database,
* accepts an integer or an expression object that evaluates to an integer.
* In some databases, this operation might not be supported or will require
* the query to be transformed in order to limit the result set size.
*
* ### Examples
*
* ```
* $query->limit(10) // generates LIMIT 10
* $query->limit($query->newExpr()->add(['1 + 1'])); // LIMIT (1 + 1)
* ```
*
* @param int|\Cake\Database\ExpressionInterface|null $num number of records to be returned
* @return $this
*/
public function limit($num)
{
$this->_dirty();
if ($num !== null && !is_object($num)) {
$num = (int)$num;
}
$this->_parts['limit'] = $num;
return $this;
}
/**
* Sets the number of records that should be skipped from the original result set
* This is commonly used for paginating large results. Accepts an integer or an
* expression object that evaluates to an integer.
*
* In some databases, this operation might not be supported or will require
* the query to be transformed in order to limit the result set size.
*
* ### Examples
*
* ```
* $query->offset(10) // generates OFFSET 10
* $query->offset($query->newExpr()->add(['1 + 1'])); // OFFSET (1 + 1)
* ```
*
* @param int|\Cake\Database\ExpressionInterface|null $num number of records to be skipped
* @return $this
*/
public function offset($num)
{
$this->_dirty();
if ($num !== null && !is_object($num)) {
$num = (int)$num;
}
$this->_parts['offset'] = $num;
return $this;
}
/**
* Adds a complete query to be used in conjunction with an UNION operator with
* this query. This is used to combine the result set of this query with the one
* that will be returned by the passed query. You can add as many queries as you
* required by calling multiple times this method with different queries.
*
* By default, the UNION operator will remove duplicate rows, if you wish to include
* every row for all queries, use unionAll().
*
* ### Examples
*
* ```
* $union = (new Query($conn))->select(['id', 'title'])->from(['a' => 'articles']);
* $query->select(['id', 'name'])->from(['d' => 'things'])->union($union);
* ```
*
* Will produce:
*
* `SELECT id, name FROM things d UNION SELECT id, title FROM articles a`
*
* @param string|\Cake\Database\Query $query full SQL query to be used in UNION operator
* @param bool $overwrite whether to reset the list of queries to be operated or not
* @return $this
*/
public function union($query, $overwrite = false)
{
if ($overwrite) {
$this->_parts['union'] = [];
}
$this->_parts['union'][] = [
'all' => false,
'query' => $query,
];
$this->_dirty();
return $this;
}
/**
* Adds a complete query to be used in conjunction with the UNION ALL operator with
* this query. This is used to combine the result set of this query with the one
* that will be returned by the passed query. You can add as many queries as you
* required by calling multiple times this method with different queries.
*
* Unlike UNION, UNION ALL will not remove duplicate rows.
*
* ```
* $union = (new Query($conn))->select(['id', 'title'])->from(['a' => 'articles']);
* $query->select(['id', 'name'])->from(['d' => 'things'])->unionAll($union);
* ```
*
* Will produce:
*
* `SELECT id, name FROM things d UNION ALL SELECT id, title FROM articles a`
*
* @param string|\Cake\Database\Query $query full SQL query to be used in UNION operator
* @param bool $overwrite whether to reset the list of queries to be operated or not
* @return $this
*/
public function unionAll($query, $overwrite = false)
{
if ($overwrite) {
$this->_parts['union'] = [];
}
$this->_parts['union'][] = [
'all' => true,
'query' => $query,
];
$this->_dirty();
return $this;
}
/**
* Create an insert query.
*
* Note calling this method will reset any data previously set
* with Query::values().
*
* @param array $columns The columns to insert into.
* @param string[] $types A map between columns & their datatypes.
* @return $this
* @throws \RuntimeException When there are 0 columns.
*/
public function insert(array $columns, array $types = [])
{
if (empty($columns)) {
throw new RuntimeException('At least 1 column is required to perform an insert.');
}
$this->_dirty();
$this->_type = 'insert';
$this->_parts['insert'][1] = $columns;
if (!$this->_parts['values']) {
$this->_parts['values'] = new ValuesExpression($columns, $this->getTypeMap()->setTypes($types));
} else {
$this->_parts['values']->setColumns($columns);
}
return $this;
}
/**
* Set the table name for insert queries.
*
* @param string $table The table name to insert into.
* @return $this
*/
public function into($table)
{
$this->_dirty();
$this->_type = 'insert';
$this->_parts['insert'][0] = $table;
return $this;
}
/**
* Creates an expression that refers to an identifier. Identifiers are used to refer to field names and allow
* the SQL compiler to apply quotes or escape the identifier.
*
* The value is used as is, and you might be required to use aliases or include the table reference in
* the identifier. Do not use this method to inject SQL methods or logical statements.
*
* ### Example
*
* ```
* $query->newExpr()->lte('count', $query->identifier('total'));
* ```
*
* @param string $identifier The identifier for an expression
* @return \Cake\Database\ExpressionInterface
*/
public function identifier($identifier)
{
return new IdentifierExpression($identifier);
}
/**
* Set the values for an insert query.
*
* Multi inserts can be performed by calling values() more than one time,
* or by providing an array of value sets. Additionally $data can be a Query
* instance to insert data from another SELECT statement.
*
* @param array|\Cake\Database\Query $data The data to insert.
* @return $this
* @throws \Cake\Database\Exception if you try to set values before declaring columns.
* Or if you try to set values on non-insert queries.
*/
public function values($data)
{
if ($this->_type !== 'insert') {
throw new Exception(
'You cannot add values before defining columns to use.'
);
}
if (empty($this->_parts['insert'])) {
throw new Exception(
'You cannot add values before defining columns to use.'
);
}
$this->_dirty();
if ($data instanceof ValuesExpression) {
$this->_parts['values'] = $data;
return $this;
}
$this->_parts['values']->add($data);
return $this;
}
/**
* Create an update query.
*
* Can be combined with set() and where() methods to create update queries.
*
* @param string|\Cake\Database\ExpressionInterface $table The table you want to update.
* @return $this
*/
public function update($table)
{
if (!is_string($table) && !($table instanceof ExpressionInterface)) {
$text = 'Table must be of type string or "%s", got "%s"';
$message = sprintf($text, ExpressionInterface::class, gettype($table));
throw new InvalidArgumentException($message);
}
$this->_dirty();
$this->_type = 'update';
$this->_parts['update'][0] = $table;
return $this;
}
/**
* Set one or many fields to update.
*
* ### Examples
*
* Passing a string:
*
* ```
* $query->update('articles')->set('title', 'The Title');
* ```
*
* Passing an array:
*
* ```
* $query->update('articles')->set(['title' => 'The Title'], ['title' => 'string']);
* ```
*
* Passing a callable:
*
* ```
* $query->update('articles')->set(function ($exp) {
* return $exp->eq('title', 'The title', 'string');
* });
* ```
*
* @param string|array|callable|\Cake\Database\Expression\QueryExpression $key The column name or array of keys
* + values to set. This can also be a QueryExpression containing a SQL fragment.
* It can also be a callable, that is required to return an expression object.
* @param mixed $value The value to update $key to. Can be null if $key is an
* array or QueryExpression. When $key is an array, this parameter will be
* used as $types instead.
* @param array|string $types The column types to treat data as.
* @return $this
*/
public function set($key, $value = null, $types = [])
{
if (empty($this->_parts['set'])) {
$this->_parts['set'] = $this->newExpr()->setConjunction(',');
}
if ($this->_parts['set']->isCallable($key)) {
$exp = $this->newExpr()->setConjunction(',');
$this->_parts['set']->add($key($exp));
return $this;
}
if (is_array($key) || $key instanceof ExpressionInterface) {
$types = (array)$value;
$this->_parts['set']->add($key, $types);
return $this;
}
if (is_string($types) && is_string($key)) {
$types = [$key => $types];
}
$this->_parts['set']->eq($key, $value, $types);
return $this;
}
/**
* Create a delete query.
*
* Can be combined with from(), where() and other methods to
* create delete queries with specific conditions.
*
* @param string|null $table The table to use when deleting.
* @return $this
*/
public function delete($table = null)
{
$this->_dirty();
$this->_type = 'delete';
if ($table !== null) {
$this->from($table);
}
return $this;
}
/**
* A string or expression that will be appended to the generated query
*
* ### Examples:
* ```
* $query->select('id')->where(['author_id' => 1])->epilog('FOR UPDATE');
* $query
* ->insert('articles', ['title'])
* ->values(['author_id' => 1])
* ->epilog('RETURNING id');
* ```
*
* Epliog content is raw SQL and not suitable for use with user supplied data.
*
* @param string|\Cake\Database\Expression\QueryExpression|null $expression The expression to be appended
* @return $this
*/
public function epilog($expression = null)
{
$this->_dirty();
$this->_parts['epilog'] = $expression;
return $this;
}
/**
* Returns the type of this query (select, insert, update, delete)
*
* @return string
*/
public function type()
{
return $this->_type;
}
/**
* Returns a new QueryExpression object. This is a handy function when
* building complex queries using a fluent interface. You can also override
* this function in subclasses to use a more specialized QueryExpression class
* if required.
*
* You can optionally pass a single raw SQL string or an array or expressions in
* any format accepted by \Cake\Database\Expression\QueryExpression:
*
* ```
* $expression = $query->newExpr(); // Returns an empty expression object
* $expression = $query->newExpr('Table.column = Table2.column'); // Return a raw SQL expression
* ```
*
* @param mixed $rawExpression A string, array or anything you want wrapped in an expression object
* @return \Cake\Database\Expression\QueryExpression
*/
public function newExpr($rawExpression = null)
{
$expression = new QueryExpression([], $this->getTypeMap());
if ($rawExpression !== null) {
$expression->add($rawExpression);
}
return $expression;
}
/**
* Returns an instance of a functions builder object that can be used for
* generating arbitrary SQL functions.
*
* ### Example:
*
* ```
* $query->func()->count('*');
* $query->func()->dateDiff(['2012-01-05', '2012-01-02'])
* ```
*
* @return \Cake\Database\FunctionsBuilder
*/
public function func()
{
if ($this->_functionsBuilder === null) {
$this->_functionsBuilder = new FunctionsBuilder();
}
return $this->_functionsBuilder;
}
/**
* Executes this query and returns a results iterator. This function is required
* for implementing the IteratorAggregate interface and allows the query to be
* iterated without having to call execute() manually, thus making it look like
* a result set instead of the query itself.
*
* @return \Cake\Database\StatementInterface|null
*/
public function getIterator()
{
if ($this->_iterator === null || $this->_dirty) {
$this->_iterator = $this->execute();
}
return $this->_iterator;
}
/**
* Returns any data that was stored in the specified clause. This is useful for
* modifying any internal part of the query and it is used by the SQL dialects
* to transform the query accordingly before it is executed. The valid clauses that
* can be retrieved are: delete, update, set, insert, values, select, distinct,
* from, join, set, where, group, having, order, limit, offset and union.
*
* The return value for each of those parts may vary. Some clauses use QueryExpression
* to internally store their state, some use arrays and others may use booleans or
* integers. This is summary of the return types for each clause.
*
* - update: string The name of the table to update
* - set: QueryExpression
* - insert: array, will return an array containing the table + columns.
* - values: ValuesExpression
* - select: array, will return empty array when no fields are set
* - distinct: boolean
* - from: array of tables
* - join: array
* - set: array
* - where: QueryExpression, returns null when not set
* - group: array
* - having: QueryExpression, returns null when not set
* - order: OrderByExpression, returns null when not set
* - limit: integer or QueryExpression, null when not set
* - offset: integer or QueryExpression, null when not set
* - union: array
*
* @param string $name name of the clause to be returned
* @return mixed
* @throws \InvalidArgumentException When the named clause does not exist.
*/
public function clause($name)
{
if (!array_key_exists($name, $this->_parts)) {
$clauses = implode(', ', array_keys($this->_parts));
throw new InvalidArgumentException("The '$name' clause is not defined. Valid clauses are: $clauses");
}
return $this->_parts[$name];
}
/**
* Registers a callback to be executed for each result that is fetched from the
* result set, the callback function will receive as first parameter an array with
* the raw data from the database for every row that is fetched and must return the
* row with any possible modifications.
*
* Callbacks will be executed lazily, if only 3 rows are fetched for database it will
* called 3 times, event though there might be more rows to be fetched in the cursor.
*
* Callbacks are stacked in the order they are registered, if you wish to reset the stack
* the call this function with the second parameter set to true.
*
* If you wish to remove all decorators from the stack, set the first parameter
* to null and the second to true.
*
* ### Example
*
* ```
* $query->decorateResults(function ($row) {
* $row['order_total'] = $row['subtotal'] + ($row['subtotal'] * $row['tax']);
* return $row;
* });
* ```
*
* @param callable|null $callback The callback to invoke when results are fetched.
* @param bool $overwrite Whether or not this should append or replace all existing decorators.
* @return $this
*/
public function decorateResults($callback, $overwrite = false)
{
if ($overwrite) {
$this->_resultDecorators = [];
}
if ($callback !== null) {
$this->_resultDecorators[] = $callback;
}
return $this;
}
/**
* This function works similar to the traverse() function, with the difference
* that it does a full depth traversal of the entire expression tree. This will execute
* the provided callback function for each ExpressionInterface object that is
* stored inside this query at any nesting depth in any part of the query.
*
* Callback will receive as first parameter the currently visited expression.
*
* @param callable $callback the function to be executed for each ExpressionInterface
* found inside this query.
* @return $this|null
*/
public function traverseExpressions(callable $callback)
{
$visitor = function ($expression) use (&$visitor, $callback) {
if (is_array($expression)) {
foreach ($expression as $e) {
$visitor($e);
}
return null;
}
if ($expression instanceof ExpressionInterface) {
$expression->traverse($visitor);
if (!($expression instanceof self)) {
$callback($expression);
}
}
};
return $this->traverse($visitor);
}
/**
* Associates a query placeholder to a value and a type.
*
* ```
* $query->bind(':id', 1, 'integer');
* ```
*
* @param string|int $param placeholder to be replaced with quoted version
* of $value
* @param mixed $value The value to be bound
* @param string|int $type the mapped type name, used for casting when sending
* to database
* @return $this
*/
public function bind($param, $value, $type = 'string')
{
$this->getValueBinder()->bind($param, $value, $type);
return $this;
}
/**
* Returns the currently used ValueBinder instance.
*
* A ValueBinder is responsible for generating query placeholders and temporarily
* associate values to those placeholders so that they can be passed correctly
* to the statement object.
*
* @return \Cake\Database\ValueBinder
*/
public function getValueBinder()
{
if ($this->_valueBinder === null) {
$this->_valueBinder = new ValueBinder();
}
return $this->_valueBinder;
}
/**
* Overwrite the current value binder
*
* A ValueBinder is responsible for generating query placeholders and temporarily
* associate values to those placeholders so that they can be passed correctly
* to the statement object.
*
* @param \Cake\Database\ValueBinder|bool $binder The binder or false to disable binding.
* @return $this
*/
public function setValueBinder($binder)
{
$this->_valueBinder = $binder;
return $this;
}
/**
* Returns the currently used ValueBinder instance. If a value is passed,
* it will be set as the new instance to be used.
*
* A ValueBinder is responsible for generating query placeholders and temporarily
* associate values to those placeholders so that they can be passed correctly
* to the statement object.
*
* @deprecated 3.5.0 Use setValueBinder()/getValueBinder() instead.
* @param \Cake\Database\ValueBinder|false|null $binder new instance to be set. If no value is passed the
* default one will be returned
* @return $this|\Cake\Database\ValueBinder
*/
public function valueBinder($binder = null)
{
deprecationWarning('Query::valueBinder() is deprecated. Use Query::getValueBinder()/setValueBinder() instead.');
if ($binder === null) {
if ($this->_valueBinder === null) {
$this->_valueBinder = new ValueBinder();
}
return $this->_valueBinder;
}
$this->_valueBinder = $binder;
return $this;
}
/**
* Enables/Disables buffered results.
*
* When enabled the results returned by this Query will be
* buffered. This enables you to iterate a result set multiple times, or
* both cache and iterate it.
*
* When disabled it will consume less memory as fetched results are not
* remembered for future iterations.
*
* @param bool $enable Whether or not to enable buffering
* @return $this
*/
public function enableBufferedResults($enable = true)
{
$this->_dirty();
$this->_useBufferedResults = (bool)$enable;
return $this;
}
/**
* Disables buffered results.
*
* Disabling buffering will consume less memory as fetched results are not
* remembered for future iterations.
*
* @return $this
*/
public function disableBufferedResults()
{
$this->_dirty();
$this->_useBufferedResults = false;
return $this;
}
/**
* Returns whether buffered results are enabled/disabled.
*
* When enabled the results returned by this Query will be
* buffered. This enables you to iterate a result set multiple times, or
* both cache and iterate it.
*
* When disabled it will consume less memory as fetched results are not
* remembered for future iterations.
*
* @return bool
*/
public function isBufferedResultsEnabled()
{
return $this->_useBufferedResults;
}
/**
* Enable/Disable buffered results.
*
* When enabled the results returned by this Query will be
* buffered. This enables you to iterate a result set multiple times, or
* both cache and iterate it.
*
* When disabled it will consume less memory as fetched results are not
* remembered for future iterations.
*
* If called with no arguments, it will return whether or not buffering is
* enabled.
*
* @deprecated 3.4.0 Use enableBufferedResults()/isBufferedResultsEnabled() instead.
* @param bool|null $enable Whether or not to enable buffering
* @return bool|$this
*/
public function bufferResults($enable = null)
{
deprecationWarning(
'Query::bufferResults() is deprecated. ' .
'Use Query::enableBufferedResults()/isBufferedResultsEnabled() instead.'
);
if ($enable !== null) {
return $this->enableBufferedResults($enable);
}
return $this->isBufferedResultsEnabled();
}
/**
* Sets the TypeMap class where the types for each of the fields in the
* select clause are stored.
*
* @param \Cake\Database\TypeMap $typeMap The map object to use
* @return $this
*/
public function setSelectTypeMap(TypeMap $typeMap)
{
$this->_selectTypeMap = $typeMap;
$this->_dirty();
return $this;
}
/**
* Gets the TypeMap class where the types for each of the fields in the
* select clause are stored.
*
* @return \Cake\Database\TypeMap
*/
public function getSelectTypeMap()
{
if ($this->_selectTypeMap === null) {
$this->_selectTypeMap = new TypeMap();
}
return $this->_selectTypeMap;
}
/**
* Disables the automatic casting of fields to their corresponding PHP data type
*
* @return $this
*/
public function disableResultsCasting()
{
$this->typeCastEnabled = false;
return $this;
}
/**
* Enables the automatic casting of fields to their corresponding type
*
* @return $this
*/
public function enableResultsCasting()
{
$this->typeCastEnabled = true;
return $this;
}
/**
* Sets the TypeMap class where the types for each of the fields in the
* select clause are stored.
*
* When called with no arguments, the current TypeMap object is returned.
*
* @deprecated 3.4.0 Use setSelectTypeMap()/getSelectTypeMap() instead.
* @param \Cake\Database\TypeMap|null $typeMap The map object to use
* @return $this|\Cake\Database\TypeMap
*/
public function selectTypeMap(TypeMap $typeMap = null)
{
deprecationWarning(
'Query::selectTypeMap() is deprecated. ' .
'Use Query::setSelectTypeMap()/getSelectTypeMap() instead.'
);
if ($typeMap !== null) {
return $this->setSelectTypeMap($typeMap);
}
return $this->getSelectTypeMap();
}
/**
* Auxiliary function used to wrap the original statement from the driver with
* any registered callbacks.
*
* @param \Cake\Database\StatementInterface $statement to be decorated
* @return \Cake\Database\Statement\CallbackStatement
*/
protected function _decorateStatement($statement)
{
$typeMap = $this->getSelectTypeMap();
$driver = $this->getConnection()->getDriver();
if ($this->typeCastEnabled && $typeMap->toArray()) {
$statement = new CallbackStatement($statement, $driver, new FieldTypeConverter($typeMap, $driver));
}
foreach ($this->_resultDecorators as $f) {
$statement = new CallbackStatement($statement, $driver, $f);
}
return $statement;
}
/**
* Helper function used to build conditions by composing QueryExpression objects.
*
* @param string $part Name of the query part to append the new part to
* @param string|array|\Cake\Database\ExpressionInterface|callable|null $append Expression or builder function to append.
* @param string $conjunction type of conjunction to be used to operate part
* @param array $types associative array of type names used to bind values to query
* @return void
*/
protected function _conjugate($part, $append, $conjunction, $types)
{
$expression = $this->_parts[$part] ?: $this->newExpr();
if (empty($append)) {
$this->_parts[$part] = $expression;
return;
}
if ($expression->isCallable($append)) {
$append = $append($this->newExpr(), $this);
}
if ($expression->getConjunction() === $conjunction) {
$expression->add($append, $types);
} else {
$expression = $this->newExpr()
->setConjunction($conjunction)
->add([$expression, $append], $types);
}
$this->_parts[$part] = $expression;
$this->_dirty();
}
/**
* Marks a query as dirty, removing any preprocessed information
* from in memory caching.
*
* @return void
*/
protected function _dirty()
{
$this->_dirty = true;
if ($this->_iterator && $this->_valueBinder) {
$this->getValueBinder()->reset();
}
}
/**
* Do a deep clone on this object.
*
* Will clone all of the expression objects used in
* each of the clauses, as well as the valueBinder.
*
* @return void
*/
public function __clone()
{
$this->_iterator = null;
if ($this->_valueBinder !== null) {
$this->_valueBinder = clone $this->_valueBinder;
}
if ($this->_selectTypeMap !== null) {
$this->_selectTypeMap = clone $this->_selectTypeMap;
}
foreach ($this->_parts as $name => $part) {
if (empty($part)) {
continue;
}
if (is_array($part)) {
foreach ($part as $i => $piece) {
if ($piece instanceof ExpressionInterface) {
$this->_parts[$name][$i] = clone $piece;
}
}
}
if ($part instanceof ExpressionInterface) {
$this->_parts[$name] = clone $part;
}
}
}
/**
* Returns string representation of this query (complete SQL statement).
*
* @return string
*/
public function __toString()
{
return $this->sql();
}
/**
* Returns an array that can be used to describe the internal state of this
* object.
*
* @return array
*/
public function __debugInfo()
{
try {
set_error_handler(function ($errno, $errstr) {
throw new RuntimeException($errstr, $errno);
}, E_ALL);
$sql = $this->sql();
$params = $this->getValueBinder()->bindings();
} catch (RuntimeException $e) {
$sql = 'SQL could not be generated for this query as it is incomplete.';
$params = [];
} finally {
restore_error_handler();
}
return [
'(help)' => 'This is a Query object, to get the results execute or iterate it.',
'sql' => $sql,
'params' => $params,
'defaultTypes' => $this->getDefaultTypes(),
'decorators' => count($this->_resultDecorators),
'executed' => $this->_iterator ? true : false,
];
}
}