Fr3nch13/CakePHP Utilities

Query extends Query
in package

Application

implements JsonSerializable, QueryInterface uses QueryTrait

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.

Interfaces

JsonSerializable
QueryInterface: The basis for every query object

Constants

APPEND = 0: Indicates that the operation should append to the list
JOIN_TYPE_INNER = 'INNER'
JOIN_TYPE_LEFT = 'LEFT'
JOIN_TYPE_RIGHT = 'RIGHT'
OVERWRITE = true: Indicates that the operation should overwrite the list
PREPEND = 1: Indicates that the operation should prepend to the list

Properties

$_repository : Table
$_autoFields : bool|null: Tracks whether the original query should include fields from the top level table.
$_beforeFindFired : bool: True if the beforeFind event has already been triggered for this query
$_cache : QueryCacher|null: A query cacher instance if this query has caching enabled.
$_connection : Connection: Connection instance to be used to execute this query.
$_counter : 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 : array<string|int, string>: The list of query clauses to traverse for generating a DELETE statement
$_dirty : 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 : bool: Whether the query is standalone or the product of an eager load operation.
$_eagerLoader : EagerLoader|null: Instance of a class responsible for storing association containments and for eager loading them when this query is executed
$_formatters : array<string|int, callable>: List of formatter classes or callbacks that will post-process the results when fetched
$_functionsBuilder : FunctionsBuilder|null: Instance of functions builder object used for generating arbitrary SQL functions.
$_hasFields : 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 : bool: Whether to hydrate results into entity objects
$_insertParts : array<string|int, string>: The list of query clauses to traverse for generating an INSERT statement
$_iterator : StatementInterface|null: Statement object resulting from executing this query.
$_mapReduce : array<string|int, mixed>: List of map-reduce routines that should be applied over the query result
$_options : array<string|int, mixed>: Holds any custom options passed using applyOptions that could not be processed by any method in this class.
$_parts : array<string, mixed>: List of SQL parts that will be used to build this query.
$_repository : RepositoryInterface: Instance of a table object this query is bound to
$_resultDecorators : array<string|int, 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 : iterable<string|int, mixed>|null: A ResultSet.
$_resultsCount : int|null: The COUNT(*) for the query.
$_selectParts : array<string|int, string>: The list of query clauses to traverse for generating a SELECT statement
$_selectTypeMap : TypeMap|null: The Type map for fields in the select clause
$_type : string: Type of this query (select, insert, update, delete).
$_typeMap : TypeMap|null
$_updateParts : array<string|int, string>: The list of query clauses to traverse for generating an UPDATE statement
$_useBufferedResults : bool: Boolean for tracking whether buffered results are enabled.
$_valueBinder : ValueBinder|null: The object responsible for generating query placeholders and temporarily store values associated to each of those.
$aliasingEnabled : bool: Whether aliases are generated for fields.
$typeCastEnabled : bool: Tracking flag to disable casting

Methods

__call() : mixed: {@inheritDoc}
__clone() : void: Handles clearing iterator and cloning all expressions and value binders.
__construct() : mixed: Constructor
__debugInfo() : array<string, mixed>: Returns an array that can be used to describe the internal state of this object.
__toString() : string: Returns string representation of this query (complete SQL statement).
addDefaultTypes() : $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.
aliasField() : array<string, string>: Returns a key => value array representing a single aliased field that can be passed directly to the select() method.
aliasFields() : array<string, string>: Runs `aliasField()` for each field in the provided list and returns the result under a single array.
all() : ResultSetInterface: Fetch the results for this query.
andHaving() : $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.
andWhere() : $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.
append() : CollectionInterface
applyOptions() : $this: Populates or adds parts to current query clauses using an array.
bind() : $this: Associates a query placeholder to a value and a type.
cache() : $this: {@inheritDoc}
chunk() : CollectionInterface
clause() : 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.
cleanCopy() : static: Creates a copy of this current query, triggers beforeFind and resets some state.
clearContain() : $this: Clears the contained associations from the current query.
clearResult() : $this: Clears the internal result cache and the internal count value from the current query object.
combine() : CollectionInterface
contain() : $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.
count() : int: Returns the total amount of results for the query.
countBy() : CollectionInterface
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.
decorateResults() : $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.
delete() : $this: Create a delete query.
disableAutoFields() : $this: Disables automatically appending fields.
disableBufferedResults() : $this: Disables buffered results.
disableHydration() : $this: Disable hydrating entities.
disableResultsCasting() : $this: Disables result casting.
distinct() : $this: Adds a `DISTINCT` clause to the query to remove duplicates from the result set.
each() : CollectionInterface
eagerLoaded() : $this: Sets the query instance to be an eager loaded query. If no argument is passed, the current configured query `_eagerLoaded` value is returned.
enableAutoFields() : $this: Sets whether the ORM should automatically append fields.
enableBufferedResults() : $this: Enables/Disables buffered results.
enableHydration() : $this: Toggle hydrating entities.
enableResultsCasting() : $this: Enables result casting.
epilog() : $this: A string or expression that will be appended to the generated query
every() : bool
execute() : StatementInterface: Compiles the SQL representation of this query and executes it using the configured connection object. Returns the resulting statement object.
expr() : 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.
extract() : CollectionInterface
filter() : CollectionInterface
find() : static: Apply custom finds to against an existing query object.
first() : EntityInterface|array<string|int, mixed>|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.
firstOrFail() : EntityInterface|array<string|int, mixed>: Get the first result from the executing query or raise an exception.
formatResults() : $this: Registers a new formatter callback function that is to be executed when trying to fetch the results from the database.
from() : $this: Adds a single or multiple tables to be used in the FROM clause for this query.
func() : FunctionsBuilder: Returns an instance of a functions builder object that can be used for generating arbitrary SQL functions.
getConnection() : Connection: Gets the connection instance to be used for executing and transforming this query.
getContain() : array<string|int, mixed>
getDefaultTypes() : array<int|string, string>: Gets default types of current type map.
getEagerLoader() : EagerLoader: Returns the currently configured instance.
getIterator() : 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.
getMapReducers() : array<string|int, mixed>: Returns the list of previously registered map reduce routines.
getOptions() : array<string|int, mixed>: 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() : RepositoryInterface: Returns the default table object that will be used by this query, that is, the table that will appear in the from clause.
getRepository() : Table: Returns the default repository object that will be used by this query, that is, the repository that will appear in the from clause.
getResultFormatters() : array<string|int, callable>: Returns the list of previously registered format routines.
getSelectTypeMap() : TypeMap: Gets the TypeMap class where the types for each of the fields in the select clause are stored.
getTypeMap() : TypeMap: Returns the existing type map.
getValueBinder() : ValueBinder: Returns the currently used ValueBinder instance.
group() : $this: Adds a single or multiple fields to be used in the GROUP BY clause for this query.
groupBy() : CollectionInterface
having() : $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.
identifier() : 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.
indexBy() : CollectionInterface
innerJoin() : $this: Adds a single `INNER JOIN` clause to the query.
innerJoinWith() : $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.
insert() : $this: Create an insert query.
into() : $this: Set the table name for insert queries.
isAutoFieldsEnabled() : bool|null: Gets whether the ORM should automatically append fields.
isBufferedResultsEnabled() : bool: Returns whether buffered results are enabled/disabled.
isEagerLoaded() : bool: Returns the current configured query `_eagerLoaded` value
isEmpty() : bool
isHydrationEnabled() : bool: Returns the current hydration mode.
isResultsCastingEnabled() : bool: Returns whether result casting is enabled/disabled.
join() : $this: Adds a single or multiple tables to be used as JOIN clauses to this query.
jsonSerialize() : ResultSetInterface: Executes the query and converts the result set into JSON.
last() : mixed
leftJoin() : $this: Adds a single `LEFT JOIN` clause to the query.
leftJoinWith() : $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.
limit() : $this: Sets the number of records that should be retrieved from database, accepts an integer or an expression object that evaluates to an integer.
map() : CollectionInterface
mapReduce() : $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.
matching() : $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.
max() : mixed
min() : mixed
modifier() : $this: Adds a single or multiple `SELECT` modifiers to be used in the `SELECT`.
nest() : CollectionInterface
newExpr() : 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.
notMatching() : $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.
offset() : $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.
order() : $this: Adds a single or multiple fields to be used in the ORDER clause for this query.
orderAsc() : $this: Add an ORDER BY clause with an ASC direction.
orderDesc() : $this: Add an ORDER BY clause with a DESC direction.
page() : $this: Set the page of results you want.
reduce() : mixed
reject() : CollectionInterface
removeJoin() : $this: Remove a join if it has been defined.
repository() : $this: Set the default Table object that will be used by this query and form the `FROM` clause.
rightJoin() : $this: Adds a single `RIGHT JOIN` clause to the query.
rowCountAndClose() : int: Executes the SQL of this query and immediately closes the statement before returning the row count of records changed.
sample() : CollectionInterface
select() : $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.
selectAllExcept() : $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.
set() : $this: Set one or many fields to update.
setConnection() : $this: Sets the connection instance to be used for executing and transforming this query.
setDefaultTypes() : $this: Overwrite the default type mappings for fields in the implementing object.
setEagerLoader() : $this: Sets the instance of the eager loader class to use for loading associations and storing containments.
setResult() : $this: Set the result set for a query.
setSelectTypeMap() : $this: Sets the TypeMap class where the types for each of the fields in the select clause are stored.
setTypeMap() : $this: Creates a new TypeMap if $typeMap is an array, otherwise exchanges it for the given one.
setValueBinder() : $this: Overwrite the current value binder
shuffle() : CollectionInterface
skip() : CollectionInterface
some() : bool
sortBy() : CollectionInterface
sql() : string: Returns the SQL representation of this object.
stopWhen() : CollectionInterface
subquery() : static: Returns a new Query that has automatic field aliasing disabled.
sumOf() : float
take() : CollectionInterface
toArray() : array<string|int, mixed>: Returns an array representation of the results after executing the query.
toArray() : array<string|int, mixed>: Returns an array representation of the results after executing the query.
toList() : array<string|int, mixed>
traverse() : $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.
traverseExpressions() : $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.
traverseParts() : $this: Will iterate over the provided parts.
triggerBeforeFind() : void: Trigger the beforeFind event on the query's repository object.
type() : string: Returns the type of this query (select, insert, update, delete)
union() : $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.
unionAll() : $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.
update() : $this: Create an update query.
values() : $this: Set the values for an insert query.
where() : $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.
whereInList() : $this: Adds an IN condition or set of conditions to be used in the WHERE clause for this query.
whereNotInList() : $this: Adds a NOT IN condition or set of conditions to be used in the WHERE clause for this query.
whereNotInListOrNull() : $this: Adds a NOT IN condition or set of conditions to be used in the WHERE clause for this query. This also allows the field to be null with a IS NULL condition since the null value would cause the NOT IN condition to always fail.
whereNotNull() : $this: Convenience method that adds a NOT NULL condition to the query
whereNull() : $this: Convenience method that adds a IS NULL condition to the query
window() : $this: Adds a named window expression.
with() : $this: Adds a new common table expression (CTE) to the query.
zip() : CollectionInterface
zipWith() : CollectionInterface
_addAssociationsToTypeMap() : void: Used to recursively add contained association column types to the query.
_addDefaultFields() : void: Inspects if there are any set fields for selecting, otherwise adds all the fields for the default table.
_addDefaultSelectTypes() : void: Sets the default types for converting the fields in the select clause
_conjugate() : void: Helper function used to build conditions by composing QueryExpression objects.
_decorateResults() : ResultSetInterface: Decorates the results iterator with MapReduce routines and formatters
_decorateStatement() : CallbackStatement|StatementInterface: Auxiliary function used to wrap the original statement from the driver with any registered callbacks.
_decoratorClass() : string: Returns the name of the class to be used for decorating results
_dirty() : void: Marks a query as dirty, removing any preprocessed information from in memory caching such as previous results
_execute() : ResultSetInterface: Executes this query and returns a ResultSet object containing the results.
_expressionsVisitor() : void: Query parts traversal method used by traverseExpressions()
_makeJoin() : array<string|int, mixed>: Returns an array that can be passed to the join method describing a single join clause
_performCount() : int: Performs and returns the COUNT(*) for the query.
_transformQuery() : void: Applies some defaults to the query object before it is executed.

APPEND

Indicates that the operation should append to the list


    public
        int
    APPEND
    = 0

JOIN_TYPE_INNER


    public
        string
    JOIN_TYPE_INNER
    = 'INNER'

JOIN_TYPE_LEFT


    public
        string
    JOIN_TYPE_LEFT
    = 'LEFT'

JOIN_TYPE_RIGHT


    public
        string
    JOIN_TYPE_RIGHT
    = 'RIGHT'

OVERWRITE

Indicates that the operation should overwrite the list


    public
        bool
    OVERWRITE
    = true

PREPEND

Indicates that the operation should prepend to the list


    public
        int
    PREPEND
    = 1

$_repository


    public
        Table
    $_repository

Instance of a table object this query is bound to.

$_autoFields

Tracks whether the original query should include fields from the top level table.


    protected
        bool|null
    $_autoFields

$_beforeFindFired

True if the beforeFind event has already been triggered for this query


    protected
        bool
    $_beforeFindFired
     = false

$_cache

A query cacher instance if this query has caching enabled.


    protected
        QueryCacher|null
    $_cache

$_connection

Connection instance to be used to execute this query.


    protected
        Connection
    $_connection

$_counter

A callable function that can be used to calculate the total amount of records this query will match when not using `limit`


    protected
        callable|null
    $_counter

$_deleteParts

The list of query clauses to traverse for generating a DELETE statement

This property is unused.


    protected
        array<string|int, string>
    $_deleteParts
     = ['with', 'delete', 'modifier', 'from', 'where', 'epilog']

$_dirty

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.


    protected
        bool
    $_dirty
     = false

$_eagerLoaded

Whether the query is standalone or the product of an eager load operation.


    protected
        bool
    $_eagerLoaded
     = false

$_eagerLoader

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


    protected
        EagerLoader|null
    $_eagerLoader

$_formatters

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


    protected
        array<string|int, callable>
    $_formatters
     = []

$_functionsBuilder

Instance of functions builder object used for generating arbitrary SQL functions.


    protected
        FunctionsBuilder|null
    $_functionsBuilder

$_hasFields

Whether the user select any fields before being executed, this is used to determined if any fields should be automatically be selected.


    protected
        bool|null
    $_hasFields

$_hydrate

Whether to hydrate results into entity objects


    protected
        bool
    $_hydrate
     = true

$_insertParts

The list of query clauses to traverse for generating an INSERT statement

This property is unused.


    protected
        array<string|int, string>
    $_insertParts
     = ['with', 'insert', 'values', 'epilog']

$_iterator

Statement object resulting from executing this query.


    protected
        StatementInterface|null
    $_iterator

$_mapReduce

List of map-reduce routines that should be applied over the query result


    protected
        array<string|int, mixed>
    $_mapReduce
     = []

$_options

Holds any custom options passed using applyOptions that could not be processed by any method in this class.


    protected
        array<string|int, mixed>
    $_options
     = []

$_parts

List of SQL parts that will be used to build this query.


    protected
        array<string, mixed>
    $_parts
     = ['delete' => true, 'update' => [], 'set' => [], 'insert' => [], 'values' => [], 'with' => [], 'select' => [], 'distinct' => false, 'modifier' => [], 'from' => [], 'join' => [], 'where' => null, 'group' => [], 'having' => null, 'window' => [], 'order' => null, 'limit' => null, 'offset' => null, 'union' => [], 'epilog' => null]

$_repository

Instance of a table object this query is bound to


    protected
        RepositoryInterface
    $_repository

$_resultDecorators

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.


    protected
        array<string|int, callable>
    $_resultDecorators
     = []

$_results

A ResultSet.


    protected
        iterable<string|int, mixed>|null
    $_results

When set, query execution will be bypassed.

$_resultsCount

The COUNT(*) for the query.


    protected
        int|null
    $_resultsCount

When set, count query execution will be bypassed.

$_selectParts

The list of query clauses to traverse for generating a SELECT statement

This property is unused.


    protected
        array<string|int, string>
    $_selectParts
     = ['with', 'select', 'from', 'join', 'where', 'group', 'having', 'order', 'limit', 'offset', 'union', 'epilog']

$_selectTypeMap

The Type map for fields in the select clause


    protected
        TypeMap|null
    $_selectTypeMap

$_type

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


    protected
        string
    $_type
     = 'select'

$_typeMap


    protected
        TypeMap|null
    $_typeMap

$_updateParts

The list of query clauses to traverse for generating an UPDATE statement

This property is unused.


    protected
        array<string|int, string>
    $_updateParts
     = ['with', 'update', 'set', 'where', 'epilog']

$_useBufferedResults

Boolean for tracking whether buffered results are enabled.


    protected
        bool
    $_useBufferedResults
     = true

$_valueBinder

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


    protected
        ValueBinder|null
    $_valueBinder

$aliasingEnabled

Whether aliases are generated for fields.


    protected
        bool
    $aliasingEnabled
     = true

$typeCastEnabled

Tracking flag to disable casting


    protected
        bool
    $typeCastEnabled
     = true

__call()

{@inheritDoc}


    public
                    __call(string $method, array<string|int, mixed> $arguments) : mixed

Parameters

$method : string: the method to call
$arguments : array<string|int, mixed>: list of arguments for the method to call

__clone()

Handles clearing iterator and cloning all expressions and value binders.


    public
                    __clone() : void

Handles cloning eager loaders.

__construct()

Constructor


    public
                    __construct(Connection $connection, Table $table) : mixed

Parameters

$connection : Connection: The connection object
$table : Table: The table this query is starting on

__debugInfo()

Returns an array that can be used to describe the internal state of this object.


    public
                    __debugInfo() : array<string, mixed>

Return values

array<string, mixed>

__toString()

Returns string representation of this query (complete SQL statement).


    public
                    __toString() : string

Return values

string

addDefaultTypes()

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.


    public
                    addDefaultTypes(Table $table) : $this

This method returns the same query object for chaining.

Parameters

$table : Table: The table to pull types from

Return values

$this

aliasField()

Returns a key => value array representing a single aliased field that can be passed directly to the select() method.


    public
                    aliasField(string $field[, string|null $alias = null ]) : array<string, string>

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

$field : string: The field to alias
$alias : string|null = null: the alias used to prefix the field

Return values

array<string, string>

aliasFields()

Runs `aliasField()` for each field in the provided list and returns the result under a single array.


    public
                    aliasFields(array<string|int, mixed> $fields[, string|null $defaultAlias = null ]) : array<string, string>

Parameters

$fields : array<string|int, mixed>: The fields to alias
$defaultAlias : string|null = null: The default alias

Return values

array<string, string>

all()

Fetch the results for this query.


    public
                    all() : ResultSetInterface

Return values

ResultSetInterface

andHaving()

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.


    public
                    andHaving(ExpressionInterface|Closure|array<string|int, mixed>|string $conditions[, array<string, string> $types = [] ]) : $this

Having fields are not suitable for use with user supplied data as they are not sanitized by the query builder.

Parameters

$conditions : ExpressionInterface|Closure|array<string|int, mixed>|string: The AND conditions for HAVING.
$types : array<string, string> = []: Associative array of type names used to bind values to query

Return values

$this

andWhere()

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.


    public
                    andWhere(ExpressionInterface|Closure|array<string|int, mixed>|string $conditions[, array<string, string> $types = [] ]) : $this

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

$conditions : ExpressionInterface|Closure|array<string|int, mixed>|string: The conditions to add with AND.
$types : array<string, string> = []: Associative array of type names used to bind values to query

Return values

$this

append()


    public
                    append([array<string|int, mixed>|Traversable $items =  ]) : CollectionInterface

Appends more rows to the result of the query.

Parameters

$items : array<string|int, mixed>|Traversable =

Return values

CollectionInterface

applyOptions()

Populates or adds parts to current query clauses using an array.


    public
                    applyOptions(array<string, mixed> $options) : $this

This is handy for passing all query clauses at once.

The method accepts the following query clause related options:

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

All other options will not affect the query, but will be stored as custom options that can be read via getOptions(). Furthermore they are automatically passed to Model.beforeFind.

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)

Custom options can be read via getOptions():

$query->applyOptions([
  'fields' => ['id', 'name'],
  'custom' => 'value',
]);

Here $options will hold ['custom' => 'value'] (the fields option will be applied to the query instead of being stored, as it's a query clause related option):

$options = $query->getOptions();

Parameters

$options : array<string, mixed>: The options to be applied

Return values

$this

bind()

Associates a query placeholder to a value and a type.


    public
                    bind(string|int $param, mixed $value[, string|int|null $type = null ]) : $this

$query->bind(':id', 1, 'integer');

Parameters

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

Return values

$this

cache()

{@inheritDoc}


    public
                    cache(Closure|string|false $key[, CacheEngine|string $config = 'default' ]) : $this

Parameters

$key : Closure|string|false: 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.
$config : CacheEngine|string = 'default': Either the name of the cache config to use, or a cache config instance.

Return values

$this

chunk()


    public
                    chunk([int $size =  ]) : CollectionInterface

Groups the results in arrays of $size rows each.

Parameters

$size : int =

Return values

CollectionInterface

clause()

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.


    public
                    clause(string $name) : mixed

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

$name : string: name of the clause to be returned

cleanCopy()

Creates a copy of this current query, triggers beforeFind and resets some state.


    public
                    cleanCopy() : static

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.

Return values

static

clearContain()

Clears the contained associations from the current query.


    public
                    clearContain() : $this

Return values

$this

clearResult()

Clears the internal result cache and the internal count value from the current query object.


    public
                    clearResult() : $this

Return values

$this

combine()


    public
                    combine([mixed $k =  ][, mixed $v =  ][, mixed $g = null ]) : CollectionInterface

Returns the values of the column $v index by column $k, and grouped by $g.

Parameters

$k : mixed =
$v : mixed =
$g : mixed = null

Return values

CollectionInterface

contain()

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.


    public
                    contain(array<string|int, mixed>|string $associations[, callable|bool $override = false ]) : $this

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

$associations : array<string|int, mixed>|string: List of table aliases to be queried.
$override : callable|bool = false: 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.

Return values

$this

count()

Returns the total amount of results for the query.


    public
                    count() : int

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

Return values

int

countBy()


    public
                    countBy([callable|string $field =  ]) : CollectionInterface

Returns the number of unique values for a column

Parameters

$field : callable|string =

Return values

CollectionInterface

counter()

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.


    public
                    counter(callable|null $counter) : $this

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

$counter : callable|null: The counter value

Return values

$this

decorateResults()

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.


    public
                    decorateResults(callable|null $callback[, bool $overwrite = false ]) : $this

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

$callback : callable|null: The callback to invoke when results are fetched.
$overwrite : bool = false: Whether this should append or replace all existing decorators.

Return values

$this

delete()

Create a delete query.


    public
                    delete([string|null $table = null ]) : $this

This changes the query type to be 'delete'. Can be combined with the where() method to create delete queries.

Parameters

$table : string|null = null: Unused parameter.

Return values

$this

disableAutoFields()

Disables automatically appending fields.


    public
                    disableAutoFields() : $this

Return values

$this

disableBufferedResults()

Disables buffered results.


    public
                    disableBufferedResults() : $this

Disabling buffering will consume less memory as fetched results are not remembered for future iterations.

Return values

$this

disableHydration()

Disable hydrating entities.


    public
                    disableHydration() : $this

Disabling hydration will cause array results to be returned for the query instead of entities.

Return values

$this

disableResultsCasting()

Disables result casting.


    public
                    disableResultsCasting() : $this

When disabled, the fields will be returned as received from the database driver (which in most environments means they are being returned as strings), which can improve performance with larger datasets.

Return values

$this

distinct()

Adds a `DISTINCT` clause to the query to remove duplicates from the result set.


    public
                    distinct([ExpressionInterface|array<string|int, mixed>|string|bool $on = [] ][, bool $overwrite = false ]) : $this

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

$on : ExpressionInterface|array<string|int, mixed>|string|bool = []: Enable/disable distinct class or list of fields to be filtered on
$overwrite : bool = false: whether to reset fields with passed list or not

Return values

$this

each()


    public
                    each([callable $c =  ]) : CollectionInterface

Passes each of the query results to the callable

Parameters

$c : callable =

Return values

CollectionInterface

eagerLoaded()

Sets the query instance to be an eager loaded query. If no argument is passed, the current configured query `_eagerLoaded` value is returned.


    public
                    eagerLoaded(bool $value) : $this

Parameters

$value : bool: Whether to eager load.

Return values

$this

enableAutoFields()

Sets whether the ORM should automatically append fields.


    public
                    enableAutoFields([bool $value = true ]) : $this

By default calling select() will disable auto-fields. You can re-enable auto-fields with this method.

Parameters

$value : bool = true: Set true to enable, false to disable.

Return values

$this

enableBufferedResults()

Enables/Disables buffered results.


    public
                    enableBufferedResults([bool $enable = true ]) : $this

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

$enable : bool = true: Whether to enable buffering

Return values

$this

enableHydration()

Toggle hydrating entities.


    public
                    enableHydration([bool $enable = true ]) : $this

If set to false array results will be returned for the query.

Parameters

$enable : bool = true: Use a boolean to set the hydration mode.

Return values

$this

enableResultsCasting()

Enables result casting.


    public
                    enableResultsCasting() : $this

When enabled, the fields in the results returned by this Query will be cast to their corresponding PHP data type.

Return values

$this

epilog()

A string or expression that will be appended to the generated query


    public
                    epilog([ExpressionInterface|string|null $expression = null ]) : $this

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

$expression : ExpressionInterface|string|null = null: The expression to be appended

Return values

$this

every()


    public
                    every([callable $c =  ]) : bool

Returns true if all the results pass the callable test

Parameters

$c : callable =

Return values

bool

execute()

Compiles the SQL representation of this query and executes it using the configured connection object. Returns the resulting statement object.


    public
                    execute() : StatementInterface

Executing a query internally executes several steps, the first one is letting the connection transform this object to fit its particular dialect, this might result in generating a different Query object that will be the one to actually be executed. Immediately after, literal values are passed to the connection so they are bound to the query in a safe way. Finally, the resulting statement is decorated with custom objects to execute callbacks for each row retrieved if necessary.

Resulting statement is traversable, so it can be used in any loop as you would with an array.

This method can be overridden in query subclasses to decorate behavior around query execution.

Return values

StatementInterface

expr()

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.


    public
                    expr([ExpressionInterface|array<string|int, mixed>|string|null $rawExpression = null ]) : QueryExpression

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->expr(); // Returns an empty expression object
$expression = $query->expr('Table.column = Table2.column'); // Return a raw SQL expression

Parameters

$rawExpression : ExpressionInterface|array<string|int, mixed>|string|null = null: A string, array or anything you want wrapped in an expression object

Return values

QueryExpression

extract()


    public
                    extract([mixed $field =  ]) : CollectionInterface

Extracts a single column from each row

Parameters

$field : mixed =

Return values

CollectionInterface

filter()


    public
                    filter([callable $c = null ]) : CollectionInterface

Keeps the results using passing the callable test

Parameters

$c : callable = null

Return values

CollectionInterface

find()

Apply custom finds to against an existing query object.


    public
                    find(string $finder[, array<string, mixed> $options = [] ]) : static

Parameters

$finder : string: The finder method to use.
$options : array<string, mixed> = []: The options for the finder.

Return values

static —

Returns a modified query.

first()

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.


    public
                    first() : EntityInterface|array<string|int, mixed>|null

Example:

$singleUser = $query->select(['id', 'username'])->first();

Return values

EntityInterface|array<string|int, mixed>|null —

The first result from the ResultSet.

firstOrFail()

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


    public
                    firstOrFail() : EntityInterface|array<string|int, mixed>

Return values

EntityInterface|array<string|int, mixed> —

The first result from the ResultSet.

formatResults()

Registers a new formatter callback function that is to be executed when trying to fetch the results from the database.


    public
                    formatResults([callable|null $formatter = null ][, int|bool $mode = self::APPEND ]) : $this

If the second argument is set to true, it will erase previous formatters and replace them with the passed first argument.

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.

Formatting callbacks will receive two arguments, the first one being an object implementing \Cake\Collection\CollectionInterface, that can be traversed and modified at will. The second one being the query instance on which the formatter callback is being applied.

Usually the query instance received by the formatter callback is the same query instance on which the callback was attached to, except for in a joined association, in that case the callback will be invoked on the association source side query, and it will receive that query instance instead of the one on which the callback was originally attached to - see the examples below!

Examples:

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;
    });
});

Add a new column to the results with respect to the query's hydration configuration:

$query->formatResults(function ($results, $query) {
    return $results->map(function ($row) use ($query) {
        $data = [
            'bar' => 'baz',
        ];

        if ($query->isHydrationEnabled()) {
            $row['foo'] = new Foo($data)
        } else {
            $row['foo'] = $data;
        }

        return $row;
    });
});

Retaining access to the association target query instance of joined associations, by inheriting the contain callback's query argument:

// Assuming a `Articles belongsTo Authors` association that uses the join strategy

$articlesQuery->contain('Authors', function ($authorsQuery) {
    return $authorsQuery->formatResults(function ($results, $query) use ($authorsQuery) {
        // Here `$authorsQuery` will always be the instance
        // where the callback was attached to.

        // The instance passed to the callback in the second
        // argument (`$query`), will be the one where the
        // callback is actually being applied to, in this
        // example that would be `$articlesQuery`.

        // ...

        return $results;
    });
});

Parameters

$formatter : callable|null = null: The formatting callable.
$mode : int|bool = self::APPEND: Whether to overwrite, append or prepend the formatter.

Return values

$this

from()

Adds a single or multiple tables to be used in the FROM clause for this query.


    public
                    from([array<string|int, mixed>|string $tables = [] ][, bool $overwrite = false ]) : $this

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

$tables : array<string|int, mixed>|string = []: 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.
$overwrite : bool = false: whether to reset tables with passed list or not

Return values

$this

func()

Returns an instance of a functions builder object that can be used for generating arbitrary SQL functions.


    public
                    func() : FunctionsBuilder

Example:

$query->func()->count('*');
$query->func()->dateDiff(['2012-01-05', '2012-01-02'])

Return values

FunctionsBuilder

getConnection()

Gets the connection instance to be used for executing and transforming this query.


    public
                    getConnection() : Connection

Return values

Connection

getContain()


    public
                    getContain() : array<string|int, mixed>

Return values

array<string|int, mixed>

getDefaultTypes()

Gets default types of current type map.


    public
                    getDefaultTypes() : array<int|string, string>

Return values

array<int|string, string>

getEagerLoader()

Returns the currently configured instance.


    public
                    getEagerLoader() : EagerLoader

Return values

EagerLoader

getIterator()

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.


    public
                    getIterator() : ResultSetInterface

Return values

ResultSetInterface

getMapReducers()

Returns the list of previously registered map reduce routines.


    public
                    getMapReducers() : array<string|int, mixed>

Return values

array<string|int, mixed>

getOptions()

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.


    public
                    getOptions() : array<string|int, mixed>

Example:

 $query->applyOptions(['doABarrelRoll' => true, 'fields' => ['id', 'name']);
 $query->getOptions(); // Returns ['doABarrelRoll' => true]

Return values

array<string|int, mixed>

getRepository()

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


    public
                    getRepository() : RepositoryInterface

Return values

RepositoryInterface

getRepository()

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


    public
                    getRepository() : Table

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

Return values

Table

getResultFormatters()

Returns the list of previously registered format routines.


    public
                    getResultFormatters() : array<string|int, callable>

Return values

array<string|int, callable>

getSelectTypeMap()

Gets the TypeMap class where the types for each of the fields in the select clause are stored.


    public
                    getSelectTypeMap() : TypeMap

Return values

TypeMap

getTypeMap()

Returns the existing type map.


    public
                    getTypeMap() : TypeMap

Return values

TypeMap

getValueBinder()

Returns the currently used ValueBinder instance.


    public
                    getValueBinder() : ValueBinder

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

Return values

ValueBinder

group()

Adds a single or multiple fields to be used in the GROUP BY clause for this query.


    public
                    group(ExpressionInterface|array<string|int, mixed>|string $fields[, bool $overwrite = false ]) : $this

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

$fields : ExpressionInterface|array<string|int, mixed>|string: fields to be added to the list
$overwrite : bool = false: whether to reset fields with passed list or not

Return values

$this

groupBy()


    public
                    groupBy([callable|string $field =  ]) : CollectionInterface

In-memory group all results by the value of a column.

Parameters

$field : callable|string =

Return values

CollectionInterface

having()

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.


    public
                    having([ExpressionInterface|Closure|array<string|int, mixed>|string|null $conditions = null ][, array<string, string> $types = [] ][, bool $overwrite = false ]) : $this

Having fields are not suitable for use with user supplied data as they are not sanitized by the query builder.

Parameters

$conditions : ExpressionInterface|Closure|array<string|int, mixed>|string|null = null: The having conditions.
$types : array<string, string> = []: Associative array of type names used to bind values to query
$overwrite : bool = false: whether to reset conditions with passed list or not

Return values

$this

identifier()

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.


    public
                    identifier(string $identifier) : ExpressionInterface

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

$identifier : string: The identifier for an expression

Return values

ExpressionInterface

indexBy()


    public
                    indexBy([callable|string $callback =  ]) : CollectionInterface

Returns the results indexed by the value of a column.

Parameters

$callback : callable|string =

Return values

CollectionInterface

innerJoin()

Adds a single `INNER JOIN` clause to the query.


    public
                    innerJoin(array<string, mixed>|string $table[, ExpressionInterface|array<string|int, mixed>|string $conditions = [] ][, array<string, string> $types = [] ]) : $this

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 method's description for further details.

Parameters

$table : array<string, mixed>|string: The table to join with
$conditions : ExpressionInterface|array<string|int, mixed>|string = []: The conditions to use for joining.
$types : array<string, string> = []: a list of types associated to the conditions used for converting values to the corresponding database representation.

Return values

$this

innerJoinWith()

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.


    public
                    innerJoinWith(string $assoc[, callable|null $builder = null ]) : $this

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

$assoc : string: The association to join with
$builder : callable|null = null: a function that will receive a pre-made query object that can be used to add custom conditions or selecting some fields

Return values

$this

insert()

Create an insert query.


    public
                    insert(array<string|int, mixed> $columns[, array<string|int, string> $types = [] ]) : $this

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

$columns : array<string|int, mixed>: The columns to insert into.
$types : array<string|int, string> = []: A map between columns & their datatypes.

Return values

$this

into()

Set the table name for insert queries.


    public
                    into(string $table) : $this

Parameters

$table : string: The table name to insert into.

Return values

$this

isAutoFieldsEnabled()

Gets whether the ORM should automatically append fields.


    public
                    isAutoFieldsEnabled() : bool|null

By default calling select() will disable auto-fields. You can re-enable auto-fields with enableAutoFields().

Return values

bool|null —

The current value. Returns null if neither enabled or disabled yet.

isBufferedResultsEnabled()

Returns whether buffered results are enabled/disabled.


    public
                    isBufferedResultsEnabled() : bool

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

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

Return values

bool

isEagerLoaded()

Returns the current configured query `_eagerLoaded` value


    public
                    isEagerLoaded() : bool

Return values

bool

isEmpty()


    public
                    isEmpty() : bool

Returns true if this query found no results.

Return values

bool

isHydrationEnabled()

Returns the current hydration mode.


    public
                    isHydrationEnabled() : bool

Return values

bool

isResultsCastingEnabled()

Returns whether result casting is enabled/disabled.


    public
                    isResultsCastingEnabled() : bool

When enabled, the fields in the results returned by this Query will be casted to their corresponding PHP data type.

Return values

bool

join()

Adds a single or multiple tables to be used as JOIN clauses to this query.


    public
                    join(array<string, mixed>|string $tables[, array<string, string> $types = [] ][, bool $overwrite = false ]) : $this

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

$tables : array<string, mixed>|string: list of tables to be joined in the query
$types : array<string, string> = []: Associative array of type names used to bind values to query
$overwrite : bool = false: whether to reset joins with passed list or not

Return values

$this

jsonSerialize()

Executes the query and converts the result set into JSON.


    public
                    jsonSerialize() : ResultSetInterface

Part of JsonSerializable interface.

Return values

ResultSetInterface —

The data to convert to JSON.

last()


    public
                    last() : mixed

Return the last row of the query result

leftJoin()

Adds a single `LEFT JOIN` clause to the query.


    public
                    leftJoin(array<string, mixed>|string $table[, ExpressionInterface|array<string|int, mixed>|string $conditions = [] ][, array<string|int, mixed> $types = [] ]) : $this

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

$table : array<string, mixed>|string: The table to join with
$conditions : ExpressionInterface|array<string|int, mixed>|string = []: The conditions to use for joining.
$types : array<string|int, mixed> = []: a list of types associated to the conditions used for converting values to the corresponding database representation.

Return values

$this

leftJoinWith()

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.


    public
                    leftJoinWith(string $assoc[, callable|null $builder = null ]) : $this

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

$assoc : string: The association to join with
$builder : callable|null = null: a function that will receive a pre-made query object that can be used to add custom conditions or selecting some fields

Return values

$this

limit()

Sets the number of records that should be retrieved from database, accepts an integer or an expression object that evaluates to an integer.


    public
                    limit(ExpressionInterface|int|null $limit) : $this

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

$limit : ExpressionInterface|int|null: number of records to be returned

Return values

$this

map()


    public
                    map([callable $c =  ]) : CollectionInterface

Modifies each of the results using the callable

Parameters

$c : callable =

Return values

CollectionInterface

mapReduce()

Register a new MapReduce routine to be executed on top of the database results Both the mapper and caller callable should be invokable objects.


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

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

$mapper : callable|null = null: The mapper callable.
$reducer : callable|null = null: The reducing function.
$overwrite : bool = false: Set to true to overwrite existing map + reduce functions.

Return values

$this

matching()

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.


    public
                    matching(string $assoc[, callable|null $builder = null ]) : $this

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

$assoc : string: The association to filter by
$builder : callable|null = null: a function that will receive a pre-made query object that can be used to add custom conditions or selecting some fields

Return values

$this

max()


    public
                    max([mixed $field =  ]) : mixed

Returns the maximum value for a single column in all the results.

Parameters

$field : mixed =

min()


    public
                    min([mixed $field =  ]) : mixed

Returns the minimum value for a single column in all the results.

Parameters

$field : mixed =

modifier()

Adds a single or multiple `SELECT` modifiers to be used in the `SELECT`.


    public
                    modifier(ExpressionInterface|array<string|int, mixed>|string $modifiers[, bool $overwrite = false ]) : $this

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

$modifiers : ExpressionInterface|array<string|int, mixed>|string: modifiers to be applied to the query
$overwrite : bool = false: whether to reset order with field list or not

Return values

$this

nest()


    public
                    nest([mixed $k =  ][, mixed $p =  ][, mixed $n = '\'children\'' ]) : 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 : mixed =
$p : mixed =
$n : mixed = '\'children\''

Return values

CollectionInterface

newExpr()


    public
                    newExpr([ExpressionInterface|array<string|int, mixed>|string|null $rawExpression = null ]) : QueryExpression

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->expr(); // Returns an empty expression object
$expression = $query->expr('Table.column = Table2.column'); // Return a raw SQL expression

Parameters

$rawExpression : ExpressionInterface|array<string|int, mixed>|string|null = null: A string, array or anything you want wrapped in an expression object

Return values

QueryExpression

notMatching()

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.


    public
                    notMatching(string $assoc[, callable|null $builder = null ]) : $this

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

$assoc : string: The association to filter by
$builder : callable|null = null: a function that will receive a pre-made query object that can be used to add custom conditions or selecting some fields

Return values

$this

offset()

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.


    public
                    offset(ExpressionInterface|int|null $offset) : $this

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

$offset : ExpressionInterface|int|null: number of records to be skipped

Return values

$this

order()

Adds a single or multiple fields to be used in the ORDER clause for this query.


    public
                    order(ExpressionInterface|Closure|array<string|int, mixed>|string $fields[, bool $overwrite = false ]) : $this

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' => $query->newExpr('DESC NULLS FIRST')])
    ->order('author_id');

Will generate:

ORDER BY title DESC NULLS FIRST, author_id

$expression = $query->newExpr()->add(['id % 2 = 0']);
$query->order($expression)->order(['title' => 'ASC']);

and

$query->order(function ($exp, $query) {
    return [$exp->add(['id % 2 = 0']), 'title' => 'ASC'];
});

Will both become:

ORDER BY (id %2 = 0), title ASC

Order fields/directions are not sanitized by the query builder. You should use an allowed list of fields/directions when passing in user-supplied data to order().

If you need to set complex expressions as order conditions, you should use orderAsc() or orderDesc().

Parameters

$fields : ExpressionInterface|Closure|array<string|int, mixed>|string: fields to be added to the list
$overwrite : bool = false: whether to reset order with field list or not

Return values

$this

orderAsc()

Add an ORDER BY clause with an ASC direction.


    public
                    orderAsc(ExpressionInterface|Closure|string $field[, bool $overwrite = false ]) : $this

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

$field : ExpressionInterface|Closure|string: The field to order on.
$overwrite : bool = false: Whether to reset the order clauses.

Return values

$this

orderDesc()

Add an ORDER BY clause with a DESC direction.


    public
                    orderDesc(ExpressionInterface|Closure|string $field[, bool $overwrite = false ]) : $this

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

$field : ExpressionInterface|Closure|string: The field to order on.
$overwrite : bool = false: Whether to reset the order clauses.

Return values

$this

page()

Set the page of results you want.


    public
                    page(int $num[, int|null $limit = null ]) : $this

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

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

Return values

$this

reduce()


    public
                    reduce([callable $c =  ][, mixed $zero = null ]) : mixed

Folds all the results into a single value using the callable.

Parameters

$c : callable =
$zero : mixed = null

reject()


    public
                    reject([callable $c =  ]) : CollectionInterface

Removes the results passing the callable test

Parameters

$c : callable =

Return values

CollectionInterface

removeJoin()

Remove a join if it has been defined.


    public
                    removeJoin(string $name) : $this

Useful when you are redefining joins or want to re-order the join clauses.

Parameters

$name : string: The alias/name of the join to remove.

Return values

$this

repository()

Set the default Table object that will be used by this query and form the `FROM` clause.


    public
                    repository(RepositoryInterface|Table $repository) : $this

Parameters

$repository : RepositoryInterface|Table: The default table object to use

Return values

$this

rightJoin()

Adds a single `RIGHT JOIN` clause to the query.


    public
                    rightJoin(array<string, mixed>|string $table[, ExpressionInterface|array<string|int, mixed>|string $conditions = [] ][, array<string|int, mixed> $types = [] ]) : $this

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

$table : array<string, mixed>|string: The table to join with
$conditions : ExpressionInterface|array<string|int, mixed>|string = []: The conditions to use for joining.
$types : array<string|int, mixed> = []: a list of types associated to the conditions used for converting values to the corresponding database representation.

Return values

$this

rowCountAndClose()

Executes the SQL of this query and immediately closes the statement before returning the row count of records changed.


    public
                    rowCountAndClose() : int

This method can be used with UPDATE and DELETE queries, but is not recommended for SELECT queries and is not used to count records.

Example

$rowCount = $query->update('articles')
                ->set(['published'=>true])
                ->where(['published'=>false])
                ->rowCountAndClose();

The above example will change the published column to true for all false records, and return the number of records that were updated.

Return values

int

sample()


    public
                    sample([int $size = 10 ]) : CollectionInterface

In-memory shuffle the results and return a subset of them.

Parameters

$size : int = 10

Return values

CollectionInterface

select()

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.


    public
                    select([ExpressionInterface|Table|Association|callable|array<string|int, mixed>|string $fields = [] ][, bool $overwrite = false ]) : $this

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

$fields : ExpressionInterface|Table|Association|callable|array<string|int, mixed>|string = []: Fields to be added to the list.
$overwrite : bool = false: whether to reset fields with passed list or not

Return values

$this

selectAllExcept()

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.


    public
                    selectAllExcept(Table|Association $table, array<string|int, string> $excludedFields[, bool $overwrite = false ]) : $this

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

$table : Table|Association: The table to use to get an array of columns
$excludedFields : array<string|int, string>: The un-aliased column names you do not want selected from $table
$overwrite : bool = false: Whether to reset/remove previous selected fields

Return values

$this

set()

Set one or many fields to update.


    public
                    set(QueryExpression|Closure|array<string|int, mixed>|string $key[, mixed $value = null ][, array<string, string>|string $types = [] ]) : $this

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

$key : QueryExpression|Closure|array<string|int, mixed>|string

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.

$value : mixed = null

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.

$types : array<string, string>|string = []

The column types to treat data as.

Return values

$this

setConnection()

Sets the connection instance to be used for executing and transforming this query.


    public
                    setConnection(Connection $connection) : $this

Parameters

$connection : Connection: Connection instance

Return values

$this

setDefaultTypes()

Overwrite the default type mappings for fields in the implementing object.


    public
                    setDefaultTypes(array<int|string, string> $types) : $this

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

$types : array<int|string, string>: The array of types to set.

Return values

$this

setEagerLoader()

Sets the instance of the eager loader class to use for loading associations and storing containments.


    public
                    setEagerLoader(EagerLoader $instance) : $this

Parameters

$instance : EagerLoader: The eager loader to use.

Return values

$this

setResult()

Set the result set for a query.


    public
                    setResult(iterable<string|int, mixed> $results) : $this

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

$results : iterable<string|int, mixed>: The results this query should return.

Return values

$this

setSelectTypeMap()

Sets the TypeMap class where the types for each of the fields in the select clause are stored.


    public
                    setSelectTypeMap(TypeMap $typeMap) : $this

Parameters

$typeMap : TypeMap: The map object to use

Return values

$this

setTypeMap()

Creates a new TypeMap if $typeMap is an array, otherwise exchanges it for the given one.


    public
                    setTypeMap(TypeMap|array<string|int, mixed> $typeMap) : $this

Parameters

$typeMap : TypeMap|array<string|int, mixed>: Creates a TypeMap if array, otherwise sets the given TypeMap

Return values

$this

setValueBinder()

Overwrite the current value binder


    public
                    setValueBinder(ValueBinder|null $binder) : $this

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

$binder : ValueBinder|null: The binder or null to disable binding.

Return values

$this

shuffle()


    public
                    shuffle() : CollectionInterface

In-memory randomize the order the results are returned

Return values

CollectionInterface

skip()


    public
                    skip([int $howMany =  ]) : CollectionInterface

Skips some rows from the start of the query result.

Parameters

$howMany : int =

Return values

CollectionInterface

some()


    public
                    some([callable $c =  ]) : bool

Returns true if at least one of the results pass the callable test

Parameters

$c : callable =

Return values

bool

sortBy()


    public
                    sortBy([callable|string $path =  ][, int $order = \SORT_DESC ][, int $sort = \SORT_NUMERIC ]) : CollectionInterface

Sorts the query with the callback

Parameters

$path : callable|string =
$order : int = \SORT_DESC
$sort : int = \SORT_NUMERIC

Return values

CollectionInterface

sql()

Returns the SQL representation of this object.


    public
                    sql([ValueBinder|null $binder = null ]) : string

Parameters

$binder : ValueBinder|null = null: Value binder that generates parameter placeholders

Return values

string

stopWhen()


    public
                    stopWhen([callable $c =  ]) : CollectionInterface

Returns each row until the callable returns true.

Parameters

$c : callable =

Return values

CollectionInterface

subquery()

Returns a new Query that has automatic field aliasing disabled.


    public
            static        subquery(Table $table) : static

Parameters

$table : Table: The table this query is starting on

Return values

static

sumOf()


    public
                    sumOf([callable|string $field =  ]) : float

Returns the sum of all values for a single column

Parameters

$field : callable|string =

Return values

float

take()


    public
                    take([int $size = 1 ][, int $from = 0 ]) : CollectionInterface

In-memory limit and offset for the query results.

Parameters

$size : int = 1
$from : int = 0

Return values

CollectionInterface

toArray()

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


    public
                    toArray() : array<string|int, mixed>

Return values

array<string|int, mixed>

toArray()

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


    public
                    toArray() : array<string|int, mixed>

Returns a key-value array with the results of this query.

Return values

array<string|int, mixed>

toList()


    public
                    toList() : array<string|int, mixed>

Returns a numerically indexed array with the results of this query.

Return values

array<string|int, mixed>

traverse()

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.


    public
                    traverse(callable $callback) : $this

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

$callback : callable: A function or callable to be executed for each part

Return values

$this

traverseExpressions()

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.


    public
                    traverseExpressions(callable $callback) : $this

Callback will receive as first parameter the currently visited expression.

Parameters

$callback : callable: the function to be executed for each ExpressionInterface found inside this query.

Return values

$this

traverseParts()

Will iterate over the provided parts.


    public
                    traverseParts(callable $visitor, array<string|int, string> $parts) : $this

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

$visitor : callable: A function or callable to be executed for each part
$parts : array<string|int, string>: The list of query parts to traverse

Return values

$this

triggerBeforeFind()

Trigger the beforeFind event on the query's repository object.


    public
                    triggerBeforeFind() : void

Will not trigger more than once, and only for select queries.

type()

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


    public
                    type() : string

Return values

string

union()

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.


    public
                    union(Query|string $query[, bool $overwrite = false ]) : $this

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

$query : Query|string: full SQL query to be used in UNION operator
$overwrite : bool = false: whether to reset the list of queries to be operated or not

Return values

$this

unionAll()

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.


    public
                    unionAll(Query|string $query[, bool $overwrite = false ]) : $this

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

$query : Query|string: full SQL query to be used in UNION operator
$overwrite : bool = false: whether to reset the list of queries to be operated or not

Return values

$this

update()

Create an update query.


    public
                    update([ExpressionInterface|string|null $table = null ]) : $this

This changes the query type to be 'update'. Can be combined with set() and where() methods to create update queries.

Parameters

$table : ExpressionInterface|string|null = null: Unused parameter.

Return values

$this

values()

Set the values for an insert query.


    public
                    values(ValuesExpression|Query|array<string|int, mixed> $data) : $this

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

$data : ValuesExpression|Query|array<string|int, mixed>: The data to insert.

Return values

$this

where()

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.


    public
                    where([ExpressionInterface|Closure|array<string|int, mixed>|string|null $conditions = null ][, array<string, string> $types = [] ][, bool $overwrite = false ]) : $this

When using arrays, each entry will be joined to the rest of the conditions using an AND operator. Consecutive calls to this function will also join the new conditions specified using the AND operator. Additionally, values can be expressed using expression objects which can include other query objects.

Any conditions created with this methods can be used with any SELECT, UPDATE and DELETE type of queries.

Conditions using operators:

$query->where([
    'posted >=' => new DateTime('3 days ago'),
    'title LIKE' => 'Hello W%',
    'author_id' => 1,
], ['posted' => 'datetime']);

The previous example produces:

WHERE posted >= 2012-01-27 AND title LIKE 'Hello W%' AND author_id = 1

Second parameter is used to specify what type is expected for each passed key. Valid types can be used from the mapped with Database\Type class.

Nesting conditions with conjunctions:

$query->where([
    'author_id !=' => 1,
    'OR' => ['published' => true, 'posted <' => new DateTime('now')],
    'NOT' => ['title' => 'Hello']
], ['published' => boolean, 'posted' => 'datetime']

The previous example produces:

WHERE author_id = 1 AND (published = 1 OR posted < '2012-02-01') AND NOT (title = 'Hello')

You can nest conditions using conjunctions as much as you like. Sometimes, you may want to define 2 different options for the same key, in that case, you can wrap each condition inside a new array:

$query->where(['OR' => [['published' => false], ['published' => true]])

Would result in:

WHERE (published = false) OR (published = true)

Keep in mind that every time you call where() with the third param set to false (default), it will join the passed conditions to the previous stored list using the AND operator. Also, using the same array key twice in consecutive calls to this method will not override the previous value.

Using expressions objects:

$exp = $query->newExpr()->add(['id !=' => 100, 'author_id' != 1])->tieWith('OR');
$query->where(['published' => true], ['published' => 'boolean'])->where($exp);

The previous example produces:

WHERE (id != 100 OR author_id != 1) AND published = 1

Other Query objects that be used as conditions for any field.

Adding conditions in multiple steps:

You can use callable functions to construct complex expressions, functions receive as first argument a new QueryExpression object and this query instance as second argument. Functions must return an expression object, that will be added the list of conditions for the query using the AND operator.

$query
  ->where(['title !=' => 'Hello World'])
  ->where(function ($exp, $query) {
    $or = $exp->or(['id' => 1]);
    $and = $exp->and(['id >' => 2, 'id <' => 10]);
   return $or->add($and);
  });

The previous example produces:

WHERE title != 'Hello World' AND (id = 1 OR (id > 2 AND id < 10))

Conditions as strings:

$query->where(['articles.author_id = authors.id', 'modified IS NULL']);

The previous example produces:

WHERE articles.author_id = authors.id AND modified IS NULL

Please note that when using the array notation or the expression objects, all values will be correctly quoted and transformed to the correspondent database data type automatically for you, thus securing your application from SQL injections. The keys however, are not treated as unsafe input, and should be validated/sanitized.

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.

Using null-able values

When using values that can be null you can use the 'IS' keyword to let the ORM generate the correct SQL based on the value's type

$query->where([
    'posted >=' => new DateTime('3 days ago'),
    'category_id IS' => $category,
]);

If $category is null - it will actually convert that into category_id IS NULL - if it's 4 it will convert it into category_id = 4

Parameters

$conditions : ExpressionInterface|Closure|array<string|int, mixed>|string|null = null: The conditions to filter on.
$types : array<string, string> = []: Associative array of type names used to bind values to query
$overwrite : bool = false: whether to reset conditions with passed list or not

Return values

$this

whereInList()

Adds an IN condition or set of conditions to be used in the WHERE clause for this query.


    public
                    whereInList(string $field, array<string|int, mixed> $values[, array<string, mixed> $options = [] ]) : $this

This method does allow empty inputs in contrast to where() if you set 'allowEmpty' to true. Be careful about using it without proper sanity checks.

Options:

types - Associative array of type names used to bind values to query
allowEmpty - Allow empty array.

Parameters

$field : string: Field
$values : array<string|int, mixed>: Array of values
$options : array<string, mixed> = []: Options

Return values

$this

whereNotInList()

Adds a NOT IN condition or set of conditions to be used in the WHERE clause for this query.


    public
                    whereNotInList(string $field, array<string|int, mixed> $values[, array<string, mixed> $options = [] ]) : $this

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

$field : string: Field
$values : array<string|int, mixed>: Array of values
$options : array<string, mixed> = []: Options

Return values

$this

whereNotInListOrNull()

Adds a NOT IN condition or set of conditions to be used in the WHERE clause for this query. This also allows the field to be null with a IS NULL condition since the null value would cause the NOT IN condition to always fail.


    public
                    whereNotInListOrNull(string $field, array<string|int, mixed> $values[, array<string, mixed> $options = [] ]) : $this

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

$field : string: Field
$values : array<string|int, mixed>: Array of values
$options : array<string, mixed> = []: Options

Return values

$this

whereNotNull()

Convenience method that adds a NOT NULL condition to the query


    public
                    whereNotNull(ExpressionInterface|array<string|int, mixed>|string $fields) : $this

Parameters

$fields : ExpressionInterface|array<string|int, mixed>|string: A single field or expressions or a list of them that should be not null.

Return values

$this

whereNull()

Convenience method that adds a IS NULL condition to the query


    public
                    whereNull(ExpressionInterface|array<string|int, mixed>|string $fields) : $this

Parameters

$fields : ExpressionInterface|array<string|int, mixed>|string: A single field or expressions or a list of them that should be null.

Return values

$this

window()

Adds a named window expression.


    public
                    window(string $name, WindowExpression|Closure $window[, bool $overwrite = false ]) : $this

You are responsible for adding windows in the order your database requires.

Parameters

$name : string: Window name
$window : WindowExpression|Closure: Window expression
$overwrite : bool = false: Clear all previous query window expressions

Return values

$this

with()

Adds a new common table expression (CTE) to the query.


    public
                    with(CommonTableExpression|Closure $cte[, bool $overwrite = false ]) : $this

Examples:

Common table expressions can either be passed as preconstructed expression objects:

$cte = new \Cake\Database\Expression\CommonTableExpression(
    'cte',
    $connection
        ->newQuery()
        ->select('*')
        ->from('articles')
);

$query->with($cte);

or returned from a closure, which will receive a new common table expression object as the first argument, and a new blank query object as the second argument:

$query->with(function (
    \Cake\Database\Expression\CommonTableExpression $cte,
    \Cake\Database\Query $query
 ) {
    $cteQuery = $query
        ->select('*')
        ->from('articles');

    return $cte
        ->name('cte')
        ->query($cteQuery);
});

Parameters

$cte : CommonTableExpression|Closure: The CTE to add.
$overwrite : bool = false: Whether to reset the list of CTEs.

Return values

$this

zip()


    public
                    zip([array<string|int, mixed>|Traversable $c =  ]) : CollectionInterface

Returns the first result of both the query and $c in an array, then the second results and so on.

Parameters

$c : array<string|int, mixed>|Traversable =

Return values

CollectionInterface

zipWith()


    public
                    zipWith([mixed $collections =  ][, callable $callable =  ]) : 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 : mixed =
$callable : callable =

Return values

CollectionInterface

_addAssociationsToTypeMap()

Used to recursively add contained association column types to the query.


    protected
                    _addAssociationsToTypeMap(Table $table, TypeMap $typeMap, array<string, array<string|int, mixed>> $associations) : void

Parameters

$table : Table: The table instance to pluck associations from.
$typeMap : TypeMap: The typemap to check for columns in. This typemap is indirectly mutated via
$associations : array<string, array<string|int, mixed>>: The nested tree of associations to walk.

_addDefaultFields()

Inspects if there are any set fields for selecting, otherwise adds all the fields for the default table.


    protected
                    _addDefaultFields() : void

_addDefaultSelectTypes()

Sets the default types for converting the fields in the select clause


    protected
                    _addDefaultSelectTypes() : void

_conjugate()

Helper function used to build conditions by composing QueryExpression objects.


    protected
                    _conjugate(string $part, ExpressionInterface|Closure|array<string|int, mixed>|string|null $append, string $conjunction, array<string, string> $types) : void

Parameters

$part : string: Name of the query part to append the new part to
$append : ExpressionInterface|Closure|array<string|int, mixed>|string|null: Expression or builder function to append. to append.
$conjunction : string: type of conjunction to be used to operate part
$types : array<string, string>: Associative array of type names used to bind values to query

_decorateResults()

Decorates the results iterator with MapReduce routines and formatters


    protected
                    _decorateResults(Traversable $result) : ResultSetInterface

Parameters

$result : Traversable: Original results

Return values

ResultSetInterface

_decorateStatement()

Auxiliary function used to wrap the original statement from the driver with any registered callbacks.


    protected
                    _decorateStatement(StatementInterface $statement) : CallbackStatement|StatementInterface

Parameters

$statement : StatementInterface: to be decorated

Return values

CallbackStatement|StatementInterface

_decoratorClass()

Returns the name of the class to be used for decorating results


    protected
                    _decoratorClass() : string

Return values

string

_dirty()

Marks a query as dirty, removing any preprocessed information from in memory caching such as previous results


    protected
                    _dirty() : void

_execute()

Executes this query and returns a ResultSet object containing the results.


    protected
                    _execute() : ResultSetInterface

This will also setup the correct statement class in order to eager load deep associations.

Return values

ResultSetInterface

_expressionsVisitor()

Query parts traversal method used by traverseExpressions()


    protected
                    _expressionsVisitor(ExpressionInterface|array<string|int, ExpressionInterface> $expression, Closure $callback) : void

Parameters

$expression : ExpressionInterface|array<string|int, ExpressionInterface>: Query expression or array of expressions.
$callback : Closure: The callback to be executed for each ExpressionInterface found inside this query.

_makeJoin()

Returns an array that can be passed to the join method describing a single join clause


    protected
                    _makeJoin(array<string, mixed>|string $table, ExpressionInterface|array<string|int, mixed>|string $conditions, string $type) : array<string|int, mixed>

Parameters

$table : array<string, mixed>|string: The table to join with
$conditions : ExpressionInterface|array<string|int, mixed>|string: The conditions to use for joining.
$type : string: the join type to use

Return values

array<string|int, mixed>

_performCount()

Performs and returns the COUNT(*) for the query.


    protected
                    _performCount() : int

Return values

int

_transformQuery()

Applies some defaults to the query object before it is executed.


    protected
                    _transformQuery() : void

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

Query extends Query in package Application implements JsonSerializable, QueryInterface uses QueryTrait

Tags

Table of Contents

Interfaces

Constants

Properties

Methods

Constants

APPEND

JOIN_TYPE_INNER

JOIN_TYPE_LEFT

JOIN_TYPE_RIGHT

OVERWRITE

PREPEND

Properties

$_repository

$_autoFields

$_beforeFindFired

$_cache

$_connection

$_counter

$_deleteParts

$_dirty

$_eagerLoaded

$_eagerLoader

$_formatters

$_functionsBuilder

$_hasFields

$_hydrate

$_insertParts

$_iterator

$_mapReduce

$_options

$_parts

$_repository

$_resultDecorators

$_results

Tags

$_resultsCount

$_selectParts

$_selectTypeMap

$_type

$_typeMap

$_updateParts

$_useBufferedResults

$_valueBinder

$aliasingEnabled

$typeCastEnabled

Methods

__call()

Parameters

Tags

__clone()

__construct()

Parameters

__debugInfo()

Tags

Return values

__toString()

Return values

addDefaultTypes()

Parameters

Return values

aliasField()

Parameters

Return values

aliasFields()

Parameters

Return values

all()

Tags

Return values

andHaving()

Parameters

Tags

Return values

andWhere()

Examples:

Parameters

Tags

Query extends Query
in package

Application

implements JsonSerializable, QueryInterface uses QueryTrait