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.

Namespace: Cake\ORM

Constants

int

APPEND ¶
```
0
```
Indicates that the operation should append to the list
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

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

A query cacher instance if this query has caching enabled.
$_connection protected

Cake\Datasource\ConnectionInterface

Connection instance to be used to execute this query.
$_counter protected

callable

A callable function that can be used to calculate the total amount of records this query will match when not using limit
$_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

Instance of a class responsible for storing association containments and for eager loading them when this query is executed
$_formatters protected

array

List of formatter classes or callbacks that will post-process the results when fetched
$_functionsBuilder protected

FunctionsBuilder

Instance of functions builder object used for generating arbitrary SQL functions.
$_hasFields protected

bool

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
$_iterator protected

Cake\Database\StatementInterface

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 protected

Cake\Datasource\RepositoryInterface

Instance of a table object this query is bound to
$_resultDecorators protected

array

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

Cake\Datasource\ResultSetInterface

A ResultSet.
$_resultsCount protected

int

The COUNT(*) for the query.
$_type protected

string

Type of this query (select, insert, update, delete).
$_typeMap protected

Cake\Database\TypeMap
$_useBufferedResults protected

bool

Boolean for tracking whether or not buffered results are enabled.
$_valueBinder protected

ValueBinder

The object responsible for generating query placeholders and temporarily store values associated to each of those.

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.
_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.
_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 himself 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

Connects any previously defined set of conditions to the provided list using the AND operator. This function accepts the conditions list in the same format as the method where does, hence you can use arrays, expression objects callback functions or strings.
applyOptions() public

Populates or adds parts to current query clauses using an array. This is handy for passing all query clauses at once.
autoFields() public

Get/Set whether or not the ORM should automatically append fields.
bind() public

Associates a query placeholder to a value and a type.
bufferResults() public

Enable/Disable buffered results.
cache() public

Enable result caching for this query.
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.
connection() public

Sets the connection instance to be used for executing and transforming this query When called with a null argument, it will return the current connection instance.
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.
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 the count 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.
defaultTypes() public

Allows setting default types when chaining query
delete() public

Create a delete query.
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.
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.
eagerLoader() public

Sets the instance of the eager loader class to use for loading associations and storing containments. If called with no arguments, it will return the currently configured instance.
epilog() public

A string or expression that will be appended to the generated query
execute() public

Compiles the SQL representation of this query and executes it using the configured connection object. Returns the resulting statement object.
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.
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.
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.
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.
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 method where() does. Please refer to its documentation for an insight on how to using each parameter.
hydrate() public

Toggle hydrating entities.
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.
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.
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.
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.
modifier() public

Adds a single or multiple SELECT modifiers to be used in the SELECT.
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.
orHaving() public

Connects any previously defined set of conditions to the provided list using the OR operator in the HAVING clause. This method operates in exactly the same way as the method orWhere() does. Please refer to its documentation for an insight on how to using each parameter.
orWhere() public

Connects any previously defined set of conditions to the provided list using the OR operator. This function accepts the conditions list in the same format as the method where does, hence you can use arrays, expression objects callback functions or strings.
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 an ASC direction.
page() public

Set the page of results you want.
removeJoin() public

Remove a join if it has been defined.
repository() public

Returns the default table object that will be used by this query, that is, the table that will appear in the from clause.
rightJoin() public

Adds a single RIGHT JOIN clause to the query.
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.
set() public

Set one or many fields to update.
setResult() public

Set the result set for a query.
sql() public

Returns the SQL representation of this object.
toArray() public

Returns an array representation of the results after executing the 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.
triggerBeforeFind() public

Trigger the beforeFind event on the query's repository object.
type() public

Returns the type of this query (select, insert, update, delete)
typeMap() public

Creates a new TypeMap if $typeMap is an array, otherwise returns the existing type map or exchanges it for the given one.
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.
valueBinder() public

Returns the currently used ValueBinder instance. If a value is passed, it will be set as the new instance to be used.
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.

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
array $arguments

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

_conjugate() ¶ protected

_conjugate(string $part, string|null|array|ExpressionInterface|callback $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|null|array|ExpressionInterface|callback $append: Expression or builder function 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

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

_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\ORM\ResultSet

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\ORM\ResultSet

_makeJoin() ¶ protected

_makeJoin(string|array $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|array $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

Returns

void

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 himself 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 $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 $alias optional: the alias used to prefix the field

Returns

array

aliasFields() ¶ public

aliasFields(array $fields, string|null $defaultAlias = null): array

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

array

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|ExpressionInterface|callback $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.

Parameters

string|array|ExpressionInterface|callback $conditions: The AND conditions for HAVING.
array $types optional: associative array of type names used to bind values to query

Returns

$this

andWhere() ¶ public

andWhere(string|array|ExpressionInterface|callback $conditions, array $types = []): $this

Connects any previously defined set of conditions to the provided list using the AND operator. This function accepts the conditions list in the same format as the method where does, hence you can use arrays, expression objects callback functions or strings.

It is important to notice that when calling this function, any previous set of conditions defined for this query will be treated as a single argument for the AND operator. This function will not only operate the most recently defined condition, but all the conditions as a whole.

When using an array for defining conditions, creating constraints form each array entry will use the same logic as with the where() function. This means that each array entry will be joined to the other using the AND operator, unless you nest the conditions in the array using other operator.

Examples:

$query->where(['title' => 'Hello World')->andWhere(['author_id' => 1]);

Will produce:

WHERE title = 'Hello World' AND author_id = 1

$query
  ->where(['OR' => ['published' => false, 'published is NULL']])
  ->andWhere(['author_id' => 1, 'comments_count >' => 10])

Produces:

WHERE (published = 0 OR published IS NULL) AND author_id = 1 AND comments_count > 10

$query
  ->where(['title' => 'Foo'])
  ->andWhere(function ($exp, $query) {
    return $exp
      ->add(['author_id' => 1])
      ->or_(['author_id' => 2]);
  });

Generates the following conditions:

WHERE (title = 'Foo') AND (author_id = 1 OR author_id = 2)

Parameters

string|array|ExpressionInterface|callback $conditions: The conditions to add with AND.
array $types optional: associative array of type names used to bind values to query

Returns

$this

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.

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

Returns

$this

autoFields() ¶ public

autoFields(bool|null $value = null): bool|$this

Get/Set 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|null $value optional: The value to set or null to read the current value.

Returns

bool|$this

bind() ¶ public

bind(string|int $param, mixed $value, string|int $type = 'string'): $this

Associates a query placeholder to a value and a type.

If type is expressed as "atype[]" (note braces) then it will cause the placeholder to be re-written dynamically so if the value is an array, it will create as many placeholders as values are in it. For example "string[]" will create several placeholders of type string.

Parameters

string|int $param: placeholder to be replaced with quoted version of $value
mixed $value: The value to be bound
string|int $type optional: the mapped type name, used for casting when sending to database

Returns

$this

bufferResults() ¶ public

bufferResults(bool|null $enable = null): bool|$this

Enable/Disable buffered results.

When enabled the results returned by this Query will be buffered. This enables you to iterate a result set multiple times, or both cache and iterate it.

When disabled it will consume less memory as fetched results are not remembered for future iterations.

If called with no arguments, it will return whether or not buffering is enabled.

Parameters

bool|null $enable optional: whether or not to enable buffering

Returns

bool|$this

cache() ¶ public

cache(false|string|Closure $key, string|Cake\Cache\CacheEngine $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

false|string|Closure $key
string|Cake\Cache\CacheEngine $config optional

Returns

$this

Throws

RuntimeException
When you attempt to cache a non-select query.

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

cleanCopy() ¶ public

cleanCopy(): Cake\ORM\Query

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

Cake\ORM\Query

connection() ¶ public

connection(Cake\Datasource\ConnectionInterface $connection = null): $this|Cake\Datasource\ConnectionInterface

Sets the connection instance to be used for executing and transforming this query When called with a null argument, it will return the current connection instance.

Parameters

Cake\Datasource\ConnectionInterface $connection optional: instance

Returns

$this|Cake\Datasource\ConnectionInterface

contain() ¶ public

contain(array|string $associations = null, bool $override = false): array|$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
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']
  ]
]);

When containing associations, it is important to include foreign key columns. Failing to do so will trigger exceptions.

// Use special join conditions for getting an Articles's belongsTo 'authors'
$query->contain([
  'Authors' => [
    'foreignKey' => false,
    'queryBuilder' => function ($q) {
      return $q->where(...); // Add full filtering conditions
    }
  ]
]);

If called with no arguments, this function will return an array with with the list of previously configured associations to be contained in the result.

If called with an empty first argument and $override is set to true, the previous list will be emptied.

Parameters

array|string $associations optional: list of table aliases to be queried
bool $override optional: whether override previous list with the one passed defaults to merging previous list with the new one.

Returns

array|$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

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(null|callable $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

null|callable $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

defaultTypes() ¶ public

defaultTypes(array $types = null): $this|array

Allows setting default types when chaining query

Parameters

array $types optional: The array of types to set.

Returns

$this|array

delete() ¶ public

delete(string $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 $table optional: Unused parameter.

Returns

$this

distinct() ¶ public

distinct(array|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|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

eagerLoaded() ¶ public

eagerLoaded(bool|null $value = null): $this|Cake\ORM\Query

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|null $value optional: Whether or not to eager load.

Returns

$this|Cake\ORM\Query

eagerLoader() ¶ public

eagerLoader(Cake\ORM\EagerLoader $instance = null): Cake\ORM\EagerLoader|$this

Sets the instance of the eager loader class to use for loading associations and storing containments. If called with no arguments, it will return the currently configured instance.

Parameters

Cake\ORM\EagerLoader $instance optional: The eager loader to use. Pass null to get the current eagerloader.

Returns

Cake\ORM\EagerLoader|$this

epilog() ¶ public

epilog(string|Cake\Database\Expression\QueryExpression $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');

Parameters

string|Cake\Database\Expression\QueryExpression $expression optional: The expression to be appended

Returns

$this

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

find() ¶ public

find(string $finder, array $options = []): $this

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
array $options optional

Returns

$this

first() ¶ public

first(): mixed

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

mixed

firstOrFail() ¶ public

firstOrFail(): mixed

Get the first result from the executing query or raise an exception.

Returns

mixed

Throws

Cake\Datasource\Exception\RecordNotFoundException
When there is no first record.

formatResults() ¶ public

formatResults(callable|null $formatter = null, bool|int $mode = 0): $this|array

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, a ResultSetDecorator, 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 first argument is set to null, it will return the list of previously registered map reduce routines.

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.
bool|int $mode optional: Whether or not to overwrite, append or prepend the formatter.

Returns

$this|array

from() ¶ public

from(array|ExpressionInterface|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|ExpressionInterface|string $tables optional: tables to be added to the list
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

getIterator() ¶ public

getIterator(): Iterator

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

Iterator

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

group() ¶ public

group(array|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');

Parameters

array|ExpressionInterface|string $fields: fields to be added to the list
bool $overwrite optional: whether to reset fields with passed list or not

Returns

$this

having() ¶ public

having(string|array|ExpressionInterface|callback $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.

Parameters

string|array|ExpressionInterface|callback $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

hydrate() ¶ public

hydrate(bool|null $enable = null): bool|$this

Toggle hydrating entities.

If set to false array results will be returned

Parameters

bool|null $enable optional: Use a boolean to set the hydration mode. Null will fetch the current hydration mode.

Returns

bool|$this

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 $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 $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

insert() ¶ public

insert(array $columns, array $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.
array $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

join() ¶ public

join(array|string|null $tables = null, 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 produceINNER 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|null $tables optional: 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

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|array $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|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

leftJoinWith() ¶ public

leftJoinWith(string $assoc, callable $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'])
     ->autoFields(true);

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'])
     ->autoFields(true);

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 $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|ExpressionInterface $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|ExpressionInterface $num: number of records to be returned

Returns

$this

mapReduce() ¶ public

mapReduce(callable|null $mapper = null, callable|null $reducer = null, bool $overwrite = false): $this|array

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 first argument is set to null, it will return the list of previously registered map reduce routines.

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|array

matching() ¶ public

matching(string $assoc, callable $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']);
 );

Parameters

string $assoc: The association to filter by
callable $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

modifier() ¶ public

modifier(array|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|ExpressionInterface|string $modifiers: modifiers to be applied to the query
bool $overwrite optional: whether to reset order with field list or not

Returns

$this

newExpr() ¶ public

newExpr(mixed $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

mixed $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 $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']);
 );

Parameters

string $assoc: The association to filter by
callable $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|ExpressionInterface $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|ExpressionInterface $num: number of records to be skipped

Returns

$this

orHaving() ¶ public

orHaving(string|array|ExpressionInterface|callback $conditions, array $types = []): $this

Connects any previously defined set of conditions to the provided list using the OR operator in the HAVING clause. This method operates in exactly the same way as the method orWhere() does. Please refer to its documentation for an insight on how to using each parameter.

Parameters

string|array|ExpressionInterface|callback $conditions: The OR conditions for HAVING.
array $types optional: associative array of type names used to bind values to query.

Returns

$this

orWhere() ¶ public

orWhere(string|array|ExpressionInterface|callback $conditions, array $types = []): $this

Connects any previously defined set of conditions to the provided list using the OR operator. This function accepts the conditions list in the same format as the method where does, hence you can use arrays, expression objects callback functions or strings.

It is important to notice that when calling this function, any previous set of conditions defined for this query will be treated as a single argument for the OR operator. This function will not only operate the most recently defined condition, but all the conditions as a whole.

When using an array for defining conditions, creating constraints form each array entry will use the same logic as with the where() function. This means that each array entry will be joined to the other using the OR operator, unless you nest the conditions in the array using other operator.

Examples:

$query->where(['title' => 'Hello World')->orWhere(['title' => 'Foo']);

Will produce:

WHERE title = 'Hello World' OR title = 'Foo'

$query
  ->where(['OR' => ['published' => false, 'published is NULL']])
  ->orWhere(['author_id' => 1, 'comments_count >' => 10])

Produces:

WHERE (published = 0 OR published IS NULL) OR (author_id = 1 AND comments_count > 10)

$query
  ->where(['title' => 'Foo'])
  ->orWhere(function ($exp, $query) {
    return $exp
      ->add(['author_id' => 1])
      ->or_(['author_id' => 2]);
  });

Generates the following conditions:

WHERE (title = 'Foo') OR (author_id = 1 OR author_id = 2)

Parameters

string|array|ExpressionInterface|callback $conditions: The conditions to add with OR.
array $types optional: associative array of type names used to bind values to query

Returns

$this

order() ¶ public

order(array|Cake\Database\ExpressionInterface|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|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()

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 an ASC direction.

This method allows you to set complex expressions as order conditions unlike order()

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 $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 should start at 1.

Parameters

int $num: The page number you want.
int $limit optional: The number of rows you want in the page. If null the current limit clause will be used.

Returns

$this

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|null $table = null): Cake\Datasource\RepositoryInterface|$this

Returns the default table object that will be used by this query, that is, the table that will appear in the from clause.

When called with a Table argument, the default table object will be set and this query object will be returned for chaining.

Parameters

Cake\Datasource\RepositoryInterface|null $table optional: The default table object to use

Returns

Cake\Datasource\RepositoryInterface|$this

rightJoin() ¶ public

rightJoin(string|array $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|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

select() ¶ public

select(array|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 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|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

set() ¶ public

set(string|array|callable|QueryExpression $key, mixed $value = null, array $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|callable|QueryExpression $key

The column name or array of keys

values to set. This can also be a QueryExpression containing a SQL fragment. It can also be a callable, that is required to return an expression object.

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 $types optional

The column types to treat data as.

Returns

$this

setResult() ¶ public

setResult(Cake\Datasource\ResultSetInterface $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

Cake\Datasource\ResultSetInterface $results: The results this query should return.

Returns

$this

sql() ¶ public

sql(ValueBinder $binder = 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

ValueBinder $binder optional

Returns

string

toArray() ¶ public

toArray(): array

Returns an array representation of the results after executing the query.

Returns

array

traverse() ¶ public

traverse(callable $visitor, array $parts = []): $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);
     }
 }, ['select', 'from']);

Parameters

callable $visitor: a function or callable to be executed for each part
array $parts optional: the query clauses to traverse

Returns

$this

traverseExpressions() ¶ public

traverseExpressions(callable $callback): $this|null

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|null

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

typeMap() ¶ public

typeMap(array|TypeMap $typeMap = null): $this|TypeMap

Creates a new TypeMap if $typeMap is an array, otherwise returns the existing type map or exchanges it for the given one.

Parameters

array|TypeMap $typeMap optional: Creates a TypeMap if array, otherwise sets the given TypeMap

Returns

$this|TypeMap

union() ¶ public

union(string|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|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|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|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 $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 $table optional: Unused parameter.

Returns

$this

valueBinder() ¶ public

valueBinder(Cake\Database\ValueBinder $binder = null): $this|Cake\Database\ValueBinder

Returns the currently used ValueBinder instance. If a value is passed, it will be set as the new instance to be used.

A ValueBinder is responsible for generating query placeholders and temporarily associate values to those placeholders so that they can be passed correctly statement object.

Parameters

Cake\Database\ValueBinder $binder optional: new instance to be set. If no value is passed the default one will be returned

Returns

$this|Cake\Database\ValueBinder

values() ¶ public

values(array|Query $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|Query $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|callback|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])->type('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|callback|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

array

`$_results` ¶ protected

A ResultSet.

When set, query execution will be bypassed.

Type

Cake\Datasource\ResultSetInterface

`$_resultsCount` ¶ protected

The COUNT(*) for the query.

When set, count query execution will be bypassed.

Type

int

`$_type` ¶ protected

Type of this query (select, insert, update, delete).

Type

string

`$_typeMap` ¶ protected

Constants

Property Summary

Method Summary

__call() public

__clone() public

__construct() public

__debugInfo() public

__toString() public

_addAssociationsToTypeMap() protected

_addDefaultFields() protected

_conjugate() protected

_decorateResults() protected

_decorateStatement() protected

_decoratorClass() protected

_dirty() protected

_execute() protected

_makeJoin() protected

_performCount() protected

_transformQuery() protected

addDefaultTypes() public

aliasField() public

aliasFields() public

all() public

andHaving() public

andWhere() public

applyOptions() public

autoFields() public

bind() public

bufferResults() public

cache() public

clause() public

cleanCopy() public

connection() public

contain() public

count() public

counter() public

decorateResults() public

defaultTypes() public

delete() public

distinct() public

eagerLoaded() public

eagerLoader() public

epilog() public

execute() public

find() public

first() public

firstOrFail() public

formatResults() public

from() public

func() public

getIterator() public

getOptions() public

group() public

having() public

hydrate() public

innerJoin() public

innerJoinWith() public

insert() public

into() public

join() public

jsonSerialize() public

leftJoin() public

leftJoinWith() public

limit() public

mapReduce() public

matching() public

modifier() public

newExpr() public

notMatching() public

offset() public

orHaving() public

orWhere() public

order() public

orderAsc() public

orderDesc() public

page() public

removeJoin() public

repository() public