Query Builder

Query Builder

class Cake\ORM\Query
The ORM’s query builder provides a simple to use fluent interface for creatingand running queries. By composing queries together, you can create advancedqueries using unions and subqueries with ease.

Underneath the covers, the query builder uses PDO prepared statements whichprotect against SQL injection attacks.

The Query Object

The easiest way to create a Query object is to use find() from aTable object. This method will return an incomplete query ready to bemodified. You can also use a table’s connection object to access the lower levelQuery builder that does not include ORM features, if necessary. See theExecuting Queries section for more information:

use Cake\ORM\TableRegistry;
$articles = TableRegistry::get('Articles');
 
// Start a new query.
$query = $articles->find();

When inside a controller, you can use the automatic table variable that iscreated using the conventions system:

// Inside ArticlesController.php
 
$query = $this->Articles->find();

Selecting Rows From A Table

use Cake\ORM\TableRegistry;
 
$query = TableRegistry::get('Articles')->find();
 
foreach ($query as $article) {
    debug($article->title);
}

For the remaining examples, assume that $articles is aORM\Table. When inside controllers, you can use$this->Articles instead of $articles.

Almost every method in a Query object will return the same query, this meansthat Query objects are lazy, and will not be executed unless you tell themto:

$query->where(['id' => 1]); // Return the same query object
$query->order(['title' => 'DESC']); // Still same object, no SQL executed

You can of course chain the methods you call on Query objects:

$query = $articles
    ->find()
    ->select(['id', 'name'])
    ->where(['id !=' => 1])
    ->order(['created' => 'DESC']);
 
foreach ($query as $article) {
    debug($article->created);
}

If you try to call debug() on a Query object, you will see its internalstate and the SQL that will be executed in the database:

debug($articles->find()->where(['id' => 1]));
 
// Outputs
// ...
// 'sql' => 'SELECT * FROM articles where id = ?'
// ...

You can execute a query directly without having to use foreach on it.The easiest way is to either call the all() or toList() methods:

$resultsIteratorObject = $articles
    ->find()
    ->where(['id >' => 1])
    ->all();
 
foreach ($resultsIteratorObject as $article) {
    debug($article->id);
}
 
$resultsArray = $articles
    ->find()
    ->where(['id >' => 1])
    ->toList();
 
foreach ($resultsArray as $article) {
    debug($article->id);
}
 
debug($resultsArray[0]->title);

In the above example, $resultsIteratorObject will be an instance ofCake\ORM\ResultSet, an object you can iterate and apply several extractingand traversing methods on.

Often, there is no need to call all(), you can simply iterate theQuery object to get its results. Query objects can also be used directly as theresult object; trying to iterate the query, calling toList() or some of themethods inherited from Collection, willresult in the query being executed and results returned to you.

Selecting A Single Row From A Table

You can use the first() method to get the first result in the query:

$article = $articles
    ->find()
    ->where(['id' => 1])
    ->first();
 
debug($article->title);

Getting A List Of Values From A Column

// Use the extract() method from the collections library
// This executes the query as well
$allTitles = $articles->find()->extract('title');
 
foreach ($allTitles as $title) {
    echo $title;
}

You can also get a key-value list out of a query result:

$list = $articles->find('list');
 
foreach ($list as $id => $title) {
    echo "$id : $title"
}

For more information on how to customize the fields used for populating the listrefer to Finding Key/Value Pairs section.

Queries Are Collection Objects

Once you get familiar with the Query object methods, it is strongly encouragedthat you visit the Collection section toimprove your skills in efficiently traversing the data. In short, it isimportant to remember that anything you can call on a Collection object, youcan also do in a Query object:

// Use the combine() method from the collections library
// This is equivalent to find('list')
$keyValueList = $articles->find()->combine('id', 'title');
 
// An advanced example
$results = $articles->find()
    ->where(['id >' => 1])
    ->order(['title' => 'DESC'])
    ->map(function ($row) { // map() is a collection method, it executes the query
        $row->trimmedTitle = trim($row->title);
        return $row;
    })
    ->combine('id', 'trimmedTitle') // combine() is another collection method
    ->toArray(); // Also a collections library method
 
foreach ($results as $id => $trimmedTitle) {
    echo "$id : $trimmedTitle";
}

Queries Are Lazily Evaluated

Query objects are lazily evaluated. This means a query is not executed until oneof the following things occur:

The query is iterated with foreach().
The query’s execute() method is called. This will return the underlyingstatement object, and is to be used with insert/update/delete queries.
The query’s first() method is called. This will return the first result in the setbuilt by SELECT (it adds LIMIT 1 to the query).
The query’s all() method is called. This will return the result set andcan only be used with SELECT statements.
The query’s toList() or toArray() method is called.
Until one of these conditions are met, the query can be modified without additionalSQL being sent to the database. It also means that if a Query hasn’t beenevaluated, no SQL is ever sent to the database. Once executed, modifying andre-evaluating a query will result in additional SQL being run.

If you want to take a look at what SQL CakePHP is generating, you can turndatabase query logging on.

Selecting Data

CakePHP makes building SELECT queries simple. To limit the fields fetched,you can use the select() method:

$query = $articles->find();
$query->select(['id', 'title', 'body']);
foreach ($query as $row) {
    debug($row->title);
}

You can set aliases for fields by providing fields as an associative array:

// Results in SELECT id AS pk, title AS aliased_title, body ...
$query = $articles->find();
$query->select(['pk' => 'id', 'aliased_title' => 'title', 'body']);

To select distinct fields, you can use the distinct() method:

// Results in SELECT DISTINCT country FROM ...
$query = $articles->find();
$query->select(['country'])
    ->distinct(['country']);

To set some basic conditions you can use the where() method:

// Conditions are combined with AND
$query = $articles->find();
$query->where(['title' => 'First Post', 'published' => true]);
 
// You can call where() multiple times
$query = $articles->find();
$query->where(['title' => 'First Post'])
    ->where(['published' => true]);

You can also pass an anonymous function to the where() method. The passedanonymous function will receive an instance of\Cake\Database\Expression\QueryExpression as its first argument, and\Cake\ORM\Query as its second:

$query = $articles->find();
$query->where(function (QueryExpression $exp, Query $q) {
    return $exp->eq('published', true);
});

See the Advanced Conditions section to find out how to constructmore complex WHERE conditions. To apply ordering, you can use the ordermethod:

$query = $articles->find()
    ->order(['title' => 'ASC', 'id' => 'ASC']);

When calling order() multiple times on a query, multiple clauses will be appended.However, when using finders you may sometimes need to overwrite the ORDER BY.Set the second parameter of order() (as well as orderAsc() or orderDesc()) toQuery::OVERWRITE or to true:

$query = $articles->find()
    ->order(['title' => 'ASC']);
// Later, overwrite the ORDER BY clause instead of appending to it.
$query = $articles->find()
    ->order(['created' => 'DESC'], Query::OVERWRITE);

New in version 3.0.12: In addition to order, the orderAsc and orderDesc methods can beused when you need to sort on complex expressions:

$query = $articles->find();
$concat = $query->func()->concat([
    'title' => 'identifier',
    'synopsis' => 'identifier'
]);
$query->orderAsc($concat);

To limit the number of rows or set the row offset you can use the limit()and page() methods:

// Fetch rows 50 to 100
$query = $articles->find()
    ->limit(50)
    ->page(2);

As you can see from the examples above, all the methods that modify the queryprovide a fluent interface, allowing you to build a query through chained methodcalls.

Selecting Specific Fields

By default a query will select all fields from a table, the exception is when youcall the select() function yourself and pass certain fields:

// Only select id and title from the articles table
$articles->find()->select(['id', 'title']);

If you wish to still select all fields from a table after having calledselect($fields), you can pass the table instance to select() for thispurpose:

// Only all fields from the articles table including
// a calculated slug field.
$query = $articlesTable->find();
$query
    ->select(['slug' => $query->func()->concat(['title' => 'identifier', '-', 'id' => 'identifier'])])
    ->select($articlesTable); // Select all fields from articles

New in version 3.1: Passing a table object to select() was added in 3.1.

If you want to select all but a few fields on a table, you can useselectAllExcept():

$query = $articlesTable->find();
 
// Get all fields except the published field.
$query->selectAllExcept($articlesTable, ['published']);

You can also pass an Association object when working with containedassociations.

New in version 3.6.0: The selectAllExcept() method was added.

Using SQL Functions

CakePHP’s ORM offers abstraction for some commonly used SQL functions. Using theabstraction allows the ORM to select the platform specific implementation of thefunction you want. For example, concat is implemented differently in MySQL,PostgreSQL and SQL Server. Using the abstraction allows your code to beportable:

// Results in SELECT COUNT(*) count FROM ...
$query = $articles->find();
$query->select(['count' => $query->func()->count('*')]);

A number of commonly used functions can be created with the func() method:

rand() Generate a random value between 0 and 1 via SQL.
sum() Calculate a sum. The arguments will be treated as literal values.
avg() Calculate an average. The arguments will be treated as literalvalues.
min() Calculate the min of a column. The arguments will be treated asliteral values.
max() Calculate the max of a column. The arguments will be treated asliteral values.
count() Calculate the count. The arguments will be treated as literalvalues.
concat() Concatenate two values together. The arguments are treated asbound parameters unless marked as literal.
coalesce() Coalesce values. The arguments are treated as bound parametersunless marked as literal.
dateDiff() Get the difference between two dates/times. The arguments aretreated as bound parameters unless marked as literal.
now() Take either ‘time’ or ‘date’ as an argument allowing you to geteither the current time, or current date.
extract() Returns the specified date part from the SQL expression.
dateAdd() Add the time unit to the date expression.
dayOfWeek() Returns a FunctionExpression representing a call to SQLWEEKDAY function.

New in version 3.1: extract(), dateAdd() and dayOfWeek() methods have been added.

New in version 3.7: rand() was added.

When providing arguments for SQL functions, there are two kinds of parametersyou can use, literal arguments and bound parameters. Identifier/Literal parameters allowyou to reference columns or other SQL literals. Bound parameters can be used tosafely add user data to SQL functions. For example:

$query = $articles->find()->innerJoinWith('Categories');
$concat = $query->func()->concat([
    'Articles.title' => 'identifier',
    ' - CAT: ',
    'Categories.name' => 'identifier',
    ' - Age: ',
    '(DATEDIFF(NOW(), Articles.created))' => 'literal',
]);
$query->select(['link_title' => $concat]);

By making arguments with a value of literal, the ORM will know thatthe key should be treated as a literal SQL value. By making arguments witha value of identifier, the ORM will know that the key should be treatedas a field identifier. The above would generate the following SQL on MySQL:

SELECT CONCAT(Articles.title, :c0, Categories.name, :c1, (DATEDIFF(NOW(), Articles.created))) FROM articles;

The :c0 value will have the ' - CAT:' text bound when the query isexecuted.

In addition to the above functions, the func() method can be used to createany generic SQL function such as year, date_format, convert, etc.For example:

$query = $articles->find();
$year = $query->func()->year([
    'created' => 'identifier'
]);
$time = $query->func()->date_format([
    'created' => 'identifier',
    "'%H:%i'" => 'literal'
]);
$query->select([
    'yearCreated' => $year,
    'timeCreated' => $time
]);

Would result in:

SELECT YEAR(created) as yearCreated, DATE_FORMAT(created, '%H:%i') as timeCreated FROM articles;

You should remember to use the function builder whenever you need to putuntrusted data into SQL functions or stored procedures:

// Use a stored procedure
$query = $articles->find();
$lev = $query->func()->levenshtein([$search, 'LOWER(title)' => 'literal']);
$query->where(function (QueryExpression $exp) use ($lev) {
    return $exp->between($lev, 0, $tolerance);
});
 
// Generated SQL would be
WHERE levenshtein(:c0, lower(street)) BETWEEN :c1 AND :c2

Aggregates - Group and Having

When using aggregate functions like count and sum you may want to usegroup by and having clauses:

$query = $articles->find();
$query->select([
    'count' => $query->func()->count('view_count'),
    'published_date' => 'DATE(created)'
])
->group('published_date')
->having(['count >' => 3]);

Case statements

The ORM also offers the SQL case expression. The case expression allowsfor implementing if … then … else logic inside your SQL. This can be usefulfor reporting on data where you need to conditionally sum or count data, or where youneed to specific data based on a condition.

If we wished to know how many published articles are in our database, we could use the following SQL:

SELECT
COUNT(CASE WHEN published = 'Y' THEN 1 END) AS number_published,
COUNT(CASE WHEN published = 'N' THEN 1 END) AS number_unpublished
FROM articles

To do this with the query builder, we’d use the following code:

$query = $articles->find();
$publishedCase = $query->newExpr()
    ->addCase(
        $query->newExpr()->add(['published' => 'Y']),
        1,
        'integer'
    );
$unpublishedCase = $query->newExpr()
    ->addCase(
        $query->newExpr()->add(['published' => 'N']),
        1,
        'integer'
    );
 
$query->select([
    'number_published' => $query->func()->count($publishedCase),
    'number_unpublished' => $query->func()->count($unpublishedCase)
]);

The addCase function can also chain together multiple statements to createif .. then .. [elseif .. then .. ] [ .. else ] logic inside your SQL.

If we wanted to classify cities into SMALL, MEDIUM, or LARGE based on populationsize, we could do the following:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->addCase(
            [
                $q->newExpr()->lt('population', 100000),
                $q->newExpr()->between('population', 100000, 999000),
                $q->newExpr()->gte('population', 999001),
            ],
            ['SMALL',  'MEDIUM', 'LARGE'], # values matching conditions
            ['string', 'string', 'string'] # type of each value
        );
    });
# WHERE CASE
#   WHEN population < 100000 THEN 'SMALL'
#   WHEN population BETWEEN 100000 AND 999000 THEN 'MEDIUM'
#   WHEN population >= 999001 THEN 'LARGE'
#   END

Any time there are fewer case conditions than values, addCase willautomatically produce an if .. then .. else statement:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->addCase(
            [
                $q->newExpr()->eq('population', 0),
            ],
            ['DESERTED', 'INHABITED'], # values matching conditions
            ['string', 'string'] # type of each value
        );
    });
# WHERE CASE
#   WHEN population = 0 THEN 'DESERTED' ELSE 'INHABITED' END

Getting Arrays Instead of Entities

While ORMs and object result sets are powerful, creating entities is sometimesunnecessary. For example, when accessing aggregated data, building an Entity maynot make sense. The process of converting the database results to entities iscalled hydration. If you wish to disable this process you can do this:

$query = $articles->find();
$query->enableHydration(false); // Results as arrays instead of entities
$result = $query->toList(); // Execute the query and return the array

After executing those lines, your result should look similar to this:

[
    ['id' => 1, 'title' => 'First Article', 'body' => 'Article 1 body' ...],
    ['id' => 2, 'title' => 'Second Article', 'body' => 'Article 2 body' ...],
    ...
]

Adding Calculated Fields

After your queries, you may need to do some post-processing. If you need to adda few calculated fields or derived data, you can use the formatResults()method. This is a lightweight way to map over the result sets. If you need morecontrol over the process, or want to reduce results you should usethe Map/Reduce feature instead. If you were querying a listof people, you could calculate their age with a result formatter:

// Assuming we have built the fields, conditions and containments.
$query->formatResults(function (\Cake\Collection\CollectionInterface $results) {
    return $results->map(function ($row) {
        $row['age'] = $row['birth_date']->diff(new \DateTime)->y;
        return $row;
    });
});

As you can see in the example above, formatting callbacks will get aResultSetDecorator as their first argument. The second argument will bethe Query instance the formatter was attached to. The $results argument canbe traversed and modified as necessary.

Result formatters are required to return an iterator object, which will be usedas the return value for the query. Formatter functions are applied after all theMap/Reduce routines have been executed. Result formatters can be applied fromwithin contained associations as well. CakePHP will ensure that your formattersare properly scoped. For example, doing the following would work as you mayexpect:

// In a method in the Articles table
$query->contain(['Authors' => function ($q) {
    return $q->formatResults(function (\Cake\Collection\CollectionInterface $authors) {
        return $authors->map(function ($author) {
            $author['age'] = $author['birth_date']->diff(new \DateTime)->y;
            return $author;
        });
    });
}]);
 
// Get results
$results = $query->all();
 
// Outputs 29
echo $results->first()->author->age;

As seen above, the formatters attached to associated query builders are scopedto operate only on the data in the association. CakePHP will ensure thatcomputed values are inserted into the correct entity.

Advanced Conditions

The query builder makes it simple to build complex where clauses.Grouped conditions can be expressed by providing combining where() andexpression objects. For simple queries, you can build conditions usingan array of conditions:

$query = $articles->find()
    ->where([
        'author_id' => 3,
        'OR' => [['view_count' => 2], ['view_count' => 3]],
    ]);

The above would generate SQL like:

SELECT * FROM articles WHERE author_id = 3 AND (view_count = 2 OR view_count = 3)

If you’d prefer to avoid deeply nested arrays, you can use the callback form ofwhere() to build your queries. The callback form allows you to use theexpression builder to build more complex conditions without arrays. For example:

$query = $articles->find()->where(function ($exp, $query) {
    // Use add() to add multiple conditions for the same field.
    $author = $exp->or_(['author_id' => 3])->add(['author_id' => 2]);
    $published = $exp->and_(['published' => true, 'view_count' => 10]);
 
    return $exp->or_([
        'promoted' => true,
        $exp->and_([$author, $published])
    ]);
});

The above generates SQL similar to:

SELECT *
FROM articles
WHERE (
    (
        (author_id = 2 OR author_id = 3)
        AND
        (published = 1 AND view_count > 10)
    )
    OR promoted = 1
)

The expression object that is passed into where() functions has two kinds ofmethods. The first type of methods are combinators. The and() andor() methods create new expression objects that change how conditionsare combined. The second type of methods are conditions. Conditions areadded into an expression where they are combined with the current combinator.

For example, calling $exp->and(…) will create a new Expression objectthat combines all conditions it contains with AND. While $exp->or()will create a new Expression object that combines all conditions added to itwith OR. An example of adding conditions with an Expression object wouldbe:

$query = $articles->find()
    ->where(function (QueryExpression $exp) {
        return $exp
            ->eq('author_id', 2)
            ->eq('published', true)
            ->notEq('spam', true)
            ->gt('view_count', 10);
    });

Since we started off using where(), we don’t need to call and_(), asthat happens implicitly. The above shows a few new conditionmethods being combined with AND. The resulting SQL would look like:

SELECT *
FROM articles
WHERE (
author_id = 2
AND published = 1
AND spam != 1
AND view_count > 10)

Deprecated since version 3.5.0: As of 3.5.0 the orWhere() method is deprecated. This method createshard to predict SQL based on the current query state.Use where() instead as it has more predictable and easierto understand behavior.

However, if we wanted to use both AND & OR conditions we could do thefollowing:

$query = $articles->find()
    ->where(function (QueryExpression $exp) {
        $orConditions = $exp->or_(['author_id' => 2])
            ->eq('author_id', 5);
        return $exp
            ->add($orConditions)
            ->eq('published', true)
            ->gte('view_count', 10);
    });

Which would generate the SQL similar to:

SELECT *
FROM articles
WHERE (
(author_id = 2 OR author_id = 5)
AND published = 1
AND view_count >= 10)

The or() and and() methods also allow you to use functions as theirparameters. This is often easier to read than method chaining:

$query = $articles->find()
    ->where(function (QueryExpression $exp) {
        $orConditions = $exp->or_(function ($or) {
            return $or->eq('author_id', 2)
                ->eq('author_id', 5);
        });
        return $exp
            ->not($orConditions)
            ->lte('view_count', 10);
    });

You can negate sub-expressions using not():

$query = $articles->find()
    ->where(function (QueryExpression $exp) {
        $orConditions = $exp->or_(['author_id' => 2])
            ->eq('author_id', 5);
        return $exp
            ->not($orConditions)
            ->lte('view_count', 10);
    });

Which will generate the following SQL looking like:

SELECT *
FROM articles
WHERE (
NOT (author_id = 2 OR author_id = 5)
AND view_count <= 10)

It is also possible to build expressions using SQL functions:

$query = $articles->find()
    ->where(function (QueryExpression $exp, Query $q) {
        $year = $q->func()->year([
            'created' => 'identifier'
        ]);
        return $exp
            ->gte($year, 2014)
            ->eq('published', true);
    });

Which will generate the following SQL looking like:

SELECT *
FROM articles
WHERE (
YEAR(created) >= 2014
AND published = 1
)

When using the expression objects you can use the following methods to createconditions:

eq() Creates an equality condition:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->eq('population', '10000');
    });
# WHERE population = 10000

notEq() Creates an inequality condition:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->notEq('population', '10000');
    });
# WHERE population != 10000

like() Creates a condition using the LIKE operator:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->like('name', '%A%');
    });
# WHERE name LIKE "%A%"

notLike() Creates a negated LIKE condition:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->notLike('name', '%A%');
    });
# WHERE name NOT LIKE "%A%"

in() Create a condition using IN:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->in('country_id', ['AFG', 'USA', 'EST']);
    });
# WHERE country_id IN ('AFG', 'USA', 'EST')

notIn() Create a negated condition using IN:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->notIn('country_id', ['AFG', 'USA', 'EST']);
    });
# WHERE country_id NOT IN ('AFG', 'USA', 'EST')

gt() Create a > condition:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->gt('population', '10000');
    });
# WHERE population > 10000

gte() Create a >= condition:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->gte('population', '10000');
    });
# WHERE population >= 10000

lt() Create a < condition:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->lt('population', '10000');
    });
# WHERE population < 10000

lte() Create a <= condition:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->lte('population', '10000');
    });
# WHERE population <= 10000

isNull() Create an IS NULL condition:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->isNull('population');
    });
# WHERE (population) IS NULL

isNotNull() Create a negated IS NULL condition:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->isNotNull('population');
    });
# WHERE (population) IS NOT NULL

between() Create a BETWEEN condition:

$query = $cities->find()
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->between('population', 999, 5000000);
    });
# WHERE population BETWEEN 999 AND 5000000,

exists() Create a condition using EXISTS:

$subquery = $cities->find()
    ->select(['id'])
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->equalFields('countries.id', 'cities.country_id');
    })
    ->andWhere(['population >' => 5000000]);
 
$query = $countries->find()
    ->where(function (QueryExpression $exp, Query $q) use ($subquery) {
        return $exp->exists($subquery);
    });
# WHERE EXISTS (SELECT id FROM cities WHERE countries.id = cities.country_id AND population > 5000000)

notExists() Create a negated condition using EXISTS:

$subquery = $cities->find()
    ->select(['id'])
    ->where(function (QueryExpression $exp, Query $q) {
        return $exp->equalFields('countries.id', 'cities.country_id');
    })
    ->andWhere(['population >' => 5000000]);
 
$query = $countries->find()
    ->where(function (QueryExpression $exp, Query $q) use ($subquery) {
        return $exp->notExists($subquery);
    });
# WHERE NOT EXISTS (SELECT id FROM cities WHERE countries.id = cities.country_id AND population > 5000000)

In situations when you can’t get, or don’t want to use the builder methods tocreate the conditions you want you can also use snippets of SQL in whereclauses:

// Compare two fields to each other
$query->where(['Categories.parent_id != Parents.id']);

Warning

The field names used in expressions, and SQL snippets should nevercontain untrusted content. See the Using SQL Functions section forhow to safely include unsafe data into function calls.

Using Identifiers in Expressions

When you need to reference a column or SQL identifier in your queries you canuse the identifier() method:

$query = $countries->find();
$query->select([
        'year' => $query->func()->year([$query->identifier('created')])
    ])
    ->where(function ($exp, $query) {
        return $exp->gt('population', 100000);
    });

Warning

To prevent SQL injections, Identifier expressions should never haveuntrusted data passed into them.

New in version 3.6.0: Query::identifier() was added in 3.6.0

Automatically Creating IN Clauses

When building queries using the ORM, you will generally not have to indicate thedata types of the columns you are interacting with, as CakePHP can infer thetypes based on the schema data. If in your queries you’d like CakePHP toautomatically convert equality to IN comparisons, you’ll need to indicatethe column data type:

$query = $articles->find()
    ->where(['id' => $ids], ['id' => 'integer[]']);
 
// Or include IN to automatically cast to an array.
$query = $articles->find()
    ->where(['id IN' => $ids]);

The above will automatically create id IN (…) instead of id = ?. Thiscan be useful when you do not know whether you will get a scalar or array ofparameters. The [] suffix on any data type name indicates to the querybuilder that you want the data handled as an array. If the data is not an array,it will first be cast to an array. After that, each value in the array willbe cast using the type system. This works withcomplex types as well. For example, you could take a list of DateTime objectsusing:

$query = $articles->find()
    ->where(['post_date' => $dates], ['post_date' => 'date[]']);

Automatic IS NULL Creation

When a condition value is expected to be null or any other value, you canuse the IS operator to automatically create the correct expression:

$query = $categories->find()
    ->where(['parent_id IS' => $parentId]);

The above will create parent_id` = :c1 or parent_id IS NULL depending onthe type of $parentId

Automatic IS NOT NULL Creation

When a condition value is expected not to be null or any other value, youcan use the IS NOT operator to automatically create the correct expression:

$query = $categories->find()
    ->where(['parent_id IS NOT' => $parentId]);

The above will create parent_id` != :c1 or parent_id IS NOT NULLdepending on the type of $parentId

Raw Expressions

When you cannot construct the SQL you need using the query builder, you can useexpression objects to add snippets of SQL to your queries:

$query = $articles->find();
$expr = $query->newExpr()->add('1 + 1');
$query->select(['two' => $expr]);

Expression objects can be used with any query builder methods likewhere(), limit(), group(), select() and many other methods.

Warning

Using expression objects leaves you vulnerable to SQL injection. You shouldnever use untrusted data into expressions.

Getting Results

Once you’ve made your query, you’ll want to retrieve rows from it. There area few ways of doing this:

// Iterate the query
foreach ($query as $row) {
    // Do stuff.
}
 
// Get the results
$results = $query->all();

You can use any of the collection methodson your query objects to pre-process or transform the results:

// Use one of the collection methods.
$ids = $query->map(function ($row) {
    return $row->id;
});
 
$maxAge = $query->max(function ($max) {
    return $max->age;
});

You can use first or firstOrFail to retrieve a single record. Thesemethods will alter the query adding a LIMIT 1 clause:

// Get just the first row
$row = $query->first();
 
// Get the first row or an exception.
$row = $query->firstOrFail();

Returning the Total Count of Records

Using a single query object, it is possible to obtain the total number of rowsfound for a set of conditions:

$total = $articles->find()->where(['is_active' => true])->count();

The count() method will ignore the limit, offset and pageclauses, thus the following will return the same result:

$total = $articles->find()->where(['is_active' => true])->limit(10)->count();

This is useful when you need to know the total result set size in advance,without having to construct another Query object. Likewise, all resultformatting and map-reduce routines are ignored when using the count()method.

Moreover, it is possible to return the total count for a query containing groupby clauses without having to rewrite the query in any way. For example, considerthis query for retrieving article ids and their comments count:

$query = $articles->find();
$query->select(['Articles.id', $query->func()->count('Comments.id')])
    ->matching('Comments')
    ->group(['Articles.id']);
$total = $query->count();

After counting, the query can still be used for fetching the associatedrecords:

$list = $query->all();

Sometimes, you may want to provide an alternate method for counting the totalrecords of a query. One common use case for this is providinga cached value or an estimate of the total rows, or to alter the query to removeexpensive unneeded parts such as left joins. This becomes particularly handywhen using the CakePHP built-in pagination system which calls the count()method:

$query = $query->where(['is_active' => true])->counter(function ($query) {
    return 100000;
});
$query->count(); // Returns 100000

In the example above, when the pagination component calls the count method, itwill receive the estimated hard-coded number of rows.

Caching Loaded Results

When fetching entities that don’t change often you may want to cache theresults. The Query class makes this simple:

$query->cache('recent_articles');

Will enable caching on the query’s result set. If only one argument is providedto cache() then the ‘default’ cache configuration will be used. You cancontrol which caching configuration is used with the second parameter:

// String config name.
$query->cache('recent_articles', 'dbResults');
 
// Instance of CacheEngine
$query->cache('recent_articles', $memcache);

In addition to supporting static keys, the cache() method accepts a functionto generate the key. The function you give it will receive the query as anargument. You can then read aspects of the query to dynamically generate thecache key:

// Generate a key based on a simple checksum
// of the query's where clause
$query->cache(function ($q) {
    return 'articles-' . md5(serialize($q->clause('where')));
});

The cache method makes it simple to add cached results to your custom finders orthrough event listeners.

When the results for a cached query are fetched the following happens:

The Model.beforeFind event is triggered.
If the query has results set, those will be returned.
The cache key will be resolved and cache data will be read. If the cache datais not empty, those results will be returned.
If the cache misses, the query will be executed and a new ResultSet will becreated. This ResultSet will be written to the cache and returned.

Note

You cannot cache a streaming query result.

Loading Associations

The builder can help you retrieve data from multiple tables at the same timewith the minimum amount of queries possible. To be able to fetch associateddata, you first need to setup associations between the tables as described inthe Associations - Linking Tables Together section. This technique of combining queriesto fetch associated data from other tables is called eager loading.

Eager loading helps avoid many of the potential performance problemssurrounding lazy-loading in an ORM. The queries generated by eager loading canbetter leverage joins, allowing more efficient queries to be made. In CakePHPyou define eager loaded associations using the ‘contain’ method:

// In a controller or table method.
 
// As an option to find()
$query = $articles->find('all', ['contain' => ['Authors', 'Comments']]);
 
// As a method on the query object
$query = $articles->find('all');
$query->contain(['Authors', 'Comments']);

The above will load the related author and comments for each article in theresult set. You can load nested associations using nested arrays to define theassociations to be loaded:

$query = $articles->find()->contain([
    'Authors' => ['Addresses'], 'Comments' => ['Authors']
]);

Alternatively, you can express nested associations using the dot notation:

$query = $articles->find()->contain([
    'Authors.Addresses',
    'Comments.Authors'
]);

You can eager load associations as deep as you like:

$query = $products->find()->contain([
    'Shops.Cities.Countries',
    'Shops.Managers'
]);

You can select fields from all associations with multiple easy contain()statements:

$query = $this->find()->select([
    'Realestates.id',
    'Realestates.title',
    'Realestates.description'
])
->contain([
    'RealestateAttributes' => [
        'Attributes' => [
            'fields' => [
                // Aliased fields in contain() must include
                // the model prefix to be mapped correctly.
                'Attributes__name' => 'attr_name'
            ]
        ]
    ]
])
->contain([
    'RealestateAttributes' => [
        'fields' => [
            'RealestateAttributes.realestate_id',
            'RealestateAttributes.value'
        ]
    ]
])
->where($condition);

If you need to reset the containments on a query you can set the second argumentto true:

$query = $articles->find();
$query->contain(['Authors', 'Comments'], true);

Passing Conditions to Contain

When using contain() you are able to restrict the data returned by theassociations and filter them by conditions. To specify conditions, pass an anonymousfunction that receives as the first argument a query object, \Cake\ORM\Query:

// In a controller or table method.
// Prior to 3.5.0 you would use contain(['Comments' => function () { ... }])
 
$query = $articles->find()->contain('Comments', function (Query $q) {
    return $q
        ->select(['body', 'author_id'])
        ->where(['Comments.approved' => true]);
});

This also works for pagination at the Controller level:

$this->paginate['contain'] = [
    'Comments' => function (Query $query) {
        return $query->select(['body', 'author_id'])
        ->where(['Comments.approved' => true]);
    }
];

Note

When you limit the fields that are fetched from an association, you mustensure that the foreign key columns are selected. Failing to select foreignkey fields will cause associated data to not be present in the final result.

It is also possible to restrict deeply-nested associations using the dotnotation:

$query = $articles->find()->contain([
    'Comments',
    'Authors.Profiles' => function (Query $q) {
        return $q->where(['Profiles.is_published' => true]);
    }
]);

In the above example, you’ll still get authors even if they don’t havea published profile. To only get authors with a published profile usematching(). If you have defined customfinders in your associations, you can use them inside contain():

// Bring all articles, but only bring the comments that are approved and
// popular.
$query = $articles->find()->contain('Comments', function (Query $q) {
    return $q->find('approved')->find('popular');
});

Note

For BelongsTo and HasOne associations only the where andselect clauses are used when loading the associated records. For therest of the association types you can use every clause that the query objectprovides.

If you need full control over the query that is generated, you can tell contain()to not append the foreignKey constraints to the generated query. In thatcase you should use an array passing foreignKey and queryBuilder:

$query = $articles->find()->contain([
    'Authors' => [
        'foreignKey' => false,
        'queryBuilder' => function (Query $q) {
            return $q->where(...); // Full conditions for filtering
        }
    ]
]);

If you have limited the fields you are loading with select() but also want toload fields off of contained associations, you can pass the association objectto select():

// Select id & title from articles, but all fields off of Users.
$query = $articles->find()
    ->select(['id', 'title'])
    ->select($articles->Users)
    ->contain(['Users']);

Alternatively, if you have multiple associations, you can use enableAutoFields():

// Select id & title from articles, but all fields off of Users, Comments
// and Tags.
$query->select(['id', 'title'])
    ->contain(['Comments', 'Tags'])
    ->enableAutoFields(true) // Prior to 3.4.0 use autoFields(true)
    ->contain(['Users' => function(Query $q) {
        return $q->autoFields(true);
    }]);

New in version 3.1: Selecting columns via an association object was added in 3.1

Sorting Contained Associations

When loading HasMany and BelongsToMany associations, you can use the sortoption to sort the data in those associations:

$query->contain([
    'Comments' => [
        'sort' => ['Comments.created' => 'DESC']
    ]
]);

Filtering by Associated Data

A fairly common query case with associations is finding records ‘matching’specific associated data. For example if you have ‘Articles belongsToMany Tags’you will probably want to find Articles that have the CakePHP tag. This isextremely simple to do with the ORM in CakePHP:

// In a controller or table method.
 
$query = $articles->find();
$query->matching('Tags', function ($q) {
    return $q->where(['Tags.name' => 'CakePHP']);
});

You can apply this strategy to HasMany associations as well. For example if‘Authors HasMany Articles’, you could find all the authors with recentlypublished articles using the following:

$query = $authors->find();
$query->matching('Articles', function ($q) {
    return $q->where(['Articles.created >=' => new DateTime('-10 days')]);
});

Filtering by deep associations is surprisingly easy, and the syntax should bealready familiar to you:

// In a controller or table method.
$query = $products->find()->matching(
    'Shops.Cities.Countries', function ($q) {
        return $q->where(['Countries.name' => 'Japan']);
    }
);
 
// Bring unique articles that were commented by 'markstory' using passed variable
// Dotted matching paths should be used over nested matching() calls
$username = 'markstory';
$query = $articles->find()->matching('Comments.Users', function ($q) use ($username) {
    return $q->where(['username' => $username]);
});

Note

As this function will create an INNER JOIN, you might want to considercalling distinct on the find query as you might get duplicate rows ifyour conditions don’t exclude them already. This might be the case, forexample, when the same users comments more than once on a single article.

The data from the association that is ‘matched’ will be available on the_matchingData property of entities. If both match and contain the sameassociation, you can expect to get both the _matchingData and standardassociation properties in your results.

Using innerJoinWith

Using the matching() function, as we saw already, will create an INNER JOIN with the specified association and will also load the fields into theresult set.

There may be cases where you want to use matching() but are not interestedin loading the fields into the result set. For this purpose, you can useinnerJoinWith():

$query = $articles->find();
$query->innerJoinWith('Tags', function ($q) {
    return $q->where(['Tags.name' => 'CakePHP']);
});

The innerJoinWith() method works the same as matching(), thatmeans that you can use dot notation to join deeply nestedassociations:

$query = $products->find()->innerJoinWith(
    'Shops.Cities.Countries', function ($q) {
        return $q->where(['Countries.name' => 'Japan']);
    }
);

Again, the only difference is that no additional columns will be added to theresult set, and no _matchingData property will be set.However, it is possible to combine innerJoinWith() and contain() when you need to filter by associate data and you want also to retrieve associate fields too (following the same filter):

$filter = ['Tags.name' => 'CakePHP'];
$query = $articles->find()
    ->distinct($articles)
    ->contain('Tags', function (\Cake\ORM\Query $q) use ($filter) {
        return $q->where($filter);
    })
    ->innerJoinWith('Tags', function (\Cake\ORM\Query $q) use ($filter) {
        return $q->where($filter);
    });

New in version 3.1: Query::innerJoinWith() was added in 3.1

Using notMatching

The opposite of matching() is notMatching(). This function will changethe query so that it filters results that have no relation to the specifiedassociation:

// In a controller or table method.
 
$query = $articlesTable
    ->find()
    ->notMatching('Tags', function ($q) {
        return $q->where(['Tags.name' => 'boring']);
    });

The above example will find all articles that were not tagged with the wordboring. You can apply this method to HasMany associations as well. You could,for example, find all the authors with no published articles in the last 10days:

$query = $authorsTable
    ->find()
    ->notMatching('Articles', function ($q) {
        return $q->where(['Articles.created >=' => new \DateTime('-10 days')]);
    });

It is also possible to use this method for filtering out records not matchingdeep associations. For example, you could find articles that have not beencommented on by a certain user:

$query = $articlesTable
    ->find()
    ->notMatching('Comments.Users', function ($q) {
        return $q->where(['username' => 'jose']);
    });

Since articles with no comments at all also satisfy the condition above, you maywant to combine matching() and notMatching() in the same query. Thefollowing example will find articles having at least one comment, but notcommented by a certain user:

$query = $articlesTable
    ->find()
    ->notMatching('Comments.Users', function ($q) {
        return $q->where(['username' => 'jose']);
    })
    ->matching('Comments');

Note

As notMatching() will create a LEFT JOIN, you might want to considercalling distinct on the find query as you can get duplicate rowsotherwise.

Keep in mind that contrary to the matching() function, notMatching()will not add any data to the _matchingData property in the results.

New in version 3.1: Query::notMatching() was added in 3.1

Using leftJoinWith

On certain occasions you may want to calculate a result based on an association,without having to load all the records for it. For example, if you wanted toload the total number of comments an article has along with all the articledata, you can use the leftJoinWith() function:

$query = $articlesTable->find();
$query->select(['total_comments' => $query->func()->count('Comments.id')])
    ->leftJoinWith('Comments')
    ->group(['Articles.id'])
    ->enableAutoFields(true); // Prior to 3.4.0 use autoFields(true);

The results for the above query will contain the article data and thetotal_comments property for each of them.

leftJoinWith() can also be used with deeply nested associations. This isuseful, for example, for bringing the count of articles tagged with a certainword, per author:

$query = $authorsTable
    ->find()
    ->select(['total_articles' => $query->func()->count('Articles.id')])
    ->leftJoinWith('Articles.Tags', function ($q) {
        return $q->where(['Tags.name' => 'awesome']);
    })
    ->group(['Authors.id'])
    ->enableAutoFields(true); // Prior to 3.4.0 use autoFields(true);

This function will not load any columns from the specified associations into theresult set.

New in version 3.1: Query::leftJoinWith() was added in 3.1

Adding Joins

In addition to loading related data with contain(), you can also addadditional joins with the query builder:

$query = $articles->find()
    ->join([
        'table' => 'comments',
        'alias' => 'c',
        'type' => 'LEFT',
        'conditions' => 'c.article_id = articles.id',
    ]);

You can append multiple joins at the same time by passing an associative arraywith multiple joins:

$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => 'c.article_id = articles.id',
        ],
        'u' => [
            'table' => 'users',
            'type' => 'INNER',
            'conditions' => 'u.id = articles.user_id',
        ]
    ]);

As seen above, when adding joins the alias can be the outer array key. Joinconditions can also be expressed as an array of conditions:

$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => [
                'c.created >' => new DateTime('-5 days'),
                'c.moderated' => true,
                'c.article_id = articles.id'
            ]
        ],
    ], ['c.created' => 'datetime', 'c.moderated' => 'boolean']);

When creating joins by hand and using array based conditions, you need toprovide the datatypes for each column in the join conditions. By providingdatatypes for the join conditions, the ORM can correctly convert data types intoSQL. In addition to join() you can use rightJoin(), leftJoin() andinnerJoin() to create joins:

// Join with an alias and string conditions
$query = $articles->find();
$query->leftJoin(
    ['Authors' => 'authors'],
    ['Authors.id = Articles.author_id']);
 
// Join with an alias, array conditions, and types
$query = $articles->find();
$query->innerJoin(
    ['Authors' => 'authors'],
    [
    'Authors.promoted' => true,
    'Authors.created' => new DateTime('-5 days'),
    'Authors.id = Articles.author_id'
    ],
    ['Authors.promoted' => 'boolean', 'Authors.created' => 'datetime']);

It should be noted that if you set the quoteIdentifiers option to true whendefining your Connection, join conditions between table fields should be set as follow:

$query = $articles->find()
    ->join([
        'c' => [
            'table' => 'comments',
            'type' => 'LEFT',
            'conditions' => [
                'c.article_id' => new \Cake\Database\Expression\IdentifierExpression('articles.id')
            ]
        ],
    ]);

This ensures that all of your identifiers will be quoted across the Query, avoiding errors withsome database Drivers (PostgreSQL notably)

Inserting Data

Unlike earlier examples, you should not use find() to create insert queries.Instead, create a new Query object using query():

$query = $articles->query();
$query->insert(['title', 'body'])
    ->values([
        'title' => 'First post',
        'body' => 'Some body text'
    ])
    ->execute();

To insert multiple rows with only one query, you can chain the values()method as many times as you need:

$query = $articles->query();
$query->insert(['title', 'body'])
    ->values([
        'title' => 'First post',
        'body' => 'Some body text'
    ])
    ->values([
        'title' => 'Second post',
        'body' => 'Another body text'
    ])
    ->execute();

Generally, it is easier to insert data using entities andORM\Table::save(). By composing a SELECT andINSERT query together, you can create INSERT INTO … SELECT stylequeries:

$select = $articles->find()
    ->select(['title', 'body', 'published'])
    ->where(['id' => 3]);
 
$query = $articles->query()
    ->insert(['title', 'body', 'published'])
    ->values($select)
    ->execute();

Note

Inserting records with the query builder will not trigger events such asModel.afterSave. Instead you should use the ORM to savedata.

Updating Data

As with insert queries, you should not use find() to create update queries.Instead, create new a Query object using query():

$query = $articles->query();
$query->update()
    ->set(['published' => true])
    ->where(['id' => $id])
    ->execute();

Generally, it is easier to update data using entities andORM\Table::patchEntity().

Note

Updating records with the query builder will not trigger events such asModel.afterSave. Instead you should use the ORM to savedata.

Deleting Data

As with insert queries, you should not use find() to create delete queries.Instead, create new a query object using query():

$query = $articles->query();
$query->delete()
    ->where(['id' => $id])
    ->execute();

Generally, it is easier to delete data using entities andORM\Table::delete().

SQL Injection Prevention

While the ORM and database abstraction layers prevent most SQL injectionsissues, it is still possible to leave yourself vulnerable through improper use.

When using condition arrays, the key/left-hand side as well as single valueentries must not contain user data:

$query->where([
    // Data on the key/left-hand side is unsafe, as it will be
    // inserted into the generated query as-is
    $userData => $value,
 
    // The same applies to single value entries, they are not
    // safe to use with user data in any form
    $userData,
    "MATCH (comment) AGAINST ($userData)",
    'created < NOW() - ' . $userData
]);

When using the expression builder, column names must not contain user data:

$query->where(function (QueryExpression $exp) use ($userData, $values) {
    // Column names in all expressions are not safe.
    return $exp->in($userData, $values);
});

When building function expressions, function names should never contain userdata:

// Not safe.
$query->func()->{$userData}($arg1);
 
// Also not safe to use an array of
// user data in a function expression
$query->func()->coalesce($userData);

Raw expressions are never safe:

$expr = $query->newExpr()->add($userData);
$query->select(['two' => $expr]);

Binding values

It is possible to protect against many unsafe situations by using bindings.Similar to binding values to prepared statements,values can be bound to queries using the Cake\Database\Query::bind()method.

The following example would be a safe variant of the unsafe, SQL injection proneexample given above:

$query
    ->where([
        'MATCH (comment) AGAINST (:userData)',
        'created < NOW() - :moreUserData'
    ])
    ->bind(':userData', $userData, 'string')
    ->bind(':moreUserData', $moreUserData, 'datetime');

Note

Unlike Cake\Database\StatementInterface::bindValue(),Query::bind() requires to pass the named placeholders including thecolon!

More Complex Queries

The query builder is capable of building complex queries like UNION queriesand sub-queries.

Unions

Unions are created by composing one or more select queries together:

$inReview = $articles->find()
    ->where(['need_review' => true]);
 
$unpublished = $articles->find()
    ->where(['published' => false]);
 
$unpublished->union($inReview);

You can create UNION ALL queries using the unionAll() method:

$inReview = $articles->find()
    ->where(['need_review' => true]);
 
$unpublished = $articles->find()
    ->where(['published' => false]);
 
$unpublished->unionAll($inReview);

Subqueries

Subqueries are a powerful feature in relational databases and building them inCakePHP is fairly intuitive. By composing queries together, you can makesubqueries:

// Prior to 3.6.0 use association() instead.
$matchingComment = $articles->getAssociation('Comments')->find()
    ->select(['article_id'])
    ->distinct()
    ->where(['comment LIKE' => '%CakePHP%']);
 
$query = $articles->find()
    ->where(['id IN' => $matchingComment]);

Subqueries are accepted anywhere a query expression can be used. For example, inthe select() and join() methods.

Adding Locking Statements

Most relational database vendors support taking out locks when doing selectoperations. You can use the epilog() method for this:

// In MySQL
$query->epilog('FOR UPDATE');

The epilog() method allows you to append raw SQL to the end of queries. Youshould never put raw user data into epilog().

Executing Complex Queries

While the query builder makes it easy to build most queries, very complexqueries can be tedious and complicated to build. You may want to executethe desired SQL directly.

Executing SQL directly allows you to fine tune the query that will be run.However, doing so doesn’t let you use contain or other higher level ORMfeatures.