Class SelectQuery

Handles clearing iterator and cloning all expressions and value binders.

Handles cloning eager loaders.

Returns

Constructor

Parameters

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

Returns

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

Returns

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

Parameters

Returns

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

Returns

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

Returns

Helper function used to build conditions by composing QueryExpression objects.

Parameters

Returns

Decorates the results iterator with MapReduce routines and formatters

Parameters

Returns

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

Returns

Executes this query and returns an iterable containing the results.

Returns

Query parts traversal method used by traverseExpressions()

Parameters

Returns

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

Parameters

Returns

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

Returns

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

Specifically add the FROM clause, adds default table fields if none are specified and applies the joins required to eager load associations defined using contain

It also sets the default types for the columns in the select clause

Returns

See Also

Hints this object to associate the correct types when casting conditions for the database. This is done by extracting the field types from the schema associated to the passed table object. This prevents the user from repeating themselves when specifying conditions.

This method returns the same query object for chaining.

Parameters

Returns

Returns a key => value array representing a single aliased field that can be passed directly to the select() method. The key will contain the alias and the value the actual field name.

If the field is already aliased, then it will not be changed. If no $alias is passed, the default table for this query will be used.

Parameters

Returns

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

Parameters

Returns

Fetch the results for this query.

Will return either the results set through setResult(), or execute this query and return the ResultSetDecorator object ready for streaming of results.

ResultSetDecorator is a traversable object that implements the methods found on Cake\Collection\Collection.

Returns

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

Returns

See Also

Connects any previously defined set of conditions to the provided list using the AND operator. {@see \Cake\Database\Query::andWhere()}

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

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

Examples:

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

Will produce:

WHERE title = 'Hello World' AND author_id = 1

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

Produces:

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

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

Generates the following conditions:

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

Parameters

Returns

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

The method accepts the following query clause related options:

• fields: Maps to the select method
• conditions: Maps to the where method
• limit: Maps to the limit method
• order: Maps to the order method
• offset: Maps to the offset method
• group: Maps to the group method
• having: Maps to the having method
• contain: Maps to the contain options for eager loading
• join: Maps to the join method
• page: Maps to the page method

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

Example:

$query->applyOptions([
  'fields' => ['id', 'name'],
  'conditions' => [
    'created >=' => '2013-01-01'
  ],
  'limit' => 10,
]);

Is equivalent to:

$query
  ->select(['id', 'name'])
  ->where(['created >=' => '2013-01-01'])
  ->limit(10)

Custom options can be read via getOptions():

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

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

$options = $query->getOptions();

Parameters

Returns

See Also

Associates a query placeholder to a value and a type.

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

Parameters

Returns

Enable result caching for this query.

If a query has caching enabled, it will do the following when executed:

• Check the cache for $key. If there are results no SQL will be executed. Instead the cached results will be returned.
• When the cached data is stale/missing the result set will be cached as the query is executed.

Usage

// Simple string key + config
$query->cache('my_key', 'db_results');

// Function to generate key.
$query->cache(function ($q) {
  $key = serialize($q->clause('select'));
  $key .= serialize($q->clause('where'));
  return md5($key);
});

// Using a pre-built cache engine.
$query->cache('my_key', $engine);

// Disable caching
$query->cache(false);

Parameters

Returns

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

Returns

Throws

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

The following state will be cleared:

• autoFields
• limit
• offset
• map/reduce functions
• result formatters
• order
• containments

This method creates query clones that are useful when working with subqueries.

Returns

Clears the contained associations from the current query.

Returns

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

Returns

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

Returns

Sets the list of associations that should be eagerly loaded along with this query. The list of associated tables passed must have been previously set as associations using the Table API.

Example:

// Bring articles' author information
$query->contain('Author');

// Also bring the category and tags associated to each article
$query->contain(['Category', 'Tag']);

Associations can be arbitrarily nested using dot notation or nested arrays, this allows this object to calculate joins or any additional queries that must be executed to bring the required associated data.

Example:

// Eager load the product info, and for each product load other 2 associations
$query->contain(['Product' => ['Manufacturer', 'Distributor']);

// Which is equivalent to calling
$query->contain(['Products.Manufactures', 'Products.Distributors']);

// For an author query, load his region, state and country
$query->contain('Regions.States.Countries');

It is possible to control the conditions and fields selected for each of the contained associations:

Example:

$query->contain(['Tags' => function ($q) {
    return $q->where(['Tags.is_popular' => true]);
}]);

$query->contain(['Products.Manufactures' => function ($q) {
    return $q->select(['name'])->where(['Manufactures.active' => true]);
}]);

Each association might define special options when eager loaded, the allowed options that can be set per association are:

• foreignKey: Used to set a different field to match both tables, if set to false no join conditions will be generated automatically. false can only be used on joinable associations and cannot be used with hasMany or belongsToMany associations.
• fields: An array with the fields that should be fetched from the association.
• finder: The finder to use when loading associated records. Either the name of the finder as a string, or an array to define options to pass to the finder.
• queryBuilder: Equivalent to passing a callback instead of an options array.

Example:

// Set options for the hasMany articles that will be eagerly loaded for an author
$query->contain([
    'Articles' => [
        'fields' => ['title', 'author_id']
    ]
]);

Finders can be configured to use options.

// Retrieve translations for the articles, but only those for the `en` and `es` locales
$query->contain([
    'Articles' => [
        'finder' => [
            'translations' => [
                'locales' => ['en', 'es']
            ]
        ]
    ]
]);

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

// Use a query builder to add conditions to the containment
$query->contain('Authors', function ($q) {
    return $q->where(...); // add conditions
});
// Use special join conditions for multiple containments in the same method call
$query->contain([
    'Authors' => [
        'foreignKey' => false,
        'queryBuilder' => function ($q) {
            return $q->where(...); // Add full filtering conditions
        }
    ],
    'Tags' => function ($q) {
        return $q->where(...); // add conditions
    }
]);

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

Parameters

Returns

Returns the total amount of results for the query.

Returns the COUNT(*) for the query. If the query has not been modified, and the count has already been performed the cached value is returned

Returns

Registers a callback that will be executed when the count method in this query is called. The return value for the function will be set as the return value of the count method.

This is particularly useful when you need to optimize a query for returning the count, for example removing unnecessary joins, removing group by or just return an estimated number of rows.

The callback will receive as first argument a clone of this query and not this query itself.

If the first param is a null value, the built-in counter function will be called instead

Parameters

Returns

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

Returns

Disable auto adding table's alias to the fields of SELECT clause.

Returns

Disables automatically appending fields.

Returns

Disables buffered results.

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

Returns

Disable hydrating entities.

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

Returns

Disables result casting.

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

Returns

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

Returns

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

Parameters

Returns

Sets whether the ORM should automatically append fields.

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

Parameters

Returns

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

Returns

Toggle hydrating entities.

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

Parameters

Returns

Enables result casting.

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

Returns

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

Returns

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

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

Returns

Apply custom finds to against an existing query object.

Allows custom find methods to be combined and applied to each other.

$repository->find('all')->find('recent');

The above is an example of stacking multiple finder methods onto a single query.

Parameters

Returns

Returns the first result out of executing this query, if the query has not been executed before, it will set the limit clause to 1 for performance reasons.

Example:

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

Returns

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

Returns

Throws

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

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

Callbacks are required to return an iterator object, which will be used as the return value for this query's result. Formatter functions are applied after all the MapReduce routines for this query have been executed.

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

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

Examples:

Return all results from the table indexed by id:

$query->select(['id', 'name'])->formatResults(function ($results) {
    return $results->indexBy('id');
});

Add a new column to the ResultSet:

$query->select(['name', 'birth_date'])->formatResults(function ($results) {
    return $results->map(function ($row) {
        $row['age'] = $row['birth_date']->diff(new DateTime)->y;

return $row;
    });
});

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

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

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

return $row;
    });
});

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

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

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

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

// ...

return $results;
    });
});

Parameters

Returns

Throws

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

Returns

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

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

Returns

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

Returns

Returns

Gets default types of current type map.

Returns

Returns the currently configured instance.

Returns

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

Returns the list of previously registered map reduce routines.

Returns

Returns an array with the custom options that were applied to this query and that were not already processed by another method in this class.

Example:

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

Returns

See Also

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

Returns

Get result decorators.

Returns

Returns the list of previously registered format routines.

Returns

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

Returns

Returns the existing type map.

Returns

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

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->groupBy(['id', 'title']);

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

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

Parameters

Returns

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->groupBy(['id', 'title']);

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

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

Parameters

Returns

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

Returns

See Also

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

Returns

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

Returns

Creates an INNER JOIN with the passed association table while preserving the foreign key matching and the custom conditions that were originally set for it.

This function will add entries in the contain graph.

Example:

// Bring only articles that were tagged with 'cake'
$query->innerJoinWith('Tags', function ($q) {
    return $q->where(['name' => 'cake']);
});

This will create the following SQL:

SELECT Articles.*
FROM articles Articles
INNER JOIN tags Tags ON Tags.name = 'cake'
INNER JOIN articles_tags ArticlesTags ON ArticlesTags.tag_id = Tags.id
  AND ArticlesTags.articles_id = Articles.id

This function works the same as matching() with the difference that it will select no fields from the association.

Parameters

Returns

See Also

Adds a complete query to be used in conjunction with an INTERSECT 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 INTERSECT operator will remove duplicate rows, if you wish to include every row for all queries, use intersectAll().

Examples

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

Will produce:

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

Parameters

Returns

Adds a complete query to be used in conjunction with the INTERSECT 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 INTERSECT, INTERSECT ALL will not remove duplicate rows.

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

Will produce:

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

Parameters

Returns

Gets whether the ORM should automatically append fields.

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

Returns

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

Returns the current configured query _eagerLoaded value

Returns

Returns the current hydration mode.

Returns

Returns whether result casting is enabled/disabled.

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

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

Returns

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

Returns

See Also

Executes the query and converts the result set into JSON.

Part of JsonSerializable interface.

Returns

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

Returns

Creates a LEFT JOIN with the passed association table while preserving the foreign key matching and the custom conditions that were originally set for it.

This function will add entries in the contain graph.

Example:

// Get the count of articles per user
$usersQuery
    ->select(['total_articles' => $query->func()->count('Articles.id')])
    ->leftJoinWith('Articles')
    ->groupBy(['Users.id'])
    ->enableAutoFields();

You can also customize the conditions passed to the LEFT JOIN:

// Get the count of articles per user with at least 5 votes
$usersQuery
    ->select(['total_articles' => $query->func()->count('Articles.id')])
    ->leftJoinWith('Articles', function ($q) {
        return $q->where(['Articles.votes >=' => 5]);
    })
    ->groupBy(['Users.id'])
    ->enableAutoFields();

This will create the following SQL:

SELECT COUNT(Articles.id) AS total_articles, Users.*
FROM users Users
LEFT JOIN articles Articles ON Articles.user_id = Users.id AND Articles.votes >= 5
GROUP BY USers.id

It is possible to left join deep associations by using dot notation

Example:

// Total comments in articles by 'markstory'
$query
    ->select(['total_comments' => $query->func()->count('Comments.id')])
    ->leftJoinWith('Comments.Users', function ($q) {
        return $q->where(['username' => 'markstory']);
    })
   ->groupBy(['Users.id']);

Please note that the query passed to the closure will only accept calling select, where, andWhere and orWhere on it. If you wish to add more complex clauses you can do it directly in the main query.

Parameters

Returns

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

Returns

Register a new MapReduce routine to be executed on top of the database results

The MapReduce routing will only be run when the query is executed and the first result is attempted to be fetched.

If the third argument is set to true, it will erase previous map reducers and replace it with the arguments passed.

Parameters

Returns

See Also

Adds filtering conditions to this query to only bring rows that have a relation to another from an associated table, based on conditions in the associated table.

This function will add entries in the contain graph.

Example:

// Bring only articles that were tagged with 'cake'
$query->matching('Tags', function ($q) {
    return $q->where(['name' => 'cake']);
});

It is possible to filter by deep associations by using dot notation:

Example:

// Bring only articles that were commented by 'markstory'
$query->matching('Comments.Users', function ($q) {
    return $q->where(['username' => 'markstory']);
});

As this function will create INNER JOIN, you might want to consider calling distinct on this query as you might get duplicate rows if your conditions don't filter them already. This might be the case, for example, of the same user commenting more than once in the same article.

Example:

// Bring unique articles that were commented by 'markstory'
$query->distinct(['Articles.id'])
    ->matching('Comments.Users', function ($q) {
        return $q->where(['username' => 'markstory']);
    });

Please note that the query passed to the closure will only accept calling select, where, andWhere and orWhere on it. If you wish to add more complex clauses you can do it directly in the main query.

Parameters

Returns

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

Returns

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

Returns

Adds filtering conditions to this query to only bring rows that have no match to another from an associated table, based on conditions in the associated table.

This function will add entries in the contain graph.

Example:

// Bring only articles that were not tagged with 'cake'
$query->notMatching('Tags', function ($q) {
    return $q->where(['name' => 'cake']);
});

It is possible to filter by deep associations by using dot notation:

Example:

// Bring only articles that weren't commented by 'markstory'
$query->notMatching('Comments.Users', function ($q) {
    return $q->where(['username' => 'markstory']);
});

As this function will create a LEFT JOIN, you might want to consider calling distinct on this query as you might get duplicate rows if your conditions don't filter them already. This might be the case, for example, of the same article having multiple comments.

Example:

// Bring unique articles that were commented by 'markstory'
$query->distinct(['Articles.id'])
    ->notMatching('Comments.Users', function ($q) {
        return $q->where(['username' => 'markstory']);
    });

Please note that the query passed to the closure will only accept calling select, where, andWhere and orWhere on it. If you wish to add more complex clauses you can do it directly in the main query.

Parameters

Returns

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

Returns

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

Will become:

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

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

Parameters

Returns

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

Returns

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

Will become:

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

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

Parameters

Returns

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

Returns

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

Returns

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

Returns

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

Returns

Throws

Remove a join if it has been defined.

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

Parameters

Returns

Get resultset factory.

Returns

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

Returns

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

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 callback is passed, the returning array of the function will be used as the list of fields.

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

Examples:

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

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

If you pass an instance of a Cake\ORM\Table or Cake\ORM\Association class, all the fields in the schema of the table or the association will be added to the select clause.

Parameters

Returns

All the fields associated with the passed table except the excluded fields will be added to the select clause of the query. Passed excluded fields should not be aliased. After the first call to this method, a second call cannot be used to remove fields that have already been added to the query by the first. If you need to change the list after the first call, pass overwrite boolean true which will reset the select clause removing all previous additions.

Parameters

Returns

Behaves the exact same as select() except adds the field to the list of fields selected and does not disable auto-selecting fields for Associations.

Use this instead of calling select() then enableAutoFields() to re-enable auto-fields.

Parameters

Returns

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

Parameters

Returns

Sets the connection role.

Parameters

Returns

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

Returns

See Also

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

Parameters

Returns

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

Parameters

Returns

Set the result set for a query.

Setting the resultset of a query will make execute() a no-op. Instead of executing the SQL query and fetching results, the ResultSet provided to this method will be returned.

This method is most useful when combined with results stored in a persistent cache.

Parameters

Returns

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

Parameters

Returns

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

Parameters

Returns

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

Returns

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

Returns

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

Returns

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

Returns

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

Returns

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

Returns

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

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

Returns

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

Returns

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

Returns

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

Returns

Sets the connection role to read.

Returns

Sets the connection role to write.

Returns

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

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

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

Conditions using operators:

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

The previous example produces:

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

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

Nesting conditions with conjunctions:

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

The previous example produces:

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

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

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

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

Using expressions objects:

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

The previous example produces:

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

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

Adding conditions in multiple steps:

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

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

• The previous example produces:

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

Conditions as strings:

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

The previous example produces:

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

Please note that when using the array notation or the expression objects, all values will be correctly quoted and transformed to the correspondent database data type automatically for you, thus securing your application from SQL injections. If you use string conditions make sure that your values are correctly quoted. The safest thing you can do is to never use string conditions.

Parameters

Returns

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

Returns

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

Returns

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

Returns

Convenience method that adds a NOT NULL condition to the query

Parameters

Returns

Convenience method that adds a IS NULL condition to the query

Parameters

Returns

Adds a named window expression.

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

Parameters

Returns

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

Returns