Class Query
Extends the base Query class to provide new methods related to association loading, automatic fields selection, automatic type casting and to wrap results into a specific iterator that will be responsible for hydrating results if required.
See: \Cake\Collection\CollectionInterface For a full description of the collection methods supported by this class
Constants
-
int
APPEND ¶0
Indicates that the operation should append to the list
-
string
JOIN_TYPE_INNER ¶'INNER'
-
string
JOIN_TYPE_LEFT ¶'LEFT'
-
string
JOIN_TYPE_RIGHT ¶'RIGHT'
-
bool
OVERWRITE ¶true
Indicates that the operation should overwrite the list
-
int
PREPEND ¶1
Indicates that the operation should prepend to the list
Property Summary
-
$_autoFields protected
bool|null
Tracks whether or not the original query should include fields from the top level table.
-
$_beforeFindFired protected
bool
True if the beforeFind event has already been triggered for this query
-
$_cache protected
Cake\Datasource\QueryCacher|null
A query cacher instance if this query has caching enabled.
-
$_connection protected
Cake\Database\Connection
Connection instance to be used to execute this query.
-
$_counter protected
callable|null
A callable function that can be used to calculate the total amount of records this query will match when not using
limit
-
$_deleteParts protected
string[]
The list of query clauses to traverse for generating a DELETE statement
-
$_dirty protected
bool
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.
-
$_eagerLoaded protected
bool
Whether the query is standalone or the product of an eager load operation.
-
$_eagerLoader protected
Cake\ORM\EagerLoader|null
Instance of a class responsible for storing association containments and for eager loading them when this query is executed
-
$_formatters protected
callable[]
List of formatter classes or callbacks that will post-process the results when fetched
-
$_functionsBuilder protected
Cake\Database\FunctionsBuilder|null
Instance of functions builder object used for generating arbitrary SQL functions.
-
$_hasFields protected
bool|null
Whether the user select any fields before being executed, this is used to determined if any fields should be automatically be selected.
-
$_hydrate protected
bool
Whether to hydrate results into entity objects
-
$_insertParts protected
string[]
The list of query clauses to traverse for generating an INSERT statement
-
$_iterator protected
Cake\Database\StatementInterface|null
Statement object resulting from executing this query.
-
$_mapReduce protected
array
List of map-reduce routines that should be applied over the query result
-
$_options protected
array
Holds any custom options passed using applyOptions that could not be processed by any method in this class.
-
$_parts protected
array
List of SQL parts that will be used to build this query.
-
$_repository public @property
Cake\ORM\Table
Instance of a table object this query is bound to.
-
$_resultDecorators protected
callable[]
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.
-
$_results protected
iterable|null
A ResultSet.
-
$_resultsCount protected
int|null
The COUNT(*) for the query.
-
$_selectParts protected
string[]
The list of query clauses to traverse for generating a SELECT statement
-
$_selectTypeMap protected
Cake\Database\TypeMap
The Type map for fields in the select clause
-
$_type protected
string
Type of this query (select, insert, update, delete).
-
$_typeMap protected
Cake\Database\TypeMap
-
$_updateParts protected
string[]
The list of query clauses to traverse for generating an UPDATE statement
-
$_useBufferedResults protected
bool
Boolean for tracking whether or not buffered results are enabled.
-
$_valueBinder protected
Cake\Database\ValueBinder|null
The object responsible for generating query placeholders and temporarily store values associated to each of those.
-
$typeCastEnabled protected
bool
Tracking flag to disable casting
Method Summary
-
__call() public
Enables calling methods from the result set as if they were from this class
-
__clone() public
Object clone hook.
-
__construct() public
Constructor
-
__debugInfo() public
Returns an array that can be used to describe the internal state of this object.
-
__toString() public
Returns string representation of this query (complete SQL statement).
-
_addAssociationsToTypeMap() protected
Used to recursively add contained association column types to the query.
-
_addDefaultFields() protected
Inspects if there are any set fields for selecting, otherwise adds all the fields for the default table.
-
_addDefaultSelectTypes() protected
Sets the default types for converting the fields in the select clause
-
_conjugate() protected
Helper function used to build conditions by composing QueryExpression objects.
-
_decorateResults() protected
Decorates the results iterator with MapReduce routines and formatters
-
_decorateStatement() protected
Auxiliary function used to wrap the original statement from the driver with any registered callbacks.
-
_decoratorClass() protected
Returns the name of the class to be used for decorating results
-
_dirty() protected
Marks a query as dirty, removing any preprocessed information from in memory caching such as previous results
-
_execute() protected
Executes this query and returns a ResultSet object containing the results. This will also setup the correct statement class in order to eager load deep associations.
-
_expressionsVisitor() protected
Query parts traversal method used by traverseExpressions()
-
_makeJoin() protected
Returns an array that can be passed to the join method describing a single join clause
-
_performCount() protected
Performs and returns the COUNT(*) for the query.
-
_transformQuery() protected
Applies some defaults to the query object before it is executed.
-
addDefaultTypes() public
Hints this object to associate the correct types when casting conditions for the database. This is done by extracting the field types from the schema associated to the passed table object. This prevents the user from repeating themselves when specifying conditions.
-
aliasField() public
Returns a key => value array representing a single aliased field that can be passed directly to the select() method. The key will contain the alias and the value the actual field name.
-
aliasFields() public
Runs
aliasField()
for each field in the provided list and returns the result under a single array. -
all() public
Fetch the results for this query.
-
andHaving() public
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. -
andWhere() public @method
Connects any previously defined set of conditions to the provided list using the AND operator. {@see \Cake\Database\Query::andWhere()}
-
append() public @method
Appends more rows to the result of the query.
-
applyOptions() public
Populates or adds parts to current query clauses using an array. This is handy for passing all query clauses at once. The option array accepts:
-
bind() public
Associates a query placeholder to a value and a type.
-
cache() public
Enable result caching for this query.
-
chunk() public @method
Groups the results in arrays of $size rows each.
-
clause() public
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.
-
cleanCopy() public
Creates a copy of this current query, triggers beforeFind and resets some state.
-
clearContain() public
Clears the contained associations from the current query.
-
combine() public @method
Returns the values of the column $v index by column $k, and grouped by $g.
-
contain() public
Sets the list of associations that should be eagerly loaded along with this query. The list of associated tables passed must have been previously set as associations using the Table API.
-
count() public
Returns the total amount of results for the query.
-
countBy() public @method
Returns the number of unique values for a column
-
counter() public
Registers a callable function that will be executed when the
count
method in this query is called. The return value for the function will be set as the return value of thecount
method. -
decorateResults() public
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.
-
delete() public
Create a delete query.
-
disableAutoFields() public
Disables automatically appending fields.
-
disableBufferedResults() public
Disables buffered results.
-
disableHydration() public
Disable hydrating entities.
-
disableResultsCasting() public
Disables the automatic casting of fields to their corresponding PHP data type
-
distinct() public
Adds a
DISTINCT
clause to the query to remove duplicates from the result set. This clause can only be used for select statements. -
each() public @method
Passes each of the query results to the callable
-
eagerLoaded() public
Sets the query instance to be an eager loaded query. If no argument is passed, the current configured query
_eagerLoaded
value is returned. -
enableAutoFields() public
Sets whether or not the ORM should automatically append fields.
-
enableBufferedResults() public
Enables/Disables buffered results.
-
enableHydration() public
Toggle hydrating entities.
-
enableResultsCasting() public
Enables the automatic casting of fields to their corresponding type
-
epilog() public
A string or expression that will be appended to the generated query
-
every() public @method
Returns true if all the results pass the callable test
-
execute() public
Compiles the SQL representation of this query and executes it using the configured connection object. Returns the resulting statement object.
-
extract() public @method
Extracts a single column from each row
-
filter() public @method
Keeps the results using passing the callable test
-
find() public
Apply custom finds to against an existing query object.
-
first() public
Returns the first result out of executing this query, if the query has not been executed before, it will set the limit clause to 1 for performance reasons.
-
firstOrFail() public
Get the first result from the executing query or raise an exception.
-
formatResults() public
Registers a new formatter callback function that is to be executed when trying to fetch the results from the database.
-
from() public
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.
-
func() public
Returns an instance of a functions builder object that can be used for generating arbitrary SQL functions.
-
getConnection() public
Gets the connection instance to be used for executing and transforming this query.
-
getContain() public
-
getDefaultTypes() public
Gets default types of current type map.
-
getEagerLoader() public
Returns the currently configured instance.
-
getIterator() public
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.
-
getMapReducers() public
Returns the list of previously registered map reduce routines.
-
getOptions() public
Returns an array with the custom options that were applied to this query and that were not already processed by another method in this class.
-
getRepository() public @method
Returns the default table object that will be used by this query, that is, the table that will appear in the from clause.
-
getResultFormatters() public
Returns the list of previously registered format routines.
-
getSelectTypeMap() public
Gets the TypeMap class where the types for each of the fields in the select clause are stored.
-
getTypeMap() public
Returns the existing type map.
-
getValueBinder() public
Returns the currently used ValueBinder instance.
-
group() public
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.
-
groupBy() public @method
In-memory group all results by the value of a column.
-
having() public
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 methodwhere()
does. Please refer to its documentation for an insight on how to using each parameter. -
identifier() public
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.
-
indexBy() public @method
Returns the results indexed by the value of a column.
-
innerJoin() public
Adds a single
INNER JOIN
clause to the query. -
innerJoinWith() public
Creates an INNER JOIN with the passed association table while preserving the foreign key matching and the custom conditions that were originally set for it.
-
insert() public
Create an insert query.
-
into() public
Set the table name for insert queries.
-
isAutoFieldsEnabled() public
Gets whether or not the ORM should automatically append fields.
-
isBufferedResultsEnabled() public
Returns whether buffered results are enabled/disabled.
-
isEagerLoaded() public
Returns the current configured query
_eagerLoaded
value -
isEmpty() public @method
Returns true if this query found no results.
-
isHydrationEnabled() public
Returns the current hydration mode.
-
join() public
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.
-
jsonSerialize() public
Executes the query and converts the result set into JSON.
-
last() public @method
Return the last row of the query result
-
leftJoin() public
Adds a single
LEFT JOIN
clause to the query. -
leftJoinWith() public
Creates a LEFT JOIN with the passed association table while preserving the foreign key matching and the custom conditions that were originally set for it.
-
limit() public
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.
-
map() public @method
Modifies each of the results using the callable
-
mapReduce() public
Register a new MapReduce routine to be executed on top of the database results Both the mapper and caller callable should be invokable objects.
-
matching() public
Adds filtering conditions to this query to only bring rows that have a relation to another from an associated table, based on conditions in the associated table.
-
max() public @method
Returns the maximum value for a single column in all the results.
-
min() public @method
Returns the minimum value for a single column in all the results.
-
modifier() public
Adds a single or multiple
SELECT
modifiers to be used in theSELECT
. -
nest() public @method
Creates a tree structure by nesting the values of column $p into that with the same value for $k using $n as the nesting key.
-
newExpr() public
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.
-
notMatching() public
Adds filtering conditions to this query to only bring rows that have no match to another from an associated table, based on conditions in the associated table.
-
offset() public
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.
-
order() public
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.
-
orderAsc() public
Add an ORDER BY clause with an ASC direction.
-
orderDesc() public
Add an ORDER BY clause with a DESC direction.
-
page() public
Set the page of results you want.
-
reduce() public @method
Folds all the results into a single value using the callable.
-
reject() public @method
Removes the results passing the callable test
-
removeJoin() public
Remove a join if it has been defined.
-
repository() public
Set the default Table object that will be used by this query and form the
FROM
clause. -
rightJoin() public
Adds a single
RIGHT JOIN
clause to the query. -
rowCountAndClose() public
Executes the SQL of this query and immediately closes the statement before returning the row count of records changed.
-
sample() public @method
In-memory shuffle the results and return a subset of them.
-
select() public
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. -
selectAllExcept() public
All the fields associated with the passed table except the excluded fields will be added to the select clause of the query. Passed excluded fields should not be aliased. After the first call to this method, a second call cannot be used to remove fields that have already been added to the query by the first. If you need to change the list after the first call, pass overwrite boolean true which will reset the select clause removing all previous additions.
-
set() public
Set one or many fields to update.
-
setConnection() public
Sets the connection instance to be used for executing and transforming this query.
-
setDefaultTypes() public
Overwrite the default type mappings for fields in the implementing object.
-
setEagerLoader() public
Sets the instance of the eager loader class to use for loading associations and storing containments.
-
setResult() public
Set the result set for a query.
-
setSelectTypeMap() public
Sets the TypeMap class where the types for each of the fields in the select clause are stored.
-
setTypeMap() public
Creates a new TypeMap if $typeMap is an array, otherwise exchanges it for the given one.
-
setValueBinder() public
Overwrite the current value binder
-
shuffle() public @method
In-memory randomize the order the results are returned
-
skip() public @method
Skips some rows from the start of the query result.
-
some() public @method
Returns true if at least one of the results pass the callable test
-
sortBy() public @method
Sorts the query with the callback
-
sql() public
Returns the SQL representation of this object.
-
stopWhen() public @method
Returns each row until the callable returns true.
-
sumOf() public @method
Returns the sum of all values for a single column
-
take() public @method
In-memory limit and offset for the query results.
-
toArray() public @method
Returns a key-value array with the results of this query.
-
toList() public @method
Returns a numerically indexed array with the results of this query.
-
traverse() public
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.
-
traverseExpressions() public
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.
-
traverseParts() public
Will iterate over the provided parts.
-
triggerBeforeFind() public
Trigger the beforeFind event on the query's repository object.
-
type() public
Returns the type of this query (select, insert, update, delete)
-
union() public
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.
-
unionAll() public
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.
-
update() public
Create an update query.
-
values() public
Set the values for an insert query.
-
where() public
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.
-
whereInList() public
Adds an IN condition or set of conditions to be used in the WHERE clause for this query.
-
whereNotInList() public
Adds a NOT IN condition or set of conditions to be used in the WHERE clause for this query.
-
whereNotNull() public
Convenience method that adds a NOT NULL condition to the query
-
whereNull() public
Convenience method that adds a IS NULL condition to the query
-
zip() public @method
Returns the first result of both the query and $c in an array, then the second results and so on.
-
zipWith() public @method
Returns each of the results out of calling $c with the first rows of the query and each of the items, then the second rows and so on.
Method Detail
__call() ¶ public
__call(string $method, array $arguments): mixed
Enables calling methods from the result set as if they were from this class
Parameters
-
string
$method the method to call
-
array
$arguments list of arguments for the method to call
Returns
mixed
Throws
BadMethodCallException
if the method is called for a non-select query
__clone() ¶ public
__clone(): void
Object clone hook.
Destroys the clones inner iterator and clones the value binder, and eagerloader instances.
Returns
void
__construct() ¶ public
__construct(Cake\Database\Connection $connection, Cake\ORM\Table $table)
Constructor
Parameters
-
Cake\Database\Connection
$connection The connection object
-
Cake\ORM\Table
$table The table this query is starting on
__debugInfo() ¶ public
__debugInfo(): array
Returns an array that can be used to describe the internal state of this object.
Returns
array
__toString() ¶ public
__toString(): string
Returns string representation of this query (complete SQL statement).
Returns
string
_addAssociationsToTypeMap() ¶ protected
_addAssociationsToTypeMap(Cake\ORM\Table $table, Cake\Database\TypeMap $typeMap, array $associations): void
Used to recursively add contained association column types to the query.
Parameters
-
Cake\ORM\Table
$table The table instance to pluck associations from.
-
Cake\Database\TypeMap
$typeMap The typemap to check for columns in. This typemap is indirectly mutated via Cake\ORM\Query::addDefaultTypes()
-
array
$associations The nested tree of associations to walk.
Returns
void
_addDefaultFields() ¶ protected
_addDefaultFields(): void
Inspects if there are any set fields for selecting, otherwise adds all the fields for the default table.
Returns
void
_addDefaultSelectTypes() ¶ protected
_addDefaultSelectTypes(): void
Sets the default types for converting the fields in the select clause
Returns
void
_conjugate() ¶ protected
_conjugate(string $part, string|array|Cake\Database\ExpressionInterface|Closure|null $append, string $conjunction, array $types): void
Helper function used to build conditions by composing QueryExpression objects.
Parameters
-
string
$part Name of the query part to append the new part to
-
string|array|Cake\Database\ExpressionInterface|Closure|null
$append Expression or builder function to append. to append.
-
string
$conjunction type of conjunction to be used to operate part
-
array
$types associative array of type names used to bind values to query
Returns
void
_decorateResults() ¶ protected
_decorateResults(Traversable $result): Cake\Datasource\ResultSetInterface
Decorates the results iterator with MapReduce routines and formatters
Parameters
-
Traversable
$result Original results
Returns
Cake\Datasource\ResultSetInterface
_decorateStatement() ¶ protected
_decorateStatement(Cake\Database\StatementInterface $statement): Cake\Database\Statement\CallbackStatement|Cake\Database\StatementInterface
Auxiliary function used to wrap the original statement from the driver with any registered callbacks.
Parameters
-
Cake\Database\StatementInterface
$statement to be decorated
Returns
Cake\Database\Statement\CallbackStatement|Cake\Database\StatementInterface
_decoratorClass() ¶ protected
_decoratorClass(): string
Returns the name of the class to be used for decorating results
Returns
string
_dirty() ¶ protected
_dirty(): void
Marks a query as dirty, removing any preprocessed information from in memory caching such as previous results
Returns
void
_execute() ¶ protected
_execute(): Cake\Datasource\ResultSetInterface
Executes this query and returns a ResultSet object containing the results. This will also setup the correct statement class in order to eager load deep associations.
Returns
Cake\Datasource\ResultSetInterface
_expressionsVisitor() ¶ protected
_expressionsVisitor(Cake\Database\ExpressionInterface|Cake\Database\ExpressionInterface[] $expression, Closure $callback): void
Query parts traversal method used by traverseExpressions()
Parameters
-
Cake\Database\ExpressionInterface|Cake\Database\ExpressionInterface[]
$expression Query expression or array of expressions.
-
Closure
$callback The callback to be executed for each ExpressionInterface found inside this query.
Returns
void
_makeJoin() ¶ protected
_makeJoin(string|string[] $table, string|array|Cake\Database\ExpressionInterface $conditions, string $type): array
Returns an array that can be passed to the join method describing a single join clause
Parameters
-
string|string[]
$table The table to join with
-
string|array|Cake\Database\ExpressionInterface
$conditions The conditions to use for joining.
-
string
$type the join type to use
Returns
array
_performCount() ¶ protected
_performCount(): int
Performs and returns the COUNT(*) for the query.
Returns
int
_transformQuery() ¶ protected
_transformQuery(): void
Applies some defaults to the query object before it is executed.
Specifically add the FROM clause, adds default table fields if none are
specified and applies the joins required to eager load associations defined
using contain
It also sets the default types for the columns in the select clause
Returns
void
See Also
addDefaultTypes() ¶ public
addDefaultTypes(Cake\ORM\Table $table): $this
Hints this object to associate the correct types when casting conditions for the database. This is done by extracting the field types from the schema associated to the passed table object. This prevents the user from repeating themselves when specifying conditions.
This method returns the same query object for chaining.
Parameters
-
Cake\ORM\Table
$table The table to pull types from
Returns
$this
aliasField() ¶ public
aliasField(string $field, string|null $alias = null): array
Returns a key => value array representing a single aliased field that can be passed directly to the select() method. The key will contain the alias and the value the actual field name.
If the field is already aliased, then it will not be changed. If no $alias is passed, the default table for this query will be used.
Parameters
-
string
$field The field to alias
-
string|null
$alias optional the alias used to prefix the field
Returns
array
aliasFields() ¶ public
aliasFields(array $fields, string|null $defaultAlias = null): string[]
Runs aliasField()
for each field in the provided list and returns
the result under a single array.
Parameters
-
array
$fields The fields to alias
-
string|null
$defaultAlias optional The default alias
Returns
string[]
all() ¶ public
all(): Cake\Datasource\ResultSetInterface
Fetch the results for this query.
Will return either the results set through setResult(), or execute this query and return the ResultSetDecorator object ready for streaming of results.
ResultSetDecorator is a traversable object that implements the methods found on Cake\Collection\Collection.
Returns
Cake\Datasource\ResultSetInterface
Throws
RuntimeException
if this method is called on a non-select Query.
andHaving() ¶ public
andHaving(string|array|Cake\Database\ExpressionInterface|Closure $conditions, array $types = []): $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.
Parameters
-
string|array|Cake\Database\ExpressionInterface|Closure
$conditions The AND conditions for HAVING.
-
array
$types optional associative array of type names used to bind values to query
Returns
$this
See Also
andWhere() ¶ public @method
andWhere(string|array|Cake\Database\ExpressionInterface|Closure $conditions, array $types = []): $this
Connects any previously defined set of conditions to the provided list using the AND operator. {@see \Cake\Database\Query::andWhere()}
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)
Parameters
-
string|array|Cake\Database\ExpressionInterface|Closure
$conditions -
array
$types optional
Returns
$this
append() ¶ public @method
append(array|Traversable $items): Cake\Collection\CollectionInterface
Appends more rows to the result of the query.
Parameters
-
array|Traversable
$items
Returns
Cake\Collection\CollectionInterface
applyOptions() ¶ public
applyOptions(array $options): $this
Populates or adds parts to current query clauses using an array. This is handy for passing all query clauses at once. The option array accepts:
- fields: Maps to the select method
- conditions: Maps to the where method
- limit: Maps to the limit method
- order: Maps to the order method
- offset: Maps to the offset method
- group: Maps to the group method
- having: Maps to the having method
- contain: Maps to the contain options for eager loading
- join: Maps to the join method
- page: Maps to the page method
Example:
$query->applyOptions([
'fields' => ['id', 'name'],
'conditions' => [
'created >=' => '2013-01-01'
],
'limit' => 10
]);
Is equivalent to:
$query
->select(['id', 'name'])
->where(['created >=' => '2013-01-01'])
->limit(10)
Parameters
-
array
$options the options to be applied
Returns
$this
bind() ¶ public
bind(string|int $param, mixed $value, string|int|null $type = null): $this
Associates a query placeholder to a value and a type.
$query->bind(':id', 1, 'integer');
Parameters
-
string|int
$param placeholder to be replaced with quoted version of $value
-
mixed
$value The value to be bound
-
string|int|null
$type optional the mapped type name, used for casting when sending to database
Returns
$this
cache() ¶ public
cache(Closure|string|false $key, string|Psr\SimpleCache\CacheInterface $config = 'default'): $this
Enable result caching for this query.
If a query has caching enabled, it will do the following when executed:
- Check the cache for $key. If there are results no SQL will be executed. Instead the cached results will be returned.
- When the cached data is stale/missing the result set will be cached as the query is executed.
Usage
// Simple string key + config
$query->cache('my_key', 'db_results');
// Function to generate key.
$query->cache(function ($q) {
$key = serialize($q->clause('select'));
$key .= serialize($q->clause('where'));
return md5($key);
});
// Using a pre-built cache engine.
$query->cache('my_key', $engine);
// Disable caching
$query->cache(false);
Parameters
-
Closure|string|false
$key Either the cache key or a function to generate the cache key. When using a function, this query instance will be supplied as an argument.
-
string|Psr\SimpleCache\CacheInterface
$config optional Either the name of the cache config to use, or a cache config instance.
Returns
$this
Throws
RuntimeException
When you attempt to cache a non-select query.
chunk() ¶ public @method
chunk(int $size): Cake\Collection\CollectionInterface
Groups the results in arrays of $size rows each.
Parameters
-
int
$size
Returns
Cake\Collection\CollectionInterface
clause() ¶ public
clause(string $name): mixed
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
Parameters
-
string
$name name of the clause to be returned
Returns
mixed
Throws
InvalidArgumentException
When the named clause does not exist.
cleanCopy() ¶ public
cleanCopy(): static
Creates a copy of this current query, triggers beforeFind and resets some state.
The following state will be cleared:
- autoFields
- limit
- offset
- map/reduce functions
- result formatters
- order
- containments
This method creates query clones that are useful when working with subqueries.
Returns
static
clearContain() ¶ public
clearContain(): $this
Clears the contained associations from the current query.
Returns
$this
combine() ¶ public @method
combine(mixed $k, mixed $v, mixed $g = null): Cake\Collection\CollectionInterface
Returns the values of the column $v index by column $k, and grouped by $g.
Parameters
-
$k
-
$v
-
$g optional
Returns
Cake\Collection\CollectionInterface
contain() ¶ public
contain(array|string $associations, callable|bool $override = false): $this
Sets the list of associations that should be eagerly loaded along with this query. The list of associated tables passed must have been previously set as associations using the Table API.
Example:
// Bring articles' author information
$query->contain('Author');
// Also bring the category and tags associated to each article
$query->contain(['Category', 'Tag']);
Associations can be arbitrarily nested using dot notation or nested arrays, this allows this object to calculate joins or any additional queries that must be executed to bring the required associated data.
Example:
// Eager load the product info, and for each product load other 2 associations
$query->contain(['Product' => ['Manufacturer', 'Distributor']);
// Which is equivalent to calling
$query->contain(['Products.Manufactures', 'Products.Distributors']);
// For an author query, load his region, state and country
$query->contain('Regions.States.Countries');
It is possible to control the conditions and fields selected for each of the contained associations:
Example:
$query->contain(['Tags' => function ($q) {
return $q->where(['Tags.is_popular' => true]);
}]);
$query->contain(['Products.Manufactures' => function ($q) {
return $q->select(['name'])->where(['Manufactures.active' => true]);
}]);
Each association might define special options when eager loaded, the allowed options that can be set per association are:
foreignKey
: Used to set a different field to match both tables, if set to false no join conditions will be generated automatically.false
can only be used on joinable associations and cannot be used with hasMany or belongsToMany associations.fields
: An array with the fields that should be fetched from the association.finder
: The finder to use when loading associated records. Either the name of the finder as a string, or an array to define options to pass to the finder.queryBuilder
: Equivalent to passing a callable instead of an options array.
Example:
// Set options for the hasMany articles that will be eagerly loaded for an author
$query->contain([
'Articles' => [
'fields' => ['title', 'author_id']
]
]);
Finders can be configured to use options.
// Retrieve translations for the articles, but only those for the `en` and `es` locales
$query->contain([
'Articles' => [
'finder' => [
'translations' => [
'locales' => ['en', 'es']
]
]
]
]);
When containing associations, it is important to include foreign key columns. Failing to do so will trigger exceptions.
// Use a query builder to add conditions to the containment
$query->contain('Authors', function ($q) {
return $q->where(...); // add conditions
});
// Use special join conditions for multiple containments in the same method call
$query->contain([
'Authors' => [
'foreignKey' => false,
'queryBuilder' => function ($q) {
return $q->where(...); // Add full filtering conditions
}
],
'Tags' => function ($q) {
return $q->where(...); // add conditions
}
]);
If called with an empty first argument and $override
is set to true, the
previous list will be emptied.
Parameters
-
array|string
$associations List of table aliases to be queried.
-
callable|bool
$override optional The query builder for the association, or if associations is an array, a bool on whether to override previous list with the one passed defaults to merging previous list with the new one.
Returns
$this
count() ¶ public
count(): int
Returns the total amount of results for the query.
Returns the COUNT(*) for the query. If the query has not been modified, and the count has already been performed the cached value is returned
Returns
int
countBy() ¶ public @method
countBy(string|callable $field): Cake\Collection\CollectionInterface
Returns the number of unique values for a column
Parameters
-
string|callable
$field
Returns
Cake\Collection\CollectionInterface
counter() ¶ public
counter(callable|null $counter): $this
Registers a callable function that will be executed when the count
method in
this query is called. The return value for the function will be set as the
return value of the count
method.
This is particularly useful when you need to optimize a query for returning the count, for example removing unnecessary joins, removing group by or just return an estimated number of rows.
The callback will receive as first argument a clone of this query and not this query itself.
If the first param is a null value, the built-in counter function will be called instead
Parameters
-
callable|null
$counter The counter value
Returns
$this
decorateResults() ¶ public
decorateResults(callable|null $callback, bool $overwrite = false): $this
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;
});
Parameters
-
callable|null
$callback The callback to invoke when results are fetched.
-
bool
$overwrite optional Whether or not this should append or replace all existing decorators.
Returns
$this
delete() ¶ public
delete(string|null $table = null): $this
Create a delete query.
This changes the query type to be 'delete'. Can be combined with the where() method to create delete queries.
Parameters
-
string|null
$table optional Unused parameter.
Returns
$this
disableAutoFields() ¶ public
disableAutoFields(): $this
Disables automatically appending fields.
Returns
$this
disableBufferedResults() ¶ public
disableBufferedResults(): $this
Disables buffered results.
Disabling buffering will consume less memory as fetched results are not remembered for future iterations.
Returns
$this
disableHydration() ¶ public
disableHydration(): $this
Disable hydrating entities.
Disabling hydration will cause array results to be returned for the query instead of entities.
Returns
$this
disableResultsCasting() ¶ public
disableResultsCasting(): $this
Disables the automatic casting of fields to their corresponding PHP data type
Returns
$this
distinct() ¶ public
distinct(array|Cake\Database\ExpressionInterface|string|bool $on = [], bool $overwrite = false): $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);
Parameters
-
array|Cake\Database\ExpressionInterface|string|bool
$on optional Enable/disable distinct class or list of fields to be filtered on
-
bool
$overwrite optional whether to reset fields with passed list or not
Returns
$this
each() ¶ public @method
each(callable $c): Cake\Collection\CollectionInterface
Passes each of the query results to the callable
Parameters
-
callable
$c
Returns
Cake\Collection\CollectionInterface
eagerLoaded() ¶ public
eagerLoaded(bool $value): $this
Sets the query instance to be an eager loaded query. If no argument is
passed, the current configured query _eagerLoaded
value is returned.
Parameters
-
bool
$value Whether or not to eager load.
Returns
$this
enableAutoFields() ¶ public
enableAutoFields(bool $value = true): $this
Sets whether or not the ORM should automatically append fields.
By default calling select() will disable auto-fields. You can re-enable auto-fields with this method.
Parameters
-
bool
$value optional Set true to enable, false to disable.
Returns
$this
enableBufferedResults() ¶ public
enableBufferedResults(bool $enable = true): $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.
Parameters
-
bool
$enable optional Whether or not to enable buffering
Returns
$this
enableHydration() ¶ public
enableHydration(bool $enable = true): $this
Toggle hydrating entities.
If set to false array results will be returned for the query.
Parameters
-
bool
$enable optional Use a boolean to set the hydration mode.
Returns
$this
enableResultsCasting() ¶ public
enableResultsCasting(): $this
Enables the automatic casting of fields to their corresponding type
Returns
$this
epilog() ¶ public
epilog(string|Cake\Database\Expression\QueryExpression|null $expression = null): $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.
Parameters
-
string|Cake\Database\Expression\QueryExpression|null
$expression optional The expression to be appended
Returns
$this
every() ¶ public @method
every(callable $c): bool
Returns true if all the results pass the callable test
Parameters
-
callable
$c
Returns
bool
execute() ¶ public
execute(): Cake\Database\StatementInterface
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.
Returns
Cake\Database\StatementInterface
extract() ¶ public @method
extract(mixed $field): Cake\Collection\CollectionInterface
Extracts a single column from each row
Parameters
-
$field
Returns
Cake\Collection\CollectionInterface
filter() ¶ public @method
filter(callable $c = null): Cake\Collection\CollectionInterface
Keeps the results using passing the callable test
Parameters
-
callable
$c optional
Returns
Cake\Collection\CollectionInterface
find() ¶ public
find(string $finder, array $options = []): static
Apply custom finds to against an existing query object.
Allows custom find methods to be combined and applied to each other.
$repository->find('all')->find('recent');
The above is an example of stacking multiple finder methods onto a single query.
Parameters
-
string
$finder The finder method to use.
-
array
$options optional The options for the finder.
Returns
static
first() ¶ public
first(): Cake\Datasource\EntityInterface|array|null
Returns the first result out of executing this query, if the query has not been executed before, it will set the limit clause to 1 for performance reasons.
Example:
$singleUser = $query->select(['id', 'username'])->first();
Returns
Cake\Datasource\EntityInterface|array|null
firstOrFail() ¶ public
firstOrFail(): Cake\Datasource\EntityInterface|array
Get the first result from the executing query or raise an exception.
Returns
Cake\Datasource\EntityInterface|array
Throws
Cake\Datasource\Exception\RecordNotFoundException
When there is no first record.
formatResults() ¶ public
formatResults(callable|null $formatter = null, int|true $mode = self::APPEND): $this
Registers a new formatter callback function that is to be executed when trying to fetch the results from the database.
Formatting callbacks will get a first parameter, an object implementing
\Cake\Collection\CollectionInterface
, that can be traversed and modified at will.
Callbacks are required to return an iterator object, which will be used as
the return value for this query's result. Formatter functions are applied
after all the MapReduce
routines for this query have been executed.
If the second argument is set to true, it will erase previous formatters and replace them with the passed first argument.
Example:
// Return all results from the table indexed by id
$query->select(['id', 'name'])->formatResults(function ($results) {
return $results->indexBy('id');
});
// Add a new column to the ResultSet
$query->select(['name', 'birth_date'])->formatResults(function ($results) {
return $results->map(function ($row) {
$row['age'] = $row['birth_date']->diff(new DateTime)->y;
return $row;
});
});
Parameters
-
callable|null
$formatter optional The formatting callable.
-
int|true
$mode optional Whether or not to overwrite, append or prepend the formatter.
Returns
$this
Throws
InvalidArgumentException
from() ¶ public
from(array|string $tables = [], bool $overwrite = false): $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
Parameters
-
array|string
$tables optional 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.
-
bool
$overwrite optional whether to reset tables with passed list or not
Returns
$this
func() ¶ public
func(): Cake\Database\FunctionsBuilder
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'])
Returns
Cake\Database\FunctionsBuilder
getConnection() ¶ public
getConnection(): Cake\Database\Connection
Gets the connection instance to be used for executing and transforming this query.
Returns
Cake\Database\Connection
getDefaultTypes() ¶ public
getDefaultTypes(): array
Gets default types of current type map.
Returns
array
getEagerLoader() ¶ public
getEagerLoader(): Cake\ORM\EagerLoader
Returns the currently configured instance.
Returns
Cake\ORM\EagerLoader
getIterator() ¶ public
getIterator(): Cake\Datasource\ResultSetInterface
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.
Returns
Cake\Datasource\ResultSetInterface
getMapReducers() ¶ public
getMapReducers(): array
Returns the list of previously registered map reduce routines.
Returns
array
getOptions() ¶ public
getOptions(): array
Returns an array with the custom options that were applied to this query and that were not already processed by another method in this class.
Example:
$query->applyOptions(['doABarrelRoll' => true, 'fields' => ['id', 'name']);
$query->getOptions(); // Returns ['doABarrelRoll' => true]
Returns
array
See Also
getRepository() ¶ public @method
getRepository(): Cake\ORM\Table
Returns the default table object that will be used by this query, that is, the table that will appear in the from clause.
Returns
Cake\ORM\Table
getResultFormatters() ¶ public
getResultFormatters(): callable[]
Returns the list of previously registered format routines.
Returns
callable[]
getSelectTypeMap() ¶ public
getSelectTypeMap(): Cake\Database\TypeMap
Gets the TypeMap class where the types for each of the fields in the select clause are stored.
Returns
Cake\Database\TypeMap
getTypeMap() ¶ public
getTypeMap(): Cake\Database\TypeMap
Returns the existing type map.
Returns
Cake\Database\TypeMap
getValueBinder() ¶ public
getValueBinder(): Cake\Database\ValueBinder
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.
Returns
Cake\Database\ValueBinder
group() ¶ public
group(array|Cake\Database\ExpressionInterface|string $fields, bool $overwrite = false): $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.
Parameters
-
array|Cake\Database\ExpressionInterface|string
$fields fields to be added to the list
-
bool
$overwrite optional whether to reset fields with passed list or not
Returns
$this
groupBy() ¶ public @method
groupBy(string|callable $field): Cake\Collection\CollectionInterface
In-memory group all results by the value of a column.
Parameters
-
string|callable
$field
Returns
Cake\Collection\CollectionInterface
having() ¶ public
having(string|array|Cake\Database\ExpressionInterface|Closure|null $conditions = null, array $types = [], bool $overwrite = false): $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.
Parameters
-
string|array|Cake\Database\ExpressionInterface|Closure|null
$conditions optional The having conditions.
-
array
$types optional associative array of type names used to bind values to query
-
bool
$overwrite optional whether to reset conditions with passed list or not
Returns
$this
See Also
identifier() ¶ public
identifier(string $identifier): Cake\Database\ExpressionInterface
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'));
Parameters
-
string
$identifier The identifier for an expression
Returns
Cake\Database\ExpressionInterface
indexBy() ¶ public @method
indexBy(string|callable $callback): Cake\Collection\CollectionInterface
Returns the results indexed by the value of a column.
Parameters
-
string|callable
$callback
Returns
Cake\Collection\CollectionInterface
innerJoin() ¶ public
innerJoin(string|array $table, string|array|Cake\Database\ExpressionInterface $conditions = [], array $types = []): $this
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.
Parameters
-
string|array
$table The table to join with
-
string|array|Cake\Database\ExpressionInterface
$conditions optional The conditions to use for joining.
-
array
$types optional a list of types associated to the conditions used for converting values to the corresponding database representation.
Returns
$this
innerJoinWith() ¶ public
innerJoinWith(string $assoc, callable|null $builder = null): $this
Creates an INNER JOIN with the passed association table while preserving the foreign key matching and the custom conditions that were originally set for it.
This function will add entries in the contain
graph.
Example:
// Bring only articles that were tagged with 'cake'
$query->innerJoinWith('Tags', function ($q) {
return $q->where(['name' => 'cake']);
);
This will create the following SQL:
SELECT Articles.*
FROM articles Articles
INNER JOIN tags Tags ON Tags.name = 'cake'
INNER JOIN articles_tags ArticlesTags ON ArticlesTags.tag_id = Tags.id
AND ArticlesTags.articles_id = Articles.id
This function works the same as matching()
with the difference that it
will select no fields from the association.
Parameters
-
string
$assoc The association to join with
-
callable|null
$builder optional a function that will receive a pre-made query object that can be used to add custom conditions or selecting some fields
Returns
$this
See Also
insert() ¶ public
insert(array $columns, string[] $types = []): $this
Create an insert query.
This changes the query type to be 'insert'. Note calling this method will reset any data previously set with Query::values()
Can be combined with the where() method to create delete queries.
Parameters
-
array
$columns The columns to insert into.
-
string[]
$types optional A map between columns & their datatypes.
Returns
$this
into() ¶ public
into(string $table): $this
Set the table name for insert queries.
Parameters
-
string
$table The table name to insert into.
Returns
$this
isAutoFieldsEnabled() ¶ public
isAutoFieldsEnabled(): bool|null
Gets whether or not the ORM should automatically append fields.
By default calling select() will disable auto-fields. You can re-enable auto-fields with enableAutoFields().
Returns
bool|null
isBufferedResultsEnabled() ¶ public
isBufferedResultsEnabled(): bool
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.
Returns
bool
isEagerLoaded() ¶ public
isEagerLoaded(): bool
Returns the current configured query _eagerLoaded
value
Returns
bool
isEmpty() ¶ public @method
isEmpty(): bool
Returns true if this query found no results.
Returns
bool
isHydrationEnabled() ¶ public
isHydrationEnabled(): bool
Returns the current hydration mode.
Returns
bool
join() ¶ public
join(array|string $tables, array $types = [], bool $overwrite = false): $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
Parameters
-
array|string
$tables list of tables to be joined in the query
-
array
$types optional associative array of type names used to bind values to query
-
bool
$overwrite optional whether to reset joins with passed list or not
Returns
$this
See Also
jsonSerialize() ¶ public
jsonSerialize(): Cake\Datasource\ResultSetInterface
Executes the query and converts the result set into JSON.
Part of JsonSerializable interface.
Returns
Cake\Datasource\ResultSetInterface
leftJoin() ¶ public
leftJoin(string|string[] $table, string|array|Cake\Database\ExpressionInterface $conditions = [], array $types = []): $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.
Parameters
-
string|string[]
$table The table to join with
-
string|array|Cake\Database\ExpressionInterface
$conditions optional The conditions to use for joining.
-
array
$types optional a list of types associated to the conditions used for converting values to the corresponding database representation.
Returns
$this
leftJoinWith() ¶ public
leftJoinWith(string $assoc, callable|null $builder = null): $this
Creates a LEFT JOIN with the passed association table while preserving the foreign key matching and the custom conditions that were originally set for it.
This function will add entries in the contain
graph.
Example:
// Get the count of articles per user
$usersQuery
->select(['total_articles' => $query->func()->count('Articles.id')])
->leftJoinWith('Articles')
->group(['Users.id'])
->enableAutoFields();
You can also customize the conditions passed to the LEFT JOIN:
// Get the count of articles per user with at least 5 votes
$usersQuery
->select(['total_articles' => $query->func()->count('Articles.id')])
->leftJoinWith('Articles', function ($q) {
return $q->where(['Articles.votes >=' => 5]);
})
->group(['Users.id'])
->enableAutoFields();
This will create the following SQL:
SELECT COUNT(Articles.id) AS total_articles, Users.*
FROM users Users
LEFT JOIN articles Articles ON Articles.user_id = Users.id AND Articles.votes >= 5
GROUP BY USers.id
It is possible to left join deep associations by using dot notation
Example:
// Total comments in articles by 'markstory'
$query
->select(['total_comments' => $query->func()->count('Comments.id')])
->leftJoinWith('Comments.Users', function ($q) {
return $q->where(['username' => 'markstory']);
)
->group(['Users.id']);
Please note that the query passed to the closure will only accept calling
select
, where
, andWhere
and orWhere
on it. If you wish to
add more complex clauses you can do it directly in the main query.
Parameters
-
string
$assoc The association to join with
-
callable|null
$builder optional a function that will receive a pre-made query object that can be used to add custom conditions or selecting some fields
Returns
$this
limit() ¶ public
limit(int|Cake\Database\ExpressionInterface|null $num): $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)
Parameters
-
int|Cake\Database\ExpressionInterface|null
$num number of records to be returned
Returns
$this
map() ¶ public @method
map(callable $c): Cake\Collection\CollectionInterface
Modifies each of the results using the callable
Parameters
-
callable
$c
Returns
Cake\Collection\CollectionInterface
mapReduce() ¶ public
mapReduce(callable|null $mapper = null, callable|null $reducer = null, bool $overwrite = false): $this
Register a new MapReduce routine to be executed on top of the database results Both the mapper and caller callable should be invokable objects.
The MapReduce routing will only be run when the query is executed and the first result is attempted to be fetched.
If the third argument is set to true, it will erase previous map reducers and replace it with the arguments passed.
Parameters
-
callable|null
$mapper optional The mapper callable.
-
callable|null
$reducer optional The reducing function.
-
bool
$overwrite optional Set to true to overwrite existing map + reduce functions.
Returns
$this
See Also
matching() ¶ public
matching(string $assoc, callable|null $builder = null): $this
Adds filtering conditions to this query to only bring rows that have a relation to another from an associated table, based on conditions in the associated table.
This function will add entries in the contain
graph.
Example:
// Bring only articles that were tagged with 'cake'
$query->matching('Tags', function ($q) {
return $q->where(['name' => 'cake']);
);
It is possible to filter by deep associations by using dot notation:
Example:
// Bring only articles that were commented by 'markstory'
$query->matching('Comments.Users', function ($q) {
return $q->where(['username' => 'markstory']);
);
As this function will create INNER JOIN
, you might want to consider
calling distinct
on this query as you might get duplicate rows if
your conditions don't filter them already. This might be the case, for example,
of the same user commenting more than once in the same article.
Example:
// Bring unique articles that were commented by 'markstory'
$query->distinct(['Articles.id'])
->matching('Comments.Users', function ($q) {
return $q->where(['username' => 'markstory']);
);
Please note that the query passed to the closure will only accept calling
select
, where
, andWhere
and orWhere
on it. If you wish to
add more complex clauses you can do it directly in the main query.
Parameters
-
string
$assoc The association to filter by
-
callable|null
$builder optional a function that will receive a pre-made query object that can be used to add custom conditions or selecting some fields
Returns
$this
max() ¶ public @method
max(mixed $field, int $type): mixed
Returns the maximum value for a single column in all the results.
Parameters
-
$field
-
int
$type
Returns
mixed
min() ¶ public @method
min(mixed $field, int $type): mixed
Returns the minimum value for a single column in all the results.
Parameters
-
$field
-
int
$type
Returns
mixed
modifier() ¶ public
modifier(array|Cake\Database\ExpressionInterface|string $modifiers, bool $overwrite = false): $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
Parameters
-
array|Cake\Database\ExpressionInterface|string
$modifiers modifiers to be applied to the query
-
bool
$overwrite optional whether to reset order with field list or not
Returns
$this
nest() ¶ public @method
nest(mixed $k, mixed $p, mixed $n = 'children'): Cake\Collection\CollectionInterface
Creates a tree structure by nesting the values of column $p into that with the same value for $k using $n as the nesting key.
Parameters
-
$k
-
$p
-
$n optional
Returns
Cake\Collection\CollectionInterface
newExpr() ¶ public
newExpr(string|array|Cake\Database\ExpressionInterface|null $rawExpression = null): Cake\Database\Expression\QueryExpression
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
Parameters
-
string|array|Cake\Database\ExpressionInterface|null
$rawExpression optional A string, array or anything you want wrapped in an expression object
Returns
Cake\Database\Expression\QueryExpression
notMatching() ¶ public
notMatching(string $assoc, callable|null $builder = null): $this
Adds filtering conditions to this query to only bring rows that have no match to another from an associated table, based on conditions in the associated table.
This function will add entries in the contain
graph.
Example:
// Bring only articles that were not tagged with 'cake'
$query->notMatching('Tags', function ($q) {
return $q->where(['name' => 'cake']);
);
It is possible to filter by deep associations by using dot notation:
Example:
// Bring only articles that weren't commented by 'markstory'
$query->notMatching('Comments.Users', function ($q) {
return $q->where(['username' => 'markstory']);
);
As this function will create a LEFT JOIN
, you might want to consider
calling distinct
on this query as you might get duplicate rows if
your conditions don't filter them already. This might be the case, for example,
of the same article having multiple comments.
Example:
// Bring unique articles that were commented by 'markstory'
$query->distinct(['Articles.id'])
->notMatching('Comments.Users', function ($q) {
return $q->where(['username' => 'markstory']);
);
Please note that the query passed to the closure will only accept calling
select
, where
, andWhere
and orWhere
on it. If you wish to
add more complex clauses you can do it directly in the main query.
Parameters
-
string
$assoc The association to filter by
-
callable|null
$builder optional a function that will receive a pre-made query object that can be used to add custom conditions or selecting some fields
Returns
$this
offset() ¶ public
offset(int|Cake\Database\ExpressionInterface|null $num): $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)
Parameters
-
int|Cake\Database\ExpressionInterface|null
$num number of records to be skipped
Returns
$this
order() ¶ public
order(array|Cake\Database\ExpressionInterface|Closure|string $fields, bool $overwrite = false): $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']);
Will become:
ORDER BY (id %2 = 0), title ASC
If you need to set complex expressions as order conditions, you
should use orderAsc()
or orderDesc()
.
Parameters
-
array|Cake\Database\ExpressionInterface|Closure|string
$fields fields to be added to the list
-
bool
$overwrite optional whether to reset order with field list or not
Returns
$this
orderAsc() ¶ public
orderAsc(string|Cake\Database\Expression\QueryExpression $field, bool $overwrite = false): $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.
Parameters
-
string|Cake\Database\Expression\QueryExpression
$field The field to order on.
-
bool
$overwrite optional Whether or not to reset the order clauses.
Returns
$this
orderDesc() ¶ public
orderDesc(string|Cake\Database\Expression\QueryExpression $field, bool $overwrite = false): $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.
Parameters
-
string|Cake\Database\Expression\QueryExpression
$field The field to order on.
-
bool
$overwrite optional Whether or not to reset the order clauses.
Returns
$this
page() ¶ public
page(int $num, int|null $limit = null): $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.
Parameters
-
int
$num The page number you want.
-
int|null
$limit optional The number of rows you want in the page. If null the current limit clause will be used.
Returns
$this
Throws
InvalidArgumentException
If page number < 1.
reduce() ¶ public @method
reduce(callable $c, mixed $zero = null): mixed
Folds all the results into a single value using the callable.
Parameters
-
callable
$c -
$zero optional
Returns
mixed
reject() ¶ public @method
reject(callable $c): Cake\Collection\CollectionInterface
Removes the results passing the callable test
Parameters
-
callable
$c
Returns
Cake\Collection\CollectionInterface
removeJoin() ¶ public
removeJoin(string $name): $this
Remove a join if it has been defined.
Useful when you are redefining joins or want to re-order the join clauses.
Parameters
-
string
$name The alias/name of the join to remove.
Returns
$this
repository() ¶ public
repository(Cake\Datasource\RepositoryInterface|Cake\ORM\Table $table): $this
Set the default Table object that will be used by this query
and form the FROM
clause.
Parameters
-
Cake\Datasource\RepositoryInterface|Cake\ORM\Table
$table The default table object to use
Returns
$this
rightJoin() ¶ public
rightJoin(string|string[] $table, string|array|Cake\Database\ExpressionInterface $conditions = [], array $types = []): $this
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.
Parameters
-
string|string[]
$table The table to join with
-
string|array|Cake\Database\ExpressionInterface
$conditions optional The conditions to use for joining.
-
array
$types optional a list of types associated to the conditions used for converting values to the corresponding database representation.
Returns
$this
rowCountAndClose() ¶ public
rowCountAndClose(): int
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.
Returns
int
sample() ¶ public @method
sample(int $size = 10): Cake\Collection\CollectionInterface
In-memory shuffle the results and return a subset of them.
Parameters
-
int
$size optional
Returns
Cake\Collection\CollectionInterface
select() ¶ public
select(array|Cake\Database\ExpressionInterface|string|callable $fields = [], bool $overwrite = false): $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.
If you pass an instance of a Cake\ORM\Table
or Cake\ORM\Association
class,
all the fields in the schema of the table or the association will be added to
the select clause.
Parameters
-
array|Cake\Database\ExpressionInterface|string|callable
$fields optional Fields to be added to the list.
-
bool
$overwrite optional whether to reset fields with passed list or not
Returns
$this
selectAllExcept() ¶ public
selectAllExcept(Cake\ORM\Table|Cake\ORM\Association $table, string[] $excludedFields, bool $overwrite = false): $this
All the fields associated with the passed table except the excluded fields will be added to the select clause of the query. Passed excluded fields should not be aliased. After the first call to this method, a second call cannot be used to remove fields that have already been added to the query by the first. If you need to change the list after the first call, pass overwrite boolean true which will reset the select clause removing all previous additions.
Parameters
-
Cake\ORM\Table|Cake\ORM\Association
$table The table to use to get an array of columns
-
string[]
$excludedFields The un-aliased column names you do not want selected from $table
-
bool
$overwrite optional Whether to reset/remove previous selected fields
Returns
$this
Throws
InvalidArgumentException
If Association|Table is not passed in first argument
set() ¶ public
set(string|array|Closure|Cake\Database\Expression\QueryExpression $key, mixed $value = null, array|string $types = []): $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');
});
Parameters
-
string|array|Closure|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 Closure, that is required to return an expression object.
-
mixed
$value optional 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.
-
array|string
$types optional The column types to treat data as.
Returns
$this
setConnection() ¶ public
setConnection(Cake\Database\Connection $connection): $this
Sets the connection instance to be used for executing and transforming this query.
Parameters
-
Cake\Database\Connection
$connection Connection instance
Returns
$this
setDefaultTypes() ¶ public
setDefaultTypes(array $types): $this
Overwrite the default type mappings for fields in the implementing object.
This method is useful if you need to set type mappings that are shared across multiple functions/expressions in a query.
To add a default without overwriting existing ones
use getTypeMap()->addDefaults()
Parameters
-
array
$types The array of types to set.
Returns
$this
See Also
setEagerLoader() ¶ public
setEagerLoader(Cake\ORM\EagerLoader $instance): $this
Sets the instance of the eager loader class to use for loading associations and storing containments.
Parameters
-
Cake\ORM\EagerLoader
$instance The eager loader to use.
Returns
$this
setResult() ¶ public
setResult(iterable $results): $this
Set the result set for a query.
Setting the resultset of a query will make execute() a no-op. Instead of executing the SQL query and fetching results, the ResultSet provided to this method will be returned.
This method is most useful when combined with results stored in a persistent cache.
Parameters
-
iterable
$results The results this query should return.
Returns
$this
setSelectTypeMap() ¶ public
setSelectTypeMap(Cake\Database\TypeMap $typeMap): $this
Sets the TypeMap class where the types for each of the fields in the select clause are stored.
Parameters
-
Cake\Database\TypeMap
$typeMap The map object to use
Returns
$this
setTypeMap() ¶ public
setTypeMap(array|Cake\Database\TypeMap $typeMap): $this
Creates a new TypeMap if $typeMap is an array, otherwise exchanges it for the given one.
Parameters
-
array|Cake\Database\TypeMap
$typeMap Creates a TypeMap if array, otherwise sets the given TypeMap
Returns
$this
setValueBinder() ¶ public
setValueBinder(Cake\Database\ValueBinder|null $binder): $this
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.
Parameters
-
Cake\Database\ValueBinder|null
$binder The binder or null to disable binding.
Returns
$this
shuffle() ¶ public @method
shuffle(): Cake\Collection\CollectionInterface
In-memory randomize the order the results are returned
Returns
Cake\Collection\CollectionInterface
skip() ¶ public @method
skip(int $howMany): Cake\Collection\CollectionInterface
Skips some rows from the start of the query result.
Parameters
-
int
$howMany
Returns
Cake\Collection\CollectionInterface
some() ¶ public @method
some(callable $c): bool
Returns true if at least one of the results pass the callable test
Parameters
-
callable
$c
Returns
bool
sortBy() ¶ public @method
sortBy(mixed $callback, int $dir, int $type): Cake\Collection\CollectionInterface
Sorts the query with the callback
Parameters
-
$callback
-
int
$dir -
int
$type
Returns
Cake\Collection\CollectionInterface
sql() ¶ public
sql(Cake\Database\ValueBinder|null $generator = null): string
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.
Parameters
-
Cake\Database\ValueBinder|null
$generator optional
Returns
string
stopWhen() ¶ public @method
stopWhen(callable $c): Cake\Collection\CollectionInterface
Returns each row until the callable returns true.
Parameters
-
callable
$c
Returns
Cake\Collection\CollectionInterface
sumOf() ¶ public @method
sumOf(string|callable $field): float
Returns the sum of all values for a single column
Parameters
-
string|callable
$field
Returns
float
take() ¶ public @method
take(int $size = 1, int $from = 0): Cake\Collection\CollectionInterface
In-memory limit and offset for the query results.
Parameters
-
int
$size optional -
int
$from optional
Returns
Cake\Collection\CollectionInterface
toArray() ¶ public @method
toArray(): array
Returns a key-value array with the results of this query.
Returns
array
toList() ¶ public @method
toList(): array
Returns a numerically indexed array with the results of this query.
Returns
array
traverse() ¶ public
traverse(callable $visitor): $this
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);
}
});
Parameters
-
callable
$visitor A function or callable to be executed for each part
Returns
$this
traverseExpressions() ¶ public
traverseExpressions(callable $callback): $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.
Parameters
-
callable
$callback the function to be executed for each ExpressionInterface found inside this query.
Returns
$this
traverseParts() ¶ public
traverseParts(callable $visitor, string[] $parts): $this
Will iterate over the provided parts.
Traversing functions can aggregate results using variables in the closure or instance variables. This method can be used to traverse a subset of query parts in order to render a SQL 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']);
Parameters
-
callable
$visitor A function or callable to be executed for each part
-
string[]
$parts The list of query parts to traverse
Returns
$this
triggerBeforeFind() ¶ public
triggerBeforeFind(): void
Trigger the beforeFind event on the query's repository object.
Will not trigger more than once, and only for select queries.
Returns
void
type() ¶ public
type(): string
Returns the type of this query (select, insert, update, delete)
Returns
string
union() ¶ public
union(string|Cake\Database\Query $query, bool $overwrite = false): $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
Parameters
-
string|Cake\Database\Query
$query full SQL query to be used in UNION operator
-
bool
$overwrite optional whether to reset the list of queries to be operated or not
Returns
$this
unionAll() ¶ public
unionAll(string|Cake\Database\Query $query, bool $overwrite = false): $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
Parameters
-
string|Cake\Database\Query
$query full SQL query to be used in UNION operator
-
bool
$overwrite optional whether to reset the list of queries to be operated or not
Returns
$this
update() ¶ public
update(string|Cake\Database\ExpressionInterface $table = null): $this
Create an update query.
This changes the query type to be 'update'. Can be combined with set() and where() methods to create update queries.
Parameters
-
string|Cake\Database\ExpressionInterface
$table optional Unused parameter.
Returns
$this
values() ¶ public
values(array|Cake\Database\Query|Cake\Database\Expression\ValuesExpression $data): $this
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.
Parameters
-
array|Cake\Database\Query|Cake\Database\Expression\ValuesExpression
$data The data to insert.
Returns
$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.
where() ¶ public
where(string|array|Cake\Database\ExpressionInterface|Closure|null $conditions = null, array $types = [], bool $overwrite = false): $this
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]])
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.
Parameters
-
string|array|Cake\Database\ExpressionInterface|Closure|null
$conditions optional The conditions to filter on.
-
array
$types optional associative array of type names used to bind values to query
-
bool
$overwrite optional whether to reset conditions with passed list or not
Returns
$this
whereInList() ¶ public
whereInList(string $field, array $values, array $options = []): $this
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 queryallowEmpty
- Allow empty array.
Parameters
-
string
$field Field
-
array
$values Array of values
-
array
$options optional Options
Returns
$this
whereNotInList() ¶ public
whereNotInList(string $field, array $values, array $options = []): $this
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.
Parameters
-
string
$field Field
-
array
$values Array of values
-
array
$options optional Options
Returns
$this
whereNotNull() ¶ public
whereNotNull(array|string|Cake\Database\ExpressionInterface $fields): $this
Convenience method that adds a NOT NULL condition to the query
Parameters
-
array|string|Cake\Database\ExpressionInterface
$fields A single field or expressions or a list of them that should be not null.
Returns
$this
whereNull() ¶ public
whereNull(array|string|Cake\Database\ExpressionInterface $fields): $this
Convenience method that adds a IS NULL condition to the query
Parameters
-
array|string|Cake\Database\ExpressionInterface
$fields A single field or expressions or a list of them that should be null.
Returns
$this
zip() ¶ public @method
zip(array|Traversable $c): Cake\Collection\CollectionInterface
Returns the first result of both the query and $c in an array, then the second results and so on.
Parameters
-
array|Traversable
$c
Returns
Cake\Collection\CollectionInterface
zipWith() ¶ public @method
zipWith(mixed $collections, callable $callable): Cake\Collection\CollectionInterface
Returns each of the results out of calling $c with the first rows of the query and each of the items, then the second rows and so on.
Parameters
-
$collections
-
callable
$callable
Returns
Cake\Collection\CollectionInterface
Property Detail
$_autoFields ¶ protected
Tracks whether or not the original query should include fields from the top level table.
Type
bool|null
$_beforeFindFired ¶ protected
True if the beforeFind event has already been triggered for this query
Type
bool
$_cache ¶ protected
A query cacher instance if this query has caching enabled.
Type
Cake\Datasource\QueryCacher|null
$_connection ¶ protected
Connection instance to be used to execute this query.
Type
Cake\Database\Connection
$_counter ¶ protected
A callable function that can be used to calculate the total amount of
records this query will match when not using limit
Type
callable|null
$_deleteParts ¶ protected
The list of query clauses to traverse for generating a DELETE statement
Type
string[]
$_dirty ¶ protected
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.
Type
bool
$_eagerLoaded ¶ protected
Whether the query is standalone or the product of an eager load operation.
Type
bool
$_eagerLoader ¶ protected
Instance of a class responsible for storing association containments and for eager loading them when this query is executed
Type
Cake\ORM\EagerLoader|null
$_formatters ¶ protected
List of formatter classes or callbacks that will post-process the results when fetched
Type
callable[]
$_functionsBuilder ¶ protected
Instance of functions builder object used for generating arbitrary SQL functions.
Type
Cake\Database\FunctionsBuilder|null
$_hasFields ¶ protected
Whether the user select any fields before being executed, this is used to determined if any fields should be automatically be selected.
Type
bool|null
$_insertParts ¶ protected
The list of query clauses to traverse for generating an INSERT statement
Type
string[]
$_iterator ¶ protected
Statement object resulting from executing this query.
Type
Cake\Database\StatementInterface|null
$_mapReduce ¶ protected
List of map-reduce routines that should be applied over the query result
Type
array
$_options ¶ protected
Holds any custom options passed using applyOptions that could not be processed by any method in this class.
Type
array
$_repository ¶ public @property
Instance of a table object this query is bound to.
Type
Cake\ORM\Table
$_resultDecorators ¶ protected
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.
Type
callable[]
$_resultsCount ¶ protected
The COUNT(*) for the query.
When set, count query execution will be bypassed.
Type
int|null
$_selectParts ¶ protected
The list of query clauses to traverse for generating a SELECT statement
Type
string[]
$_updateParts ¶ protected
The list of query clauses to traverse for generating an UPDATE statement
Type
string[]
$_useBufferedResults ¶ protected
Boolean for tracking whether or not buffered results are enabled.
Type
bool
$_valueBinder ¶ protected
The object responsible for generating query placeholders and temporarily store values associated to each of those.
Type
Cake\Database\ValueBinder|null