Welcome to the Cookbook

Most of the model's find calls involve passing sets of conditions in one way or another. The simplest approach to this is to use a WHERE clause snippet of SQL. If you find yourself needing more control, you can use arrays.

Using arrays is clearer and easier to read, and also makes it very easy to build queries. This syntax also breaks out the elements of your query (fields, values, operators, etc.) into discrete, manipulatable parts. This allows CakePHP to generate the most efficient query possible, ensure proper SQL syntax, and properly escape each individual part of the query.

At it's most basic, an array-based query looks like this:

$conditions = array("Post.title" => "This is a post");
//Example usage with a model:
$this->Post->find('first', array('conditions' => $conditions));

1. $conditions = array("Post.title" => "This is a post");
2. //Example usage with a model:
3. $this->Post->find('first', array('conditions' => $conditions));

The structure here is fairly self-explanatory: it will find any post where the title equals "This is a post". Note that we could have used just "title" as the field name, but when building queries, it is good practice to always specify the model name, as it improves the clarity of the code, and helps prevent collisions in the future, should you choose to change your schema.

What about other types of matches? These are equally simple. Let's say we wanted to find all the posts where the title is not "This is a post":

array("Post.title <>" => "This is a post")

1. array("Post.title <>" => "This is a post")

Notice the '<>' that follows the field name. CakePHP can parse out any valid SQL comparison operator, including match expressions using LIKE, BETWEEN, or REGEX, as long as you leave a space between field name and the operator. The one exception here is IN (...)-style matches. Let's say you wanted to find posts where the title was in a given set of values:

array(
	"Post.title" => array("First post", "Second post", "Third post")
)

1. array(
2. "Post.title" => array("First post", "Second post", "Third post")
3. )

To do a NOT IN(...) match to find posts where the title is not in the given set of values:

array(
	"NOT" => array("Post.title" => array("First post", "Second post", "Third post"))
)

1. array(
2. "NOT" => array("Post.title" => array("First post", "Second post", "Third post"))
3. )

Adding additional filters to the conditions is as simple as adding additional key/value pairs to the array:

array (
	"Post.title" => array("First post", "Second post", "Third post"),
	"Post.created >" => date('Y-m-d', strtotime("-2 weeks"))
)

1. array (
2. "Post.title" => array("First post", "Second post", "Third post"),
3. "Post.created >" => date('Y-m-d', strtotime("-2 weeks"))
4. )

You can also create finds that compare two fields in the database

array("Post.created = Post.modified")

1. array("Post.created = Post.modified")

This above example will return posts where the created date is equal to the modified date (ie it will return posts that have never been modified).

Remember that if you find yourself unable to form a WHERE clause in this method (ex. boolean operations), you can always specify it as a string like:

array(
    'Model.field & 8 = 1',
    //other conditions as usual
)

1. array(
2. 'Model.field & 8 = 1',
3. //other conditions as usual
4. )

By default, CakePHP joins multiple conditions with boolean AND; which means, the snippet above would only match posts that have been created in the past two weeks, and have a title that matches one in the given set. However, we could just as easily find posts that match either condition:

array( "OR" => array (
	"Post.title" => array("First post", "Second post", "Third post"),
	"Post.created >" => date('Y-m-d', strtotime("-2 weeks"))
    )
)

1. array( "OR" => array (
2. "Post.title" => array("First post", "Second post", "Third post"),
3. "Post.created >" => date('Y-m-d', strtotime("-2 weeks"))
4. )
5. )

Cake accepts all valid SQL boolean operations, including AND, OR, NOT, XOR, etc., and they can be upper or lower case, whichever you prefer. These conditions are also infinitely nest-able. Let's say you had a belongsTo relationship between Posts and Authors. Let's say you wanted to find all the posts that contained a certain keyword (“magic”) or were created in the past two weeks, but you want to restrict your search to posts written by Bob:

array (
	"Author.name" => "Bob", 
	"OR" => array (
		"Post.title LIKE" => "%magic%",
		"Post.created >" => date('Y-m-d', strtotime("-2 weeks"))
	)
)

1. array (
2. "Author.name" => "Bob",
3. "OR" => array (
4. "Post.title LIKE" => "%magic%",
5. "Post.created >" => date('Y-m-d', strtotime("-2 weeks"))
6. )
7. )

Cake can also check for null fields. In this example, the query will return records where the post title is not null:

array ("NOT" => array (
        "Post.title" => null
    )
)

1. array ("NOT" => array (
2. "Post.title" => null
3. )
4. )

To handle BETWEEN queries, you can use the following:

array('Post.id BETWEEN ? AND ?' => array(1,10))

1. array('Post.id BETWEEN ? AND ?' => array(1,10))

Note: CakePHP will quote the numeric values depending on the field type in your DB.

How about GROUP BY?

array('fields'=>array('Product.type','MIN(Product.price) as price'), 'group' => 'Product.type');

1. array('fields'=>array('Product.type','MIN(Product.price) as price'), 'group' => 'Product.type');

The data returned for this would be in the following format:

Array
(
    [0] => Array
        (
            [Product] => Array
                (
                    [type] => Clothing
                )
            [0] => Array
                (
                    [price] => 32
                )
        )
    [1] => Array....

1. Array
2. (
3. [0] => Array
4. (
5. [Product] => Array
6. (
7. [type] => Clothing
8. )
9. [0] => Array
10. (
11. [price] => 32
12. )
13. )
14. [1] => Array....

A quick example of doing a DISTINCT query. You can use other operators, such as MIN(), MAX(), etc., in a similar fashion

array('fields'=>array('DISTINCT (User.name) AS my_column_name'), 'order'=>array('User.id DESC'));

1. array('fields'=>array('DISTINCT (User.name) AS my_column_name'), 'order'=>array('User.id DESC'));

You can create very complex conditions, by nesting multiple condition arrays:

array(
   'OR' => array(
      array('Company.name' => 'Future Holdings'),
      array('Company.name' => 'Steel Mega Works')
   ),
   'AND' => array(
      array(
         'OR'=>array(
            array('Company.status' => 'active'),
            'NOT'=>array(
               array('Company.status'=> array('inactive', 'suspended'))
            )
         )
     )
   )
);

1. array(
2. 'OR' => array(
3. array('Company.name' => 'Future Holdings'),
4. array('Company.name' => 'Steel Mega Works')
5. ),
6. 'AND' => array(
7. array(
8. 'OR'=>array(
9. array('Company.status' => 'active'),
10. 'NOT'=>array(
11. array('Company.status'=> array('inactive', 'suspended'))
12. )
13. )
14. )
15. )
16. );

Which produces the following SQL:

SELECT `Company`.`id`, `Company`.`name`, 
`Company`.`description`, `Company`.`location`, 
`Company`.`created`, `Company`.`status`, `Company`.`size`

FROM
   `companies` AS `Company`
WHERE
   ((`Company`.`name` = 'Future Holdings')
   OR
   (`Company`.`name` = 'Steel Mega Works'))
AND
   ((`Company`.`status` = 'active')
   OR (NOT (`Company`.`status` IN ('inactive', 'suspended'))))

1. SELECT `Company`.`id`, `Company`.`name`,
2. `Company`.`description`, `Company`.`location`,
3. `Company`.`created`, `Company`.`status`, `Company`.`size`
4. FROM
5. `companies` AS `Company`
6. WHERE
7. ((`Company`.`name` = 'Future Holdings')
8. OR
9. (`Company`.`name` = 'Steel Mega Works'))
10. AND
11. ((`Company`.`status` = 'active')
12. OR (NOT (`Company`.`status` IN ('inactive', 'suspended'))))

Sub-queries

For the example, imagine we have a "users" table with "id", "name" and "status". The status can be "A", "B" or "C". And we want to get all the users that have status different than "B" using sub-query.

In order to achieve that we are going to get the model data source and ask it to build the query as if we were calling a find method, but it will just return the SQL statement. After that we make an expression and add it to the conditions array.

$conditionsSubQuery['"User2"."status"'] = 'B';

$dbo = $this->User->getDataSource();
$subQuery = $dbo->buildStatement(
    array(
        'fields' => array('"User2"."id"'),
        'table' => $dbo->fullTableName($this->User),
        'alias' => 'User2',
        'limit' => null,
        'offset' => null,
        'joins' => array(),
        'conditions' => $conditionsSubQuery,
        'order' => null,
        'group' => null
    ),
    $this->User
);
$subQuery = ' "User"."id" NOT IN (' . $subQuery . ') ';
$subQueryExpression = $dbo->expression($subQuery);

$conditions[] = $subQueryExpression;

$this->User->find('all', compact('conditions'));

1. $conditionsSubQuery['"User2"."status"'] = 'B';
2. $dbo = $this->User->getDataSource();
3. $subQuery = $dbo->buildStatement(
4. array(
5. 'fields' => array('"User2"."id"'),
6. 'table' => $dbo->fullTableName($this->User),
7. 'alias' => 'User2',
8. 'limit' => null,
9. 'offset' => null,
10. 'joins' => array(),
11. 'conditions' => $conditionsSubQuery,
12. 'order' => null,
13. 'group' => null
14. ),
15. $this->User
16. );
17. $subQuery = ' "User"."id" NOT IN (' . $subQuery . ') ';
18. $subQueryExpression = $dbo->expression($subQuery);
19. $conditions[] = $subQueryExpression;
20. $this->User->find('all', compact('conditions'));

This should generate the following SQL:

SELECT 
    "User"."id" AS "User__id", 
    "User"."name" AS "User__name", 
    "User"."status" AS "User__status" 
FROM 
    "users" AS "User" 
WHERE 
    "User"."id" NOT IN (
        SELECT 
            "User2"."id" 
        FROM 
            "users" AS "User2" 
        WHERE 
            "User2"."status" = 'B' 
    )

1. SELECT
2. "User"."id" AS "User__id",
3. "User"."name" AS "User__name",
4. "User"."status" AS "User__status"
5. FROM
6. "users" AS "User"
7. WHERE
8. "User"."id" NOT IN (
9. SELECT
10. "User2"."id"
11. FROM
12. "users" AS "User2"
13. WHERE
14. "User2"."status" = 'B'
15. )