CakePHP
  • Documentation
    • Book
    • API
    • Videos
    • Reporting Security Issues
    • Privacy Policy
    • Logos & Trademarks
  • Business Solutions
  • Swag
  • Road Trip
  • Team
  • Community
    • Community
    • Get Involved
    • Issues (Github)
    • Bakery
    • Featured Resources
    • Training
    • Meetups
    • My CakePHP
    • CakeFest
    • Newsletter
    • Linkedin
    • YouTube
    • Facebook
    • Twitter
    • Mastodon
    • Help & Support
    • Forum
    • Stack Overflow
    • IRC
    • Slack
    • Paid Support
CakePHP

C CakePHP 5.1 Chiffon API

  • Project:
    • CakePHP
      • CakePHP
      • Authentication
      • Authorization
      • Chronos
      • Elastic Search
      • Queue
  • Version:
    • 5.1
      • 5.2
      • 5.1
      • 5.0
      • 4.6
      • 4.5
      • 4.4
      • 4.3
      • 4.2
      • 4.1
      • 4.0
      • 3.10
      • 3.9
      • 3.8
      • 3.7
      • 3.6
      • 3.5
      • 3.4
      • 3.3
      • 3.2
      • 3.1
      • 3.0
      • 2.10
      • 2.9
      • 2.8
      • 2.7
      • 2.6
      • 2.5
      • 2.4
      • 2.3
      • 2.2
      • 2.1
      • 2.0
      • 1.3
      • 1.2

Namespaces

  • Global
  • Cake
    • Cache
    • Collection
    • Command
    • Console
    • Controller
    • Core
    • Database
      • Driver
      • Exception
      • Expression
      • Log
      • Query
      • Retry
      • Schema
      • Statement
      • Type
    • Datasource
    • Error
    • Event
    • Form
    • Http
    • I18n
    • Log
    • Mailer
    • Network
    • ORM
    • Routing
    • TestSuite
    • Utility
    • Validation
    • View

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.

Abstract
Namespace: Cake\Database

Constants

  • string
    JOIN_TYPE_INNER ¶
    'INNER'
  • string
    JOIN_TYPE_LEFT ¶
    'LEFT'
  • string
    JOIN_TYPE_RIGHT ¶
    'RIGHT'
  • string
    TYPE_DELETE ¶
    'delete'
  • string
    TYPE_INSERT ¶
    'insert'
  • string
    TYPE_SELECT ¶
    'select'
  • string
    TYPE_UPDATE ¶
    'update'

Property Summary

  • $_connection protected
    Cake\Database\Connection

    Connection instance to be used to execute this query.

  • $_dirty protected
    bool

    Indicates whether internal state of this query was changed, this is used to discard internal cached objects such as the transformed query or the reference to the executed statement.

  • $_functionsBuilder protected
    Cake\Database\FunctionsBuilder|null

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

  • $_parts protected
    array<string, mixed>

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

  • $_statement protected
    Cake\Database\StatementInterface|null
  • $_type protected
    string

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

  • $_typeMap protected
    Cake\Database\TypeMap|null
  • $_valueBinder protected
    Cake\Database\ValueBinder|null

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

  • $connectionRole protected
    string

    Connection role ('read' or 'write')

Method Summary

  • __clone() public

    Handles clearing iterator and cloning all expressions and value binders.

  • __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.

  • _dirty() protected

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

  • _expressionsVisitor() protected

    Query parts traversal method used by traverseExpressions()

  • _makeJoin() protected

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

  • 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.

  • 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, union and intersect.

  • comment() public

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

  • 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.

  • expr() 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.

  • 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.

  • getConnectionRole() public

    Returns the connection role ('read' or 'write')

  • getDefaultTypes() public

    Gets default types of current type map.

  • getTypeMap() public

    Returns the existing type map.

  • getValueBinder() public

    Returns the currently used ValueBinder instance.

  • 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.

  • 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.

  • order() public deprecated

    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 deprecated

    Add an ORDER BY clause with an ASC direction.

  • orderBy() 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.

  • orderByAsc() public

    Add an ORDER BY clause with an ASC direction.

  • orderByDesc() public

    Add an ORDER BY clause with a DESC direction.

  • orderDesc() public deprecated

    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.

  • setConnection() public

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

  • setDefaultTypes() public

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

  • setTypeMap() public

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

  • setValueBinder() public

    Overwrite the current value binder

  • 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.

  • traverseParts() public

    Will iterate over the provided parts.

  • type() public

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

  • 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.

  • whereNotInListOrNull() public

    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() 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

  • with() public

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

Method Detail

__clone() ¶ public

__clone(): void

Handles clearing iterator and cloning all expressions and value binders.

Returns
void

__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(): array<string, mixed>

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

Returns
array<string, mixed>

__toString() ¶ public

__toString(): string

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

Returns
string

_conjugate() ¶ protected

_conjugate(string $part, Cake\Database\ExpressionInterface|Closure|array|string|null $append, string $conjunction, array<string, string> $types): void

Helper function used to build conditions by composing QueryExpression objects.

Parameters
string $part

Name of the query part to append the new part to

Cake\Database\ExpressionInterface|Closure|array|string|null $append

Expression or builder function to append. to append.

string $conjunction

type of conjunction to be used to operate part

array<string, string> $types

Associative array of type names used to bind values to query

Returns
void

_dirty() ¶ protected

_dirty(): void

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

Returns
void

_expressionsVisitor() ¶ protected

_expressionsVisitor(mixed $expression, Closure $callback): void

Query parts traversal method used by traverseExpressions()

Parameters
mixed $expression

Query expression or array of expressions.

Closure $callback

The callback to be executed for each ExpressionInterface found inside this query.

Returns
void

_makeJoin() ¶ protected

_makeJoin(array<string, string|Cake\Database\Query\SelectQuery>|string $table, Cake\Database\ExpressionInterface|Closure|array|string $conditions, string $type): array<string, array{table: (string|Cake\Database\Query\SelectQuery, conditions: (Cake\Database\ExpressionInterface|Closure|array|string, type: string}>

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

Parameters
array<string, string|Cake\Database\Query\SelectQuery>|string $table

The table to join with

Cake\Database\ExpressionInterface|Closure|array|string $conditions

The conditions to use for joining.

string $type

the join type to use

Returns
array<string, array{table: (string|Cake\Database\Query\SelectQuery, conditions: (Cake\Database\ExpressionInterface|Closure|array|string, type: string}>

andWhere() ¶ public

andWhere(Cake\Database\ExpressionInterface|Closure|array|string $conditions, array<string, string> $types = []): $this

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

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

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

Examples:

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

Will produce:

WHERE title = 'Hello World' AND author_id = 1

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

Produces:

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

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

Generates the following conditions:

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

Parameters
Cake\Database\ExpressionInterface|Closure|array|string $conditions

The conditions to add with AND.

array<string, string> $types optional

Associative array of type names used to bind values to query

Returns
$this
See Also
\Cake\Database\Query::where()
\Cake\Database\TypeFactory

bind() ¶ public

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

Associates a query placeholder to a value and a type.

$query->bind(':id', 1, 'integer');
Parameters
string|int $param

placeholder to be replaced with quoted version of $value

mixed $value

The value to be bound

string|int|null $type optional

the mapped type name, used for casting when sending to database

Returns
$this

clause() ¶ public

clause(string $name): mixed

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

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
  • intersect: array
Parameters
string $name

name of the clause to be returned

Returns
mixed
Throws
InvalidArgumentException
When the named clause does not exist.

comment() ¶ public

comment(string|null $expression = null): $this

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

Examples:

$query->select('id')->where(['author_id' => 1])->comment('Filter for admin user');

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

Parameters
string|null $expression optional

The comment to be added

Returns
$this

epilog() ¶ public

epilog(Cake\Database\ExpressionInterface|string|null $expression = null): $this

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

Examples:

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

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

Parameters
Cake\Database\ExpressionInterface|string|null $expression optional

The expression to be appended

Returns
$this

execute() ¶ public

execute(): Cake\Database\StatementInterface

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

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

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

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

Returns
Cake\Database\StatementInterface

expr() ¶ public

expr(Cake\Database\ExpressionInterface|array|string|null $rawExpression = null): Cake\Database\Expression\QueryExpression

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

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

$expression = $query->expr(); // Returns an empty expression object
$expression = $query->expr('Table.column = Table2.column'); // Return a raw SQL expression
Parameters
Cake\Database\ExpressionInterface|array|string|null $rawExpression optional

A string, array or anything you want wrapped in an expression object

Returns
Cake\Database\Expression\QueryExpression

from() ¶ public

from(array|string $tables = [], bool $overwrite = false): $this

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

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

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

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

Examples:

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

tables to be added to the list. This argument, can be passed as an array of strings, array of expression objects, or a single string. See the examples above for the valid call types.

bool $overwrite optional

whether to reset tables with passed list or not

Returns
$this

func() ¶ public

func(): Cake\Database\FunctionsBuilder

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

Example:

$query->func()->count('*');
$query->func()->dateDiff(['2012-01-05', '2012-01-02'])
Returns
Cake\Database\FunctionsBuilder

getConnection() ¶ public

getConnection(): Cake\Database\Connection

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

Returns
Cake\Database\Connection

getConnectionRole() ¶ public

getConnectionRole(): string

Returns the connection role ('read' or 'write')

Returns
string

getDefaultTypes() ¶ public

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

Gets default types of current type map.

Returns
array<int|string, string>

getTypeMap() ¶ public

getTypeMap(): Cake\Database\TypeMap

Returns the existing type map.

Returns
Cake\Database\TypeMap

getValueBinder() ¶ public

getValueBinder(): Cake\Database\ValueBinder

Returns the currently used ValueBinder instance.

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

Returns
Cake\Database\ValueBinder

identifier() ¶ public

identifier(string $identifier): Cake\Database\ExpressionInterface

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

The value is used as is, and you might be required to use aliases or include the table reference in the identifier. Do not use this method to inject SQL methods or logical statements.

Example

$query->newExpr()->lte('count', $query->identifier('total'));
Parameters
string $identifier

The identifier for an expression

Returns
Cake\Database\ExpressionInterface

innerJoin() ¶ public

innerJoin(array<string, string|Cake\Database\Query\SelectQuery>|string $table, Cake\Database\ExpressionInterface|Closure|array|string $conditions = [], array<string, string> $types = []): $this

Adds a single INNER JOIN clause to the query.

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

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

Parameters
array<string, string|Cake\Database\Query\SelectQuery>|string $table

The table to join with

Cake\Database\ExpressionInterface|Closure|array|string $conditions optional

The conditions to use for joining.

array<string, string> $types optional

a list of types associated to the conditions used for converting values to the corresponding database representation.

Returns
$this

join() ¶ public

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

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

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

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

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

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

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

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

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

Using conditions and types

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

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

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

Overwriting joins

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

$query->join(['alias' => 'table']); // joins table with as alias
$query->join(['alias' => 'another_table']); // joins another_table with as alias
$query->join(['something' => 'different_table'], [], true); // resets joins list
Parameters
array<string, mixed>|string $tables

list of tables to be joined in the query

array<string, string> $types optional

Associative array of type names used to bind values to query

bool $overwrite optional

whether to reset joins with passed list or not

Returns
$this
See Also
\Cake\Database\TypeFactory

leftJoin() ¶ public

leftJoin(array<string, string|Cake\Database\Query\SelectQuery>|string $table, Cake\Database\ExpressionInterface|Closure|array|string $conditions = [], array $types = []): $this

Adds a single LEFT JOIN clause to the query.

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

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

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

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

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

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

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

Parameters
array<string, string|Cake\Database\Query\SelectQuery>|string $table

The table to join with

Cake\Database\ExpressionInterface|Closure|array|string $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(Cake\Database\ExpressionInterface|int|null $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. 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
Cake\Database\ExpressionInterface|int|null $limit

number of records to be returned

Returns
$this

modifier() ¶ public

modifier(Cake\Database\ExpressionInterface|array|string $modifiers, bool $overwrite = false): $this

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

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

Example:

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

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

modifiers to be applied to the query

bool $overwrite optional

whether to reset order with field list or not

Returns
$this

newExpr() ¶ public

newExpr(Cake\Database\ExpressionInterface|array|string|null $rawExpression = null): Cake\Database\Expression\QueryExpression

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

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

$expression = $query->expr(); // Returns an empty expression object
$expression = $query->expr('Table.column = Table2.column'); // Return a raw SQL expression
Parameters
Cake\Database\ExpressionInterface|array|string|null $rawExpression optional

A string, array or anything you want wrapped in an expression object

Returns
Cake\Database\Expression\QueryExpression

offset() ¶ public

offset(Cake\Database\ExpressionInterface|int|null $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.

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
Cake\Database\ExpressionInterface|int|null $offset

number of records to be skipped

Returns
$this

order() ¶ public

order(Cake\Database\ExpressionInterface|Closure|array|string $fields, bool $overwrite = false): $this

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

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

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

Examples:

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

Produces:

ORDER BY title DESC, author_id ASC

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

Will generate:

ORDER BY title DESC NULLS FIRST, author_id

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

and

$query->orderBy(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 orderByAsc() or orderByDesc().

Parameters
Cake\Database\ExpressionInterface|Closure|array|string $fields

fields to be added to the list

bool $overwrite optional

whether to reset order with field list or not

Returns
$this

orderAsc() ¶ public

orderAsc(Cake\Database\ExpressionInterface|Closure|string $field, bool $overwrite = false): $this

Add an ORDER BY clause with an ASC direction.

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

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

Parameters
Cake\Database\ExpressionInterface|Closure|string $field

The field to order on.

bool $overwrite optional

Whether to reset the order clauses.

Returns
$this

orderBy() ¶ public

orderBy(Cake\Database\ExpressionInterface|Closure|array|string $fields, bool $overwrite = false): $this

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

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

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

Examples:

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

Produces:

ORDER BY title DESC, author_id ASC

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

Will generate:

ORDER BY title DESC NULLS FIRST, author_id

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

and

$query->orderBy(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 orderByAsc() or orderByDesc().

Parameters
Cake\Database\ExpressionInterface|Closure|array|string $fields

fields to be added to the list

bool $overwrite optional

whether to reset order with field list or not

Returns
$this

orderByAsc() ¶ public

orderByAsc(Cake\Database\ExpressionInterface|Closure|string $field, bool $overwrite = false): $this

Add an ORDER BY clause with an ASC direction.

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

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

Parameters
Cake\Database\ExpressionInterface|Closure|string $field

The field to order on.

bool $overwrite optional

Whether to reset the order clauses.

Returns
$this

orderByDesc() ¶ public

orderByDesc(Cake\Database\ExpressionInterface|Closure|string $field, bool $overwrite = false): $this

Add an ORDER BY clause with a DESC direction.

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

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

Parameters
Cake\Database\ExpressionInterface|Closure|string $field

The field to order on.

bool $overwrite optional

Whether to reset the order clauses.

Returns
$this

orderDesc() ¶ public

orderDesc(Cake\Database\ExpressionInterface|Closure|string $field, bool $overwrite = false): $this

Add an ORDER BY clause with a DESC direction.

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

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

Parameters
Cake\Database\ExpressionInterface|Closure|string $field

The field to order on.

bool $overwrite optional

Whether to reset the order clauses.

Returns
$this

page() ¶ public

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

Set the page of results you want.

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

Pages must start at 1.

Parameters
int $num

The page number you want.

int|null $limit optional

The number of rows you want in the page. If null the current limit clause will be used.

Returns
$this
Throws
InvalidArgumentException
If page number < 1.

removeJoin() ¶ public

removeJoin(string $name): $this

Remove a join if it has been defined.

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

Parameters
string $name

The alias/name of the join to remove.

Returns
$this

rightJoin() ¶ public

rightJoin(array<string, string|Cake\Database\Query\SelectQuery>|string $table, Cake\Database\ExpressionInterface|Closure|array|string $conditions = [], array $types = []): $this

Adds a single RIGHT JOIN clause to the query.

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

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

Parameters
array<string, string|Cake\Database\Query\SelectQuery>|string $table

The table to join with

Cake\Database\ExpressionInterface|Closure|array|string $conditions optional

The conditions to use for joining.

array $types optional

a list of types associated to the conditions used for converting values to the corresponding database representation.

Returns
$this

rowCountAndClose() ¶ public

rowCountAndClose(): int

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

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

Example

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

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

Returns
int

setConnection() ¶ public

setConnection(Cake\Database\Connection $connection): $this

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

Parameters
Cake\Database\Connection $connection

Connection instance

Returns
$this

setDefaultTypes() ¶ public

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

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

This method is useful if you need to set type mappings that are shared across multiple functions/expressions in a query.

To add a default without overwriting existing ones use getTypeMap()->addDefaults()

Parameters
array<int|string, string> $types

The array of types to set.

Returns
$this
See Also
\Cake\Database\TypeMap::setDefaults()

setTypeMap() ¶ public

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

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

Parameters
Cake\Database\TypeMap|array $typeMap

Creates a TypeMap if array, otherwise sets the given TypeMap

Returns
$this

setValueBinder() ¶ public

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

Overwrite the current value binder

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

Parameters
Cake\Database\ValueBinder|null $binder

The binder or null to disable binding.

Returns
$this

sql() ¶ public

sql(Cake\Database\ValueBinder|null $binder = null): string

Returns the SQL representation of this object.

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

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

To get the fully rendered query with the placeholders replaced with the actual values, (string)$query should be used, instead.

Parameters
Cake\Database\ValueBinder|null $binder optional

Value binder that generates parameter placeholders

Returns
string

traverse() ¶ public

traverse(Closure $callback): $this

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

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

Example

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

Callback to be executed for each part

Returns
$this

traverseExpressions() ¶ public

traverseExpressions(Closure $callback): $this

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

Callback will receive as first parameter the currently visited expression.

Parameters
Closure $callback

the function to be executed for each ExpressionInterface found inside this query.

Returns
$this

traverseParts() ¶ public

traverseParts(Closure $visitor, array<string> $parts): $this

Will iterate over the provided parts.

Traversing functions can aggregate results using variables in the closure or instance variables. This method can be used to traverse a subset of query parts in order to render a SQL query.

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

Example

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

Callback executed for each part

array<string> $parts

The list of query parts to traverse

Returns
$this

type() ¶ public

type(): string

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

Returns
string

where() ¶ public

where(Cake\Database\ExpressionInterface|Closure|array|string|null $conditions = null, array<string, string> $types = [], bool $overwrite = false): $this

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

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

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

Conditions using operators:

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

The previous example produces:

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

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

Nesting conditions with conjunctions:

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

The previous example produces:

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

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

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

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 callbacks 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
Cake\Database\ExpressionInterface|Closure|array|string|null $conditions optional

The conditions to filter on.

array<string, string> $types optional

Associative array of type names used to bind values to query

bool $overwrite optional

whether to reset conditions with passed list or not

Returns
$this
See Also
\Cake\Database\TypeFactory
\Cake\Database\Expression\QueryExpression

whereInList() ¶ public

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

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

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

Options:

  • types - Associative array of type names used to bind values to query
  • allowEmpty - Allow empty array.
Parameters
string $field

Field

array $values

Array of values

array<string, mixed> $options optional

Options

Returns
$this

whereNotInList() ¶ public

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

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

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

Parameters
string $field

Field

array $values

Array of values

array<string, mixed> $options optional

Options

Returns
$this

whereNotInListOrNull() ¶ public

whereNotInListOrNull(string $field, array $values, array<string, mixed> $options = []): $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.

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<string, mixed> $options optional

Options

Returns
$this

whereNotNull() ¶ public

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

Convenience method that adds a NOT NULL condition to the query

Parameters
Cake\Database\ExpressionInterface|array|string $fields

A single field or expressions or a list of them that should be not null.

Returns
$this

whereNull() ¶ public

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

Convenience method that adds a IS NULL condition to the query

Parameters
Cake\Database\ExpressionInterface|array|string $fields

A single field or expressions or a list of them that should be null.

Returns
$this

with() ¶ public

with(Cake\Database\Expression\CommonTableExpression|Closure $cte, bool $overwrite = false): $this

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

Examples:

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

$cte = new \Cake\Database\Expression\CommonTableExpression(
    'cte',
    $connection
        ->selectQuery('*')
        ->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 select 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
Cake\Database\Expression\CommonTableExpression|Closure $cte

The CTE to add.

bool $overwrite optional

Whether to reset the list of CTEs.

Returns
$this

Property Detail

$_connection ¶ protected

Connection instance to be used to execute this query.

Type
Cake\Database\Connection

$_dirty ¶ protected

Indicates whether internal state of this query was changed, this is used to discard internal cached objects such as the transformed query or the reference to the executed statement.

Type
bool

$_functionsBuilder ¶ protected

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

Type
Cake\Database\FunctionsBuilder|null

$_parts ¶ protected

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

Type
array<string, mixed>

$_statement ¶ protected

Type
Cake\Database\StatementInterface|null

$_type ¶ protected

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

Type
string

$_typeMap ¶ protected

Type
Cake\Database\TypeMap|null

$_valueBinder ¶ protected

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

Type
Cake\Database\ValueBinder|null

$connectionRole ¶ protected

Connection role ('read' or 'write')

Type
string
OpenHub
Pingping
Linode
  • Business Solutions
  • Showcase
  • Documentation
  • Book
  • API
  • Videos
  • Reporting Security Issues
  • Privacy Policy
  • Logos & Trademarks
  • Community
  • Get Involved
  • Issues (Github)
  • Bakery
  • Featured Resources
  • Training
  • Meetups
  • My CakePHP
  • CakeFest
  • Newsletter
  • Linkedin
  • YouTube
  • Facebook
  • Twitter
  • Mastodon
  • Help & Support
  • Forum
  • Stack Overflow
  • IRC
  • Slack
  • Paid Support

Generated using CakePHP API Docs