Class Query

This class represents a Relational database SQL Query. A query can be of different types like select, update, insert and delete. Exposes the methods for dynamically constructing each query part, execute it and transform it to a specific SQL dialect.

Cake\Database\Query implements Cake\Database\ExpressionInterface, IteratorAggregate uses Cake\Database\TypeMapTrait

Direct Subclasses

Cake\ORM\Query

Namespace: Cake\Database
Location: Database/Query.php

Properties summary

$_connection protected

Cake\Database\Connection

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

boolean

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.
$_functionsBuilder protected

Cake\Database\FunctionsBuilder|null

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

Cake\Database\StatementInterface|null

Statement object resulting from executing this query.
$_parts protected

array

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

array

A list of callback functions to be called to alter each row from resulting statement upon retrieval. Each one of the callback function will receive the row array as first argument.
$_selectTypeMap protected

Cake\Database\TypeMap

The Type map for fields in the select clause
$_type protected

string

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

boolean

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

Cake\Database\ValueBinder|null

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

boolean

Tracking flag to disable casting

Inherited Properties

_typeMap

Method Summary

__clone() public

Do a deep clone on this object.
__construct() public

Constructor.
__debugInfo() public

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

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

Helper function used to build conditions by composing QueryExpression objects.
_decorateStatement() protected

Auxiliary function used to wrap the original statement from the driver with any registered callbacks.
_dirty() protected

Marks a query as dirty, removing any preprocessed information from in memory caching.
_makeJoin() protected

Returns an array that can be passed to the join method describing a single join clause
andHaving() public

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

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

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

Enable/Disable buffered results.
clause() public

Returns any data that was stored in the specified clause. This is useful for modifying any internal part of the query and it is used by the SQL dialects to transform the query accordingly before it is executed. The valid clauses that can be retrieved are: delete, update, set, insert, values, select, distinct, from, join, set, where, group, having, order, limit, offset and union.
connection() public

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

Registers a callback to be executed for each result that is fetched from the result set, the callback function will receive as first parameter an array with the raw data from the database for every row that is fetched and must return the row with any possible modifications.
delete() public

Create a delete query.
disableBufferedResults() public

Disables buffered results.
disableResultsCasting() public

Disables the automatic casting of fields to their corresponding PHP data type
distinct() public

Adds a DISTINCT clause to the query to remove duplicates from the result set. This clause can only be used for select statements.
enableBufferedResults() public

Enables/Disables buffered results.
enableResultsCasting() public

Enables the automatic casting of fields to their corresponding type
epilog() public

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

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

Adds a single or multiple tables to be used in the FROM clause for this query. Tables can be passed as an array of strings, array of expression objects, a single expression or a single string.
func() public

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

Gets the connection instance to be used for executing and transforming this query.
getIterator() public

Executes this query and returns a results iterator. This function is required for implementing the IteratorAggregate interface and allows the query to be iterated without having to call execute() manually, thus making it look like a result set instead of the query itself.
getSelectTypeMap() public

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

Returns the currently used ValueBinder instance.
group() public

Adds a single or multiple fields to be used in the GROUP BY clause for this query. Fields can be passed as an array of strings, array of expression objects, a single expression or a single string.
having() public

Adds a condition or set of conditions to be used in the HAVING clause for this query. This method operates in exactly the same way as the method where() does. Please refer to its documentation for an insight on how to using each parameter.
identifier() public

Creates an expression that refers to an identifier. Identifiers are used to refer to field names and allow the SQL compiler to apply quotes or escape the identifier.
innerJoin() public

Adds a single INNER JOIN clause to the query.
insert() public

Create an insert query.
into() public

Set the table name for insert queries.
isBufferedResultsEnabled() public

Returns whether buffered results are enabled/disabled.
join() public

Adds a single or multiple tables to be used as JOIN clauses to this query. Tables can be passed as an array of strings, an array describing the join parts, an array with multiple join descriptions, or a single string.
leftJoin() public

Adds a single LEFT JOIN clause to the query.
limit() public

Sets the number of records that should be retrieved from database, accepts an integer or an expression object that evaluates to an integer. In some databases, this operation might not be supported or will require the query to be transformed in order to limit the result set size.
modifier() public

Adds a single or multiple SELECT modifiers to be used in the SELECT.
newExpr() public

Returns a new QueryExpression object. This is a handy function when building complex queries using a fluent interface. You can also override this function in subclasses to use a more specialized QueryExpression class if required.
offset() public

Sets the number of records that should be skipped from the original result set This is commonly used for paginating large results. Accepts an integer or an expression object that evaluates to an integer.
orHaving() public

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

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

Adds a single or multiple fields to be used in the ORDER clause for this query. Fields can be passed as an array of strings, array of expression objects, a single expression or a single string.
orderAsc() public

Add an ORDER BY clause with an ASC direction.
orderDesc() public

Add an ORDER BY clause with a DESC direction.
page() public

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

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

Adds a single RIGHT JOIN clause to the query.
rowCountAndClose() public

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

Adds new fields to be returned by a SELECT statement when this query is executed. Fields can be passed as an array of strings, array of expression objects, a single expression or a single string.
selectTypeMap() public

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

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

Sets the connection instance to be used for executing and transforming this query.
setSelectTypeMap() public

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

Overwrite the current value binder
sql() public

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

Will iterate over every specified part. Traversing functions can aggregate results using variables in the closure or instance variables. This function is commonly used as a way for traversing all query parts that are going to be used for constructing a query.
traverseExpressions() public

This function works similar to the traverse() function, with the difference that it does a full depth traversal of the entire expression tree. This will execute the provided callback function for each ExpressionInterface object that is stored inside this query at any nesting depth in any part of the query.
type() public

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

Adds a complete query to be used in conjunction with an UNION operator with this query. This is used to combine the result set of this query with the one that will be returned by the passed query. You can add as many queries as you required by calling multiple times this method with different queries.
unionAll() public

Adds a complete query to be used in conjunction with the UNION ALL operator with this query. This is used to combine the result set of this query with the one that will be returned by the passed query. You can add as many queries as you required by calling multiple times this method with different queries.
update() public

Create an update query.
valueBinder() public

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

Set the values for an insert query.
where() public

Adds a condition or set of conditions to be used in the WHERE clause for this query. Conditions can be expressed as an array of fields as keys with comparison operators in it, the values for the array will be used for comparing the field to such literal. Finally, conditions can be expressed as a single string or an array of strings.
whereInList() public

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

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

Convenience method that adds a NOT NULL condition to the query
whereNull() public

Convenience method that adds a IS NULL condition to the query

Method Detail

__clone() public ¶

__clone( )

Do a deep clone on this object.

Will clone all of the expression objects used in each of the clauses, as well as the valueBinder.

__construct() public ¶

__construct( Cake\Database\Connection $connection )

Constructor.

Parameters

Cake\Database\Connection $connection: The connection object to be used for transforming and executing this query

__debugInfo() public ¶

__debugInfo( )

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

Returns

array

__toString() public ¶

__toString( )

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

Returns

string

_conjugate() protected ¶

_conjugate( string $part , string|null|array|Cake\Database\ExpressionInterface|callable $append , string $conjunction , array $types )

Helper function used to build conditions by composing QueryExpression objects.

Parameters

string $part: Name of the query part to append the new part to
string|null|array|Cake\Database\ExpressionInterface|callable $append: Expression or builder function to append.
string $conjunction: type of conjunction to be used to operate part
array $types: associative array of type names used to bind values to query

_decorateStatement() protected ¶

_decorateStatement( Cake\Database\StatementInterface $statement )

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

Parameters

Cake\Database\StatementInterface $statement: to be decorated

Returns

Cake\Database\Statement\CallbackStatement

_dirty() protected ¶

_dirty( )

Marks a query as dirty, removing any preprocessed information from in memory caching.

_makeJoin() protected ¶

_makeJoin( string|array $table , string|array|Cake\Database\ExpressionInterface $conditions , string $type )

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

Parameters

string|array $table: The table to join with
string|array|Cake\Database\ExpressionInterface $conditions: The conditions to use for joining.
string $type: the join type to use

Returns

array

andHaving() public ¶

andHaving( string|array|Cake\Database\ExpressionInterface|callable $conditions , array $types [] )

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

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

Parameters

string|array|Cake\Database\ExpressionInterface|callable $conditions: The AND conditions for HAVING.
array $types optional []: associative array of type names used to bind values to query

Returns

$this

See

\Cake\Database\Query::andWhere()

andWhere() public ¶

andWhere( string|array|Cake\Database\ExpressionInterface|callable $conditions , array $types [] )

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

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

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

Examples:

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

Will produce:

WHERE title = 'Hello World' AND author_id = 1

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

Produces:

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

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

Generates the following conditions:

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

Parameters

string|array|Cake\Database\ExpressionInterface|callable $conditions: The conditions to add with AND.
array $types optional []: associative array of type names used to bind values to query

Returns

$this

See

\Cake\Database\Query::where()
Cake\Database\Type

bind() public ¶

bind( string|integer $param , mixed $value , string|integer $type 'string' )

Associates a query placeholder to a value and a type.

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

$query->bind(':id', [1, 2, 3], 'int[]');

Will create 3 int placeholders. When using named placeholders, this method requires that the placeholders include : e.g. :value.

Parameters

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

Returns

$this

bufferResults() public ¶

bufferResults( boolean|null $enable null )

Enable/Disable buffered results.

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

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

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

Deprecated

3.4.0 Use enableBufferedResults()/isBufferedResultsEnabled() instead.

Parameters

boolean|null $enable optional null: Whether or not to enable buffering

Returns

boolean|Cake\Database\Query
$this

clause() public ¶

clause( string $name )

Returns any data that was stored in the specified clause. This is useful for modifying any internal part of the query and it is used by the SQL dialects to transform the query accordingly before it is executed. The valid clauses that can be retrieved are: delete, update, set, insert, values, select, distinct, from, join, set, where, group, having, order, limit, offset and union.

The return value for each of those parts may vary. Some clauses use QueryExpression to internally store their state, some use arrays and others may use booleans or integers. This is summary of the return types for each clause.

update: string The name of the table to update
set: QueryExpression
insert: array, will return an array containing the table + columns.
values: ValuesExpression
select: array, will return empty array when no fields are set
distinct: boolean
from: array of tables
join: array
set: array
where: QueryExpression, returns null when not set
group: array
having: QueryExpression, returns null when not set
order: OrderByExpression, returns null when not set
limit: integer or QueryExpression, null when not set
offset: integer or QueryExpression, null when not set
union: array

Parameters

string $name: name of the clause to be returned

Returns

mixed

Throws

InvalidArgumentException
When the named clause does not exist.

connection() public ¶

connection( Cake\Database\Connection|null $connection null )

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

Deprecated

3.4.0 Use setConnection()/getConnection() instead.

Parameters

Cake\Database\Connection|null $connection optional null: Connection instance

Returns

$this|\Cake\Database\Connection

decorateResults() public ¶

decorateResults( callable|null $callback , boolean $overwrite false )

Registers a callback to be executed for each result that is fetched from the result set, the callback function will receive as first parameter an array with the raw data from the database for every row that is fetched and must return the row with any possible modifications.

Callbacks will be executed lazily, if only 3 rows are fetched for database it will called 3 times, event though there might be more rows to be fetched in the cursor.

Callbacks are stacked in the order they are registered, if you wish to reset the stack the call this function with the second parameter set to true.

If you wish to remove all decorators from the stack, set the first parameter to null and the second to true.

Example

$query->decorateResults(function ($row) {
  $row['order_total'] = $row['subtotal'] + ($row['subtotal'] * $row['tax']);
   return $row;
});

Parameters

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

Returns

$this

delete() public ¶

delete( string|null $table null )

Create a delete query.

Can be combined with from(), where() and other methods to create delete queries with specific conditions.

Parameters

string|null $table optional null: The table to use when deleting.

Returns

$this

disableBufferedResults() public ¶

disableBufferedResults( )

Disables buffered results.

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

Returns

$this

disableResultsCasting() public ¶

disableResultsCasting( )

Disables the automatic casting of fields to their corresponding PHP data type

Returns

$this

distinct() public ¶

distinct( array|Cake\Database\ExpressionInterface|string|boolean $on [] , boolean $overwrite false )

Adds a DISTINCT clause to the query to remove duplicates from the result set. This clause can only be used for select statements.

If you wish to filter duplicates based of those rows sharing a particular field or set of fields, you may pass an array of fields to filter on. Beware that this option might not be fully supported in all database systems.

Examples:

// Filters products with the same name and city
$query->select(['name', 'city'])->from('products')->distinct();

// Filters products in the same city
$query->distinct(['city']);
$query->distinct('city');

// Filter products with the same name
$query->distinct(['name'], true);
$query->distinct('name', true);

Parameters

array|Cake\Database\ExpressionInterface|string|boolean $on optional []: Enable/disable distinct class or list of fields to be filtered on
boolean $overwrite optional false: whether to reset fields with passed list or not

Returns

$this

enableBufferedResults() public ¶

enableBufferedResults( boolean $enable true )

Enables/Disables buffered results.

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

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

Parameters

boolean $enable optional true: Whether or not to enable buffering

Returns

$this

enableResultsCasting() public ¶

enableResultsCasting( )

Enables the automatic casting of fields to their corresponding type

Returns

$this

epilog() public ¶

epilog( string|Cake\Database\Expression\QueryExpression|null $expression null )

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

Examples:

$query->select('id')->where(['author_id' => 1])->epilog('FOR UPDATE');
$query
 ->insert('articles', ['title'])
 ->values(['author_id' => 1])
 ->epilog('RETURNING id');

Epliog content is raw SQL and not suitable for use with user supplied data.

Parameters

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

Returns

$this

execute() public ¶

execute( )

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

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

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

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

Returns

Cake\Database\StatementInterface

from() public ¶

from( array|string $tables [] , boolean $overwrite false )

Adds a single or multiple tables to be used in the FROM clause for this query. Tables can be passed as an array of strings, array of expression objects, a single expression or a single string.

If an array is passed, keys will be used to alias tables using the value as the real field to be aliased. It is possible to alias strings, ExpressionInterface objects or even other Query objects.

By default this function will append any passed argument to the list of tables to be selected from, unless the second argument is set to true.

This method can be used for select, update and delete statements.

Examples:

$query->from(['p' => 'posts']); // Produces FROM posts p
$query->from('authors'); // Appends authors: FROM posts p, authors
$query->from(['products'], true); // Resets the list: FROM products
$query->from(['sub' => $countQuery]); // FROM (SELECT ...) sub

Parameters

array|string $tables optional []: tables to be added to the list. This argument, can be passed as an array of strings, array of expression objects, or a single string. See the examples above for the valid call types.
boolean $overwrite optional false: whether to reset tables with passed list or not

Returns

$this|array

func() public ¶

func( )

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

Example:

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

Returns

Cake\Database\FunctionsBuilder

getConnection() public ¶

getConnection( )

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

Returns

Cake\Database\Connection

getIterator() public ¶

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.

Returns

Cake\Database\StatementInterface|null

Implementation of

IteratorAggregate::getIterator()

getSelectTypeMap() public ¶

getSelectTypeMap( )

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

Returns

Cake\Database\TypeMap

getValueBinder() public ¶

getValueBinder( )

Returns the currently used ValueBinder instance.

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

Returns

Cake\Database\ValueBinder

group() public ¶

group( array|Cake\Database\ExpressionInterface|string $fields , boolean $overwrite false )

Adds a single or multiple fields to be used in the GROUP BY clause for this query. Fields can be passed as an array of strings, array of expression objects, a single expression or a single string.

By default this function will append any passed argument to the list of fields to be grouped, unless the second argument is set to true.

Examples:

// Produces GROUP BY id, title
$query->group(['id', 'title']);

// Produces GROUP BY title
$query->group('title');

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

Parameters

array|Cake\Database\ExpressionInterface|string $fields: fields to be added to the list
boolean $overwrite optional false: whether to reset fields with passed list or not

Returns

$this

having() public ¶

having( string|array|Cake\Database\ExpressionInterface|callable|null $conditions null , array $types [] , boolean $overwrite false )

Adds a condition or set of conditions to be used in the HAVING clause for this query. This method operates in exactly the same way as the method where() does. Please refer to its documentation for an insight on how to using each parameter.

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

Parameters

string|array|Cake\Database\ExpressionInterface|callable|null $conditions optional null: The having conditions.
array $types optional []: associative array of type names used to bind values to query
boolean $overwrite optional false: whether to reset conditions with passed list or not

Returns

$this

See

\Cake\Database\Query::where()

identifier() public ¶

identifier( string $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.

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->newExp()->lte('count', $query->identifier('total'));

Parameters

string $identifier: The identifier for an expression

Returns

Cake\Database\ExpressionInterface

innerJoin() public ¶

innerJoin( string|array $table , string|array|Cake\Database\ExpressionInterface $conditions [] , array $types [] )

Adds a single INNER JOIN clause to the query.

This is a shorthand method for building joins via join().

The arguments of this method are identical to the leftJoin() shorthand, please refer to that methods description for further details.

Parameters

string|array $table: The table to join with
string|array|Cake\Database\ExpressionInterface $conditions optional []: The conditions to use for joining.
array $types optional []: a list of types associated to the conditions used for converting values to the corresponding database representation.

Returns

$this

insert() public ¶

insert( array $columns , array $types [] )

Create an insert query.

Note calling this method will reset any data previously set with Query::values().

Parameters

array $columns: The columns to insert into.
array $types optional []: A map between columns & their datatypes.

Returns

$this

Throws

RuntimeException
When there are 0 columns.

into() public ¶

into( string $table )

Set the table name for insert queries.

Parameters

string $table: The table name to insert into.

Returns

$this

isBufferedResultsEnabled() public ¶

isBufferedResultsEnabled( )

Returns whether buffered results are enabled/disabled.

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

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

Returns

boolean

join() public ¶

join( array|string|null $tables null , array $types [] , boolean $overwrite false )

Adds a single or multiple tables to be used as JOIN clauses to this query. Tables can be passed as an array of strings, an array describing the join parts, an array with multiple join descriptions, or a single string.

By default this function will append any passed argument to the list of tables to be joined, unless the third argument is set to true.

When no join type is specified an INNER JOIN is used by default: $query->join(['authors']) will produce INNER JOIN authors ON 1 = 1

It is also possible to alias joins using the array key: $query->join(['a' => 'authors']) will produce INNER JOIN authors a ON 1 = 1

A join can be fully described and aliased using the array notation:

$query->join([
    'a' => [
        'table' => 'authors',
        'type' => 'LEFT',
        'conditions' => 'a.id = b.author_id'
    ]
]);
// Produces LEFT JOIN authors a ON a.id = b.author_id

You can even specify multiple joins in an array, including the full description:

$query->join([
    'a' => [
        'table' => 'authors',
        'type' => 'LEFT',
        'conditions' => 'a.id = b.author_id'
    ],
    'p' => [
        'table' => 'publishers',
        'type' => 'INNER',
        'conditions' => 'p.id = b.publisher_id AND p.name = "Cake Software Foundation"'
    ]
]);
// LEFT JOIN authors a ON a.id = b.author_id
// INNER JOIN publishers p ON p.id = b.publisher_id AND p.name = "Cake Software Foundation"

Using conditions and types

Conditions can be expressed, as in the examples above, using a string for comparing columns, or string with already quoted literal values. Additionally it is possible to use conditions expressed in arrays or expression objects.

When using arrays for expressing conditions, it is often desirable to convert the literal values to the correct database representation. This is achieved using the second parameter of this function.

$query->join(['a' => [
    'table' => 'articles',
    'conditions' => [
        'a.posted >=' => new DateTime('-3 days'),
        'a.published' => true,
        'a.author_id = authors.id'
    ]
]], ['a.posted' => 'datetime', 'a.published' => 'boolean'])

Overwriting joins

When creating aliased joins using the array notation, you can override previous join definitions by using the same alias in consequent calls to this function or you can replace all previously defined joins with another list if the third parameter for this function is set to true.

$query->join(['alias' => 'table']); // joins table with as alias
$query->join(['alias' => 'another_table']); // joins another_table with as alias
$query->join(['something' => 'different_table'], [], true); // resets joins list

Parameters

array|string|null $tables optional null: list of tables to be joined in the query
array $types optional []: associative array of type names used to bind values to query
boolean $overwrite optional false: whether to reset joins with passed list or not

Returns

$this|array

See

Cake\Database\Type

leftJoin() public ¶

leftJoin( string|array $table , string|array|Cake\Database\ExpressionInterface $conditions [] , array $types [] )

Adds a single LEFT JOIN clause to the query.

This is a shorthand method for building joins via join().

The table name can be passed as a string, or as an array in case it needs to be aliased:

// LEFT JOIN authors ON authors.id = posts.author_id
$query->leftJoin('authors', 'authors.id = posts.author_id');

// LEFT JOIN authors a ON a.id = posts.author_id
$query->leftJoin(['a' => 'authors'], 'a.id = posts.author_id');

Conditions can be passed as strings, arrays, or expression objects. When using arrays it is possible to combine them with the $types parameter in order to define how to convert the values:

$query->leftJoin(['a' => 'articles'], [
     'a.posted >=' => new DateTime('-3 days'),
     'a.published' => true,
     'a.author_id = authors.id'
], ['a.posted' => 'datetime', 'a.published' => 'boolean']);

See join() for further details on conditions and types.

Parameters

string|array $table: The table to join with
string|array|Cake\Database\ExpressionInterface $conditions optional []: The conditions to use for joining.
array $types optional []: a list of types associated to the conditions used for converting values to the corresponding database representation.

Returns

$this

limit() public ¶

limit( integer|Cake\Database\ExpressionInterface $num )

Sets the number of records that should be retrieved from database, accepts an integer or an expression object that evaluates to an integer. In some databases, this operation might not be supported or will require the query to be transformed in order to limit the result set size.

Examples

$query->limit(10) // generates LIMIT 10
$query->limit($query->newExpr()->add(['1 + 1'])); // LIMIT (1 + 1)

Parameters

integer|Cake\Database\ExpressionInterface $num: number of records to be returned

Returns

$this

modifier() public ¶

modifier( array|Cake\Database\ExpressionInterface|string $modifiers , boolean $overwrite false )

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

By default this function will append any passed argument to the list of modifiers to be applied, unless the second argument is set to true.

Example:

// Ignore cache query in MySQL
$query->select(['name', 'city'])->from('products')->modifier('SQL_NO_CACHE');
// It will produce the SQL: SELECT SQL_NO_CACHE name, city FROM products

// Or with multiple modifiers
$query->select(['name', 'city'])->from('products')->modifier(['HIGH_PRIORITY', 'SQL_NO_CACHE']);
// It will produce the SQL: SELECT HIGH_PRIORITY SQL_NO_CACHE name, city FROM products

Parameters

array|Cake\Database\ExpressionInterface|string $modifiers: modifiers to be applied to the query
boolean $overwrite optional false: whether to reset order with field list or not

Returns

$this

newExpr() public ¶

newExpr( mixed $rawExpression null )

Returns a new QueryExpression object. This is a handy function when building complex queries using a fluent interface. You can also override this function in subclasses to use a more specialized QueryExpression class if required.

You can optionally pass a single raw SQL string or an array or expressions in any format accepted by \Cake\Database\Expression\QueryExpression:

$expression = $query->newExpr(); // Returns an empty expression object
$expression = $query->newExpr('Table.column = Table2.column'); // Return a raw SQL expression

Parameters

mixed $rawExpression optional null: A string, array or anything you want wrapped in an expression object

Returns

Cake\Database\Expression\QueryExpression

offset() public ¶

offset( integer|Cake\Database\ExpressionInterface $num )

Sets the number of records that should be skipped from the original result set This is commonly used for paginating large results. Accepts an integer or an expression object that evaluates to an integer.

In some databases, this operation might not be supported or will require the query to be transformed in order to limit the result set size.

Examples

$query->offset(10) // generates OFFSET 10
$query->offset($query->newExpr()->add(['1 + 1'])); // OFFSET (1 + 1)

Parameters

integer|Cake\Database\ExpressionInterface $num: number of records to be skipped

Returns

$this

orHaving() public ¶

orHaving( string|array|Cake\Database\ExpressionInterface|callable $conditions , array $types [] )

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

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

Deprecated

3.5.0 This method creates hard to predict SQL based on the current query state. Use Query::having() instead as it has more predicatable and easier to understand behavior.

Parameters

string|array|Cake\Database\ExpressionInterface|callable $conditions: The OR conditions for HAVING.
array $types optional []: associative array of type names used to bind values to query.

Returns

$this

See

\Cake\Database\Query::orWhere()

orWhere() public ¶

orWhere( string|array|Cake\Database\ExpressionInterface|callable $conditions , array $types [] )

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

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

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

Examples:

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

Will produce:

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

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

Produces:

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

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

Generates the following conditions:

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

Deprecated

3.5.0 This method creates hard to predict SQL based on the current query state. Use Query::where() instead as it has more predicatable and easier to understand behavior.

Parameters

string|array|Cake\Database\ExpressionInterface|callable $conditions: The conditions to add with OR.
array $types optional []: associative array of type names used to bind values to query

Returns

$this

See

\Cake\Database\Query::where()
Cake\Database\Type

order() public ¶

order( array|Cake\Database\ExpressionInterface|callable|string $fields , boolean $overwrite false )

Adds a single or multiple fields to be used in the ORDER clause for this query. Fields can be passed as an array of strings, array of expression objects, a single expression or a single string.

If an array is passed, keys will be used as the field itself and the value will represent the order in which such field should be ordered. When called multiple times with the same fields as key, the last order definition will prevail over the others.

By default this function will append any passed argument to the list of fields to be selected, unless the second argument is set to true.

Examples:

$query->order(['title' => 'DESC', 'author_id' => 'ASC']);

Produces:

ORDER BY title DESC, author_id ASC

$query->order(['title' => 'DESC NULLS FIRST'])->order('author_id');

Will generate:

ORDER BY title DESC NULLS FIRST, author_id

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

and

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

Will both become:

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

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

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

Parameters

array|Cake\Database\ExpressionInterface|callable|string $fields: fields to be added to the list
boolean $overwrite optional false: whether to reset order with field list or not

Returns

$this

orderAsc() public ¶

orderAsc( string|Cake\Database\Expression\QueryExpression $field , boolean $overwrite false )

Add an ORDER BY clause with an ASC direction.

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

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

Parameters

string|Cake\Database\Expression\QueryExpression $field: The field to order on.
boolean $overwrite optional false: Whether or not to reset the order clauses.

Returns

$this

orderDesc() public ¶

orderDesc( string|Cake\Database\Expression\QueryExpression $field , boolean $overwrite false )

Add an ORDER BY clause with a DESC direction.

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

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

Parameters

string|Cake\Database\Expression\QueryExpression $field: The field to order on.
boolean $overwrite optional false: Whether or not to reset the order clauses.

Returns

$this

page() public ¶

page( integer $num , integer|null $limit null )

Set the page of results you want.

This method provides an easier to use interface to set the limit + offset in the record set you want as results. If empty the limit will default to the existing limit clause, and if that too is empty, then 25 will be used.

Pages must start at 1.

Parameters

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

Returns

$this

Throws

InvalidArgumentException
If page number < 1.

removeJoin() public ¶

removeJoin( string $name )

Remove a join if it has been defined.

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

Parameters

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

Returns

$this

rightJoin() public ¶

rightJoin( string|array $table , string|array|Cake\Database\ExpressionInterface $conditions [] , array $types [] )

Adds a single RIGHT JOIN clause to the query.

This is a shorthand method for building joins via join().

The arguments of this method are identical to the leftJoin() shorthand, please refer to that methods description for further details.

Parameters

string|array $table: The table to join with
string|array|Cake\Database\ExpressionInterface $conditions optional []: The conditions to use for joining.
array $types optional []: a list of types associated to the conditions used for converting values to the corresponding database representation.

Returns

$this

rowCountAndClose() public ¶

rowCountAndClose( )

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

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

Example

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

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

Returns

integer

select() public ¶

select( array|Cake\Database\ExpressionInterface|string|callable $fields [] , boolean $overwrite false )

Adds new fields to be returned by a SELECT statement when this query is executed. Fields can be passed as an array of strings, array of expression objects, a single expression or a single string.

If an array is passed, keys will be used to alias fields using the value as the real field to be aliased. It is possible to alias strings, Expression objects or even other Query objects.

If a callable function is passed, the returning array of the function will be used as the list of fields.

By default this function will append any passed argument to the list of fields to be selected, unless the second argument is set to true.

Examples:

$query->select(['id', 'title']); // Produces SELECT id, title
$query->select(['author' => 'author_id']); // Appends author: SELECT id, title, author_id as author
$query->select('id', true); // Resets the list: SELECT id
$query->select(['total' => $countQuery]); // SELECT id, (SELECT ...) AS total
$query->select(function ($query) {
    return ['article_id', 'total' => $query->count('*')];
})

By default no fields are selected, if you have an instance of Cake\ORM\Query and try to append fields you should also call Cake\ORM\Query::enableAutoFields() to select the default fields from the table.

Parameters

array|Cake\Database\ExpressionInterface|string|callable $fields optional []: fields to be added to the list.
boolean $overwrite optional false: whether to reset fields with passed list or not

Returns

$this

selectTypeMap() public ¶

selectTypeMap( Cake\Database\TypeMap $typeMap null )

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

When called with no arguments, the current TypeMap object is returned.

Deprecated

3.4.0 Use setSelectTypeMap()/getSelectTypeMap() instead.

Parameters

Cake\Database\TypeMap $typeMap optional null: The map object to use

Returns

$this|\Cake\Database\TypeMap

set() public ¶

set( string|array|callable|Cake\Database\Expression\QueryExpression $key , mixed $value null , array $types [] )

Set one or many fields to update.

Examples

Passing a string:

$query->update('articles')->set('title', 'The Title');

Passing an array:

$query->update('articles')->set(['title' => 'The Title'], ['title' => 'string']);

Passing a callable:

$query->update('articles')->set(function ($exp) {
  return $exp->eq('title', 'The title', 'string');
});

Parameters

string|array|callable|Cake\Database\Expression\QueryExpression $key: The column name or array of keys + values to set. This can also be a QueryExpression containing a SQL fragment. It can also be a callable, that is required to return an expression object.
mixed $value optional 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.
array $types optional []: The column types to treat data as.

Returns

$this

setConnection() public ¶

setConnection( Cake\Database\Connection $connection )

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

Parameters

Cake\Database\Connection $connection: Connection instance

Returns

$this

setSelectTypeMap() public ¶

setSelectTypeMap( Cake\Database\TypeMap $typeMap )

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

Parameters

Cake\Database\TypeMap $typeMap: The map object to use

Returns

$this

setValueBinder() public ¶

setValueBinder( Cake\Database\ValueBinder|boolean $binder )

Overwrite the current value binder

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

Parameters

Cake\Database\ValueBinder|boolean $binder: The binder or false to disable binding.

Returns

$this

sql() public ¶

sql( Cake\Database\ValueBinder $generator null )

Returns the SQL representation of this object.

This function will compile this query to make it compatible with the SQL dialect that is used by the connection, This process might add, remove or alter any query part or internal expression to make it executable in the target platform.

The resulting query may have placeholders that will be replaced with the actual values when the query is executed, hence it is most suitable to use with prepared statements.

Parameters

Cake\Database\ValueBinder $generator optional null: A placeholder object that will hold associated values for expressions

Returns

string

Implementation of

Cake\Database\ExpressionInterface::sql()

traverse() public ¶

traverse( callable $visitor , array $parts [] )

Will iterate over every specified part. Traversing functions can aggregate results using variables in the closure or instance variables. This function is commonly used as a way for traversing all query parts that are going to be used for constructing a query.

The callback will receive 2 parameters, the first one is the value of the query part that is being iterated and the second the name of such part.

Example:

$query->select(['title'])->from('articles')->traverse(function ($value, $clause) {
    if ($clause === 'select') {
        var_dump($value);
    }
}, ['select', 'from']);

Parameters

callable $visitor: A function or callable to be executed for each part
array $parts optional []: The query clauses to traverse

Returns

$this

Implementation of

Cake\Database\ExpressionInterface::traverse()

traverseExpressions() public ¶

traverseExpressions( callable $callback )

This function works similar to the traverse() function, with the difference that it does a full depth traversal of the entire expression tree. This will execute the provided callback function for each ExpressionInterface object that is stored inside this query at any nesting depth in any part of the query.

Callback will receive as first parameter the currently visited expression.

Parameters

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

Returns

$this|null

type() public ¶

type( )

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

Returns

string

union() public ¶

union( string|Cake\Database\Query $query , boolean $overwrite false )

Adds a complete query to be used in conjunction with an UNION operator with this query. This is used to combine the result set of this query with the one that will be returned by the passed query. You can add as many queries as you required by calling multiple times this method with different queries.

By default, the UNION operator will remove duplicate rows, if you wish to include every row for all queries, use unionAll().

Examples

$union = (new Query($conn))->select(['id', 'title'])->from(['a' => 'articles']);
$query->select(['id', 'name'])->from(['d' => 'things'])->union($union);

Will produce:

SELECT id, name FROM things d UNION SELECT id, title FROM articles a

Parameters

string|Cake\Database\Query $query: full SQL query to be used in UNION operator
boolean $overwrite optional false: whether to reset the list of queries to be operated or not

Returns

$this

unionAll() public ¶

unionAll( string|Cake\Database\Query $query , boolean $overwrite false )

Adds a complete query to be used in conjunction with the UNION ALL operator with this query. This is used to combine the result set of this query with the one that will be returned by the passed query. You can add as many queries as you required by calling multiple times this method with different queries.

Unlike UNION, UNION ALL will not remove duplicate rows.

$union = (new Query($conn))->select(['id', 'title'])->from(['a' => 'articles']);
$query->select(['id', 'name'])->from(['d' => 'things'])->unionAll($union);

Will produce:

SELECT id, name FROM things d UNION ALL SELECT id, title FROM articles a

Parameters

string|Cake\Database\Query $query: full SQL query to be used in UNION operator
boolean $overwrite optional false: whether to reset the list of queries to be operated or not

Returns

$this

update() public ¶

update( string|Cake\Database\ExpressionInterface $table )

Create an update query.

Can be combined with set() and where() methods to create update queries.

Parameters

string|Cake\Database\ExpressionInterface $table: The table you want to update.

Returns

$this

valueBinder() public ¶

valueBinder( Cake\Database\ValueBinder|false|null $binder null )

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

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

Deprecated

3.5.0 Use setValueBinder()/getValueBinder() instead.

Parameters

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

Returns

$this|\Cake\Database\ValueBinder

values() public ¶

values( array|Cake\Database\Query $data )

Set the values for an insert query.

Multi inserts can be performed by calling values() more than one time, or by providing an array of value sets. Additionally $data can be a Query instance to insert data from another SELECT statement.

Parameters

array|Cake\Database\Query $data: The data to insert.

Returns

$this

Throws

Cake\Database\Exception

if you try to set values before declaring columns. Or if you try to set values on non-insert queries.

where() public ¶

where( string|array|Cake\Database\ExpressionInterface|callable|null $conditions null , array $types [] , boolean $overwrite false )

Adds a condition or set of conditions to be used in the WHERE clause for this query. Conditions can be expressed as an array of fields as keys with comparison operators in it, the values for the array will be used for comparing the field to such literal. Finally, conditions can be expressed as a single string or an array of strings.

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

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

Conditions using operators:

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

The previous example produces:

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

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

Nesting conditions with conjunctions:

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

The previous example produces:

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

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

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

Would result in:

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

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

Using expressions objects:

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

The previous example produces:

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

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

Adding conditions in multiple steps:

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

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

The previous example produces:

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

Conditions as strings:

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

The previous example produces:

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

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

If you use string conditions make sure that your values are correctly quoted. The safest thing you can do is to never use string conditions.

Parameters

string|array|Cake\Database\ExpressionInterface|callable|null $conditions optional null: The conditions to filter on.
array $types optional []: associative array of type names used to bind values to query
boolean $overwrite optional false: whether to reset conditions with passed list or not

Returns

$this

See

Cake\Database\Type
Cake\Database\Expression\QueryExpression

whereInList() public ¶

whereInList( string $field , array $values , array $options [] )

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

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

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

Parameters

string $field: Field
array $values: Array of values
array $options optional []: Options

Returns

$this

whereNotInList() public ¶

whereNotInList( string $field , array $values , array $options [] )

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

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

Parameters

string $field: Field
array $values: Array of values
array $options optional []: Options

Returns

$this

whereNotNull() public ¶

whereNotNull( array|string|Cake\Database\ExpressionInterface $fields )

Convenience method that adds a NOT NULL condition to the query

Parameters

array|string|Cake\Database\ExpressionInterface $fields: A single field or expressions or a list of them that should be not null

Returns

$this

whereNull() public ¶

whereNull( array|string|Cake\Database\ExpressionInterface $fields )

Convenience method that adds a IS NULL condition to the query

Parameters

array|string|Cake\Database\ExpressionInterface $fields: A single field or expressions or a list of them that should be null

Returns

$this

Methods used from Cake\Database\TypeMapTrait

defaultTypes() public ¶

defaultTypes( array $types null )

Allows setting default types when chaining query

Deprecated

3.4.0 Use setDefaultTypes()/getDefaultTypes() instead.

Parameters

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

Returns

$this|array

getDefaultTypes() public ¶

getDefaultTypes( )

Gets default types of current type map.

Returns

array

getTypeMap() public ¶

getTypeMap( )

Returns the existing type map.

Returns

Cake\Database\TypeMap

setDefaultTypes() public ¶

setDefaultTypes( array $types )

Allows setting default types when chaining query.

Parameters

array $types: The array of types to set.

Returns

$this

setTypeMap() public ¶

setTypeMap( array|Cake\Database\TypeMap $typeMap )

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

Parameters

array|Cake\Database\TypeMap $typeMap: Creates a TypeMap if array, otherwise sets the given TypeMap

Returns

$this

typeMap() public ¶

typeMap( array|Cake\Database\TypeMap|null $typeMap null )

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

Deprecated

3.4.0 Use setTypeMap()/getTypeMap() instead.

Parameters

array|Cake\Database\TypeMap|null $typeMap optional null: Creates a TypeMap if array, otherwise sets the given TypeMap

Returns

$this|\Cake\Database\TypeMap

Properties detail

`$_connection` ¶

protected Cake\Database\Connection

Connection instance to be used to execute this query.

`$_dirty` ¶

protected boolean

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.

false

`$_functionsBuilder` ¶

protected Cake\Database\FunctionsBuilder|null

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

`$_iterator` ¶

protected Cake\Database\StatementInterface|null

Statement object resulting from executing this query.

`$_parts` ¶

protected array

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

[
    'delete' => true,
    'update' => [],
    'set' => [],
    'insert' => [],
    'values' => [],
    'select' => [],
    'distinct' => false,
    'modifier' => [],
    'from' => [],
    'join' => [],
    'where' => null,
    'group' => [],
    'having' => null,
    'order' => null,
    'limit' => null,
    'offset' => null,
    'union' => [],
    'epilog' => null
]

`$_resultDecorators` ¶

protected array

A list of callback functions to be called to alter each row from resulting statement upon retrieval. Each one of the callback function will receive the row array as first argument.

[]

`$_selectTypeMap` ¶

protected Cake\Database\TypeMap

The Type map for fields in the select clause

`$_type` ¶

protected string

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

'select'

`$_useBufferedResults` ¶

protected boolean

Boolean for tracking whether or not buffered results are enabled.

true

`$_valueBinder` ¶

protected Cake\Database\ValueBinder|null

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

`$typeCastEnabled` ¶

protected boolean

Tracking flag to disable casting

true

Namespaces

Classes

Interfaces

Traits

Exceptions

Class Query

Direct Subclasses

Properties summary

Inherited Properties

Method Summary

__clone() public

__construct() public

__debugInfo() public

__toString() public

_conjugate() protected

_decorateStatement() protected

_dirty() protected

_makeJoin() protected

andHaving() public

andWhere() public

bind() public

bufferResults() public

clause() public

connection() public

decorateResults() public

delete() public

disableBufferedResults() public

disableResultsCasting() public

distinct() public

enableBufferedResults() public

enableResultsCasting() public

epilog() public

execute() public

from() public

func() public

getConnection() public

getIterator() public

getSelectTypeMap() public

getValueBinder() public

group() public

having() public

identifier() public

innerJoin() public

insert() public

into() public

isBufferedResultsEnabled() public

join() public

leftJoin() public

limit() public

modifier() public

newExpr() public

offset() public

orHaving() public

orWhere() public

order() public

orderAsc() public

orderDesc() public

page() public

removeJoin() public

rightJoin() public

rowCountAndClose() public

select() public

selectTypeMap() public

set() public

setConnection() public

setSelectTypeMap() public

setValueBinder() public

sql() public

traverse() public

traverseExpressions() public

type() public

union() public

unionAll() public

update() public

valueBinder() public

values() public

where() public

whereInList() public

whereNotInList() public

whereNotNull() public