Welcome to The Cookbook

find($type, $params)

Find is the multifunctional workhorse of all model data-retrieval functions. $type can be either 'all', 'first', 'count', 'list', 'neighbors' or 'threaded'. The default find type is 'first'.

$params is used to pass all parameters to the various finds, and has the following possible keys by default - all of which are optional:

array(
	'conditions' => array('Model.field' => $thisValue), //array of conditions
	'recursive' => 1, //int
	'fields' => array('Model.field1', 'DISTINCT Model.field2'), //array of field names
	'order' => array('Model.created', 'Model.field3 DESC'), //string or array defining order
	'group' => array('Model.field'), //fields to GROUP BY
	'limit' => n, //int
	'page' => n, //int
	'callbacks' => true //other possible values are false, 'before', 'after'
)

1. array(
2. 'conditions' => array('Model.field' => $thisValue), //array of conditions
3. 'recursive' => 1, //int
4. 'fields' => array('Model.field1', 'DISTINCT Model.field2'), //array of field names
5. 'order' => array('Model.created', 'Model.field3 DESC'), //string or array defining order
6. 'group' => array('Model.field'), //fields to GROUP BY
7. 'limit' => n, //int
8. 'page' => n, //int
9. 'callbacks' => true //other possible values are false, 'before', 'after'
10. )

It's also possible to add and use other parameters, as is made use of by some find types, behaviors and of course possible with your own model methods

More information about model callbacks is available here

find('first', $params)

'first' is the default find type, and will return one result, you'd use this for any use where you expect only one result. Below are a couple of simple (controller code) examples:

function some_function() {
   ...
   $this->Article->order = null; // resetting if it's set
   $semiRandomArticle = $this->Article->find();
   $this->Article->order = 'Article.created DESC'; // simulating the model having a default order
   $lastCreated = $this->Article->find();
   $alsoLastCreated = $this->Article->find('first', array('order' => array('Article.created DESC')));
   $specificallyThisOne = $this->Article->find('first', array('conditions' => array('Article.id' => 1)));
   ...
}

1. function some_function() {
2. ...
3. $this->Article->order = null; // resetting if it's set
4. $semiRandomArticle = $this->Article->find();
5. $this->Article->order = 'Article.created DESC'; // simulating the model having a default order
6. $lastCreated = $this->Article->find();
7. $alsoLastCreated = $this->Article->find('first', array('order' => array('Article.created DESC')));
8. $specificallyThisOne = $this->Article->find('first', array('conditions' => array('Article.id' => 1)));
9. ...
10. }

In the first example, no parameters at all are passed to find - therefore no conditions or sort order will be used. The format returned from find('first') call is of the form:

Array
(
    [ModelName] => Array
        (
            [id] => 83
            [field1] => value1
            [field2] => value2
            [field3] => value3
        )

    [AssociatedModelName] => Array
        (
            [id] => 1
            [field1] => value1
            [field2] => value2
            [field3] => value3
        )
)

There are no additional parameters used by find('first').

find('count', $params)

find('count', $params) returns an integer value. Below are a couple of simple (controller code) examples:

function some_function() {
   ...
   $total = $this->Article->find('count');
   $pending = $this->Article->find('count', array('conditions' => array('Article.status' => 'pending')));
   $authors = $this->Article->User->find('count');
   $publishedAuthors = $this->Article->find('count', array(
      'fields' => 'DISTINCT Article.user_id',
      'conditions' => array('Article.status !=' => 'pending')
   ));
   ...
}

1. function some_function() {
2. ...
3. $total = $this->Article->find('count');
4. $pending = $this->Article->find('count', array('conditions' => array('Article.status' => 'pending')));
5. $authors = $this->Article->User->find('count');
6. $publishedAuthors = $this->Article->find('count', array(
7. 'fields' => 'DISTINCT Article.user_id',
8. 'conditions' => array('Article.status !=' => 'pending')
9. ));
10. ...
11. }

Don't pass fields as an array to find('count'). You would only need to specify fields for a DISTINCT count (since otherwise, the count is always the same - dictated by the conditions).

There are no additional parameters used by find('count').

find('all', $params)

find('all') returns an array of (potentially multiple) results. It is in fact the mechanism used by all find() variants, as well as paginate. Below are a couple of simple (controller code) examples:

function some_function() {
   ...
   $allArticles = $this->Article->find('all');
   $pending = $this->Article->find('all', array('conditions' => array('Article.status' => 'pending')));
   $allAuthors = $this->Article->User->find('all');
   $allPublishedAuthors = $this->Article->User->find('all', array('conditions' => array('Article.status !=' => 'pending')));
   ...
}

1. function some_function() {
2. ...
3. $allArticles = $this->Article->find('all');
4. $pending = $this->Article->find('all', array('conditions' => array('Article.status' => 'pending')));
5. $allAuthors = $this->Article->User->find('all');
6. $allPublishedAuthors = $this->Article->User->find('all', array('conditions' => array('Article.status !=' => 'pending')));
7. ...
8. }

In the above example $allAuthors will contain every user in the users table, there will be no condition applied to the find as none were passed.

The results of a call to find('all') will be of the following form:

Array
(
    [0] => Array
        (
            [ModelName] => Array
                (
                    [id] => 83
                    [field1] => value1
                    [field2] => value2
                    [field3] => value3
                )

            [AssociatedModelName] => Array
                (
                    [id] => 1
                    [field1] => value1
                    [field2] => value2
                    [field3] => value3
                )

        )
)

There are no additional parameters used by find('all').

find('list', $params)

find('list', $params) returns an indexed array, useful for any use where you would want a list such as for populating input select boxes. Below are a couple of simple (controller code) examples:

function some_function() {
   ...
   $allArticles = $this->Article->find('list');
   $pending = $this->Article->find('list', array('conditions' => array('Article.status' => 'pending')));
   $allAuthors = $this->Article->User->find('list');
   $allPublishedAuthors = $this->Article->User->find('list', array('conditions' => array('Article.status !=' => 'pending')));
   ...
}

1. function some_function() {
2. ...
3. $allArticles = $this->Article->find('list');
4. $pending = $this->Article->find('list', array('conditions' => array('Article.status' => 'pending')));
5. $allAuthors = $this->Article->User->find('list');
6. $allPublishedAuthors = $this->Article->User->find('list', array('conditions' => array('Article.status !=' => 'pending')));
7. ...
8. }

In the above example $allAuthors will contain every user in the users table, there will be no condition applied to the find as none were passed.

The results of a call to find('list') will be in the following form:

Array
(
    //[id] => 'displayValue',
    [1] => 'displayValue1',
    [2] => 'displayValue2',
    [4] => 'displayValue4',
    [5] => 'displayValue5',
    [6] => 'displayValue6',
    [3] => 'displayValue3',
)

When calling find('list') the fields passed are used to determine what should be used as the array key, value and optionally what to group the results by. By default the primary key for the model is used for the key, and the display field is used for the value. Some further examples to clarify:.

function some_function() {
   ...
   $justusernames = $this->Article->User->find('list', array('fields' => array('User.username'));
   $usernameMap = $this->Article->User->find('list', array('fields' => array('User.username', 'User.first_name'));
   $usernameGroups = $this->Article->User->find('list', array('fields' => array('User.username', 'User.first_name', 'User.group'));
   ...
}

1. function some_function() {
2. ...
3. $justusernames = $this->Article->User->find('list', array('fields' => array('User.username'));
4. $usernameMap = $this->Article->User->find('list', array('fields' => array('User.username', 'User.first_name'));
5. $usernameGroups = $this->Article->User->find('list', array('fields' => array('User.username', 'User.first_name', 'User.group'));
6. ...
7. }

With the above code example, the resultant vars would look something like this:

$justusernames = Array
(
    //[id] => 'username',
    [213] => 'AD7six',
    [25] => '_psychic_',
    [1] => 'PHPNut',
    [2] => 'gwoo',
    [400] => 'jperras',
)

$usernameMap = Array
(
    //[username] => 'firstname',
    ['AD7six'] => 'Andy',
    ['_psychic_'] => 'John',
    ['PHPNut'] => 'Larry',
    ['gwoo'] => 'Gwoo',
    ['jperras'] => 'Joël',
)

$usernameGroups = Array
(
    ['Uber'] => Array
        (
	    ['PHPNut'] => 'Larry',
	    ['gwoo'] => 'Gwoo',
        )

    ['Admin'] => Array
        (
	    ['_psychic_'] => 'John',
	    ['AD7six'] => 'Andy',
	    ['jperras'] => 'Joël',
        )

)

find('threaded', $params)

find('threaded', $params) returns a nested array, and is appropriate if you want to use the parent_id field of your model data to build nested results. Below are a couple of simple (controller code) examples:

function some_function() {
   ...
   $allCategories = $this->Category->find('threaded');
   $aCategory = $this->Category->find('first', array('conditions' => array('parent_id' => 42)); // not the root
   $someCategories = $this->Category->find('threaded', array(
	'conditions' => array(
		'Article.lft >=' => $aCategory['Category']['lft'], 
		'Article.rght <=' => $aCategory['Category']['rght']
	)
   ));
   ...
}

1. function some_function() {
2. ...
3. $allCategories = $this->Category->find('threaded');
4. $aCategory = $this->Category->find('first', array('conditions' => array('parent_id' => 42)); // not the root
5. $someCategories = $this->Category->find('threaded', array(
6. 'conditions' => array(
7. 'Article.lft >=' => $aCategory['Category']['lft'],
8. 'Article.rght <=' => $aCategory['Category']['rght']
9. )
10. ));
11. ...
12. }

It is not necessary to use the Tree behavior to use this method - but all desired results must be possible to be found in a single query.

In the above code example, $allCategories will contain a nested array representing the whole category structure. The second example makes use of the data structure used by the Tree behavior the return a partial, nested, result for $aCategory and everything below it. The results of a call to find('threaded') will be of the following form:

Array
(
    [0] => Array
        (
            [ModelName] => Array
                (
                    [id] => 83
                    [parent_id] => null
                    [field1] => value1
                    [field2] => value2
                    [field3] => value3
                )

            [AssociatedModelName] => Array
                (
                    [id] => 1
                    [field1] => value1
                    [field2] => value2
                    [field3] => value3
                )
            [children] => Array
                (
		    [0] => Array
			(
			    [ModelName] => Array
				(
				    [id] => 42
		                    [parent_id] => 83
				    [field1] => value1
				    [field2] => value2
				    [field3] => value3
				)

			    [AssociatedModelName] => Array
				(
				    [id] => 2
				    [field1] => value1
				    [field2] => value2
				    [field3] => value3
				)
		            [children] => Array
				(
				)
	                )
			...
                )
        )
)

The order results appear can be changed as it is influence by the order of processing. For example, if 'order' => 'name ASC' is passed in the params to find('threaded'), the results will appear in name order. Likewise any order can be used, there is no inbuilt requirement of this method for the top result to be returned first.

There are no additional parameters used by find('threaded').

find('neighbors', $params)

'neighbors' will perform a find similar to 'first', but will return the row before and after the one you request. Below is a simple (controller code) example:

function some_function() {
   $neighbors = $this->Article->find('neighbors', array('field' => 'id', 'value' => 3));
}

1. function some_function() {
2. $neighbors = $this->Article->find('neighbors', array('field' => 'id', 'value' => 3));
3. }

You can see in this example the two required elements of the $params array: field and value. Other elements are still allowed as with any other find (Ex: If your model acts as containable, then you can specify 'contain' in $params). The format returned from a find('neighbors') call is in the form:

Array
(
    [prev] => Array
        (
            [ModelName] => Array
                (
                    [id] => 2
                    [field1] => value1
                    [field2] => value2
                    ...
                )
            [AssociatedModelName] => Array
                (
                    [id] => 151
                    [field1] => value1
                    [field2] => value2
                    ...
                )
        )
    [next] => Array
        (
            [ModelName] => Array
                (
                    [id] => 4
                    [field1] => value1
                    [field2] => value2
                    ...
                )
            [AssociatedModelName] => Array
                (
                    [id] => 122
                    [field1] => value1
                    [field2] => value2
                    ...
                )
        )
)

Note how the result always contains only two root elements: prev and next.

findAll(string $conditions, array $fields, string $order, int $limit, int $page, int $recursive)

findAll has been deprecated, use find('all') instead.

Returns the specified fields up to $limit records matching $conditions (if any), start listing from page $page (default is page 1). If there are no matching fields, an empty array is returned.

The $conditions should be formed just as they would in an SQL statement: $conditions = "Pastry.type LIKE '%cake%' AND Pastry.created_on > '2007-01-01'", for example. Prefixing conditions with the model's name ('Pastry.type' rather than just 'type') is always a good practice, especially when associated data is being fetched in a query.

Setting the $recursive parameter to an integer forces findAll() to fetch data according to the behavior described in the Model Attributes $recursive section outlined earlier. Do not forget to manually add the required foreign key columns to the $fields array as described there.

Data from findAll() is returned in an array, following this basic format:

Array
(
    [0] => Array
        (
            [ModelName] => Array
                (
                    [id] => 83
                    [field1] => value1
                    [field2] => value2
                    [field3] => value3
                )

            [AssociatedModelName] => Array
                (
                    [id] => 1
                    [field1] => value1
                    [field2] => value2
                    [field3] => value3
                )
        )
    [1] => Array
        (
            [ModelName] => Array
                (
                    [id] => 85
                    [field1] => value1
                    [field2] => value2
                    [field3] => value3
                )

            [AssociatedModelName] => Array
                (
                    [id] => 2
                    [field1] => value1
                    [field2] => value2
                    [field3] => value3
                )
        )
)

findAllBy<fieldName>(string $value)

These magic functions can be used as a shortcut to search your tables by a certain field. Just add the name of the field (in CamelCase format) to the end of these functions, and supply the criteria for that field as the first parameter.

PHP5 findAllBy<x> Example	Corresponding SQL Fragment
$this->Product->findAllByOrderStatus(‘3’);	Product.order_status = 3
$this->Recipe->findAllByType(‘Cookie’);	Recipe.type = ‘Cookie’
$this->User->findAllByLastName(‘Anderson’);	User.last_name = ‘Anderson’
$this->Cake->findById(7);	Cake.id = 7
$this->User->findByUserName(‘psychic’);	User.user_name = ‘psychic’

PHP4 users have to use this function a little differently due to some case-insensitivity in PHP4:

PHP4 findAllBy<x> Example	Corresponding SQL Fragment
$this->Product->findAllByOrder_status(‘3’);	Product.order_status = 3
$this->Recipe->findAllByType(‘Cookie’);	Recipe.type = ‘Cookie’
$this->User->findAllByLast_name(‘Anderson’);	User.last_name = ‘Anderson’
$this->Cake->findById(7);	Cake.id = 7
$this->User->findByUser_name(‘psychic’);	User.user_name = ‘psychic’

findBy() functions like find('first',...), while findAllBy() functions like find('all',...).

In either case, the returned result is an array formatted just as it would be from find() or findAll(), respectively.

findBy<fieldName>(string $value)

These magic functions can be used as a shortcut to search your tables by a certain field. Just add the name of the field (in CamelCase format) to the end of these functions, and supply the criteria for that field as the first parameter.

PHP5 findAllBy<x> Example	Corresponding SQL Fragment
$this->Product->findAllByOrderStatus(‘3’);	Product.order_status = 3
$this->Recipe->findAllByType(‘Cookie’);	Recipe.type = ‘Cookie’
$this->User->findAllByLastName(‘Anderson’);	User.last_name = ‘Anderson’
$this->Cake->findById(7);	Cake.id = 7
$this->User->findByUserName(‘psychic’);	User.user_name = ‘psychic’

PHP4 users have to use this function a little differently due to some case-insensitivity in PHP4:

PHP4 findAllBy<x> Example	Corresponding SQL Fragment
$this->Product->findAllByOrder_status(‘3’);	Product.order_status = 3
$this->Recipe->findAllByType(‘Cookie’);	Recipe.type = ‘Cookie’
$this->User->findAllByLast_name(‘Anderson’);	User.last_name = ‘Anderson’
$this->Cake->findById(7);	Cake.id = 7
$this->User->findByUser_name(‘psychic’);	User.user_name = ‘psychic’

findBy() functions like find('first',...), while findAllBy() functions like find('all',...).

In either case, the returned result is an array formatted just as it would be from find() or findAll(), respectively.

findNeighbours(string $conditions, mixed $field, string $value)

findNeighbours has been deprecated, use find('neighbors') instead.

This shortcut method creates an array containing values helpful in generating 'Previous' and 'Next' links in a view.

The method determines which data rows to return based on the values submitted in the $field and $value parameters. Further refinement can be done with the $conditions parameter.

For example, if you call the function like this:

$conditions = array('Article.status' => 'published');
$field = array('date', 'id');
$value = '2008-03-24';
$this->Article->findNeighbours( $conditions, $field, $value ) );

1. $conditions = array('Article.status' => 'published');
2. $field = array('date', 'id');
3. $value = '2008-03-24';
4. $this->Article->findNeighbours( $conditions, $field, $value ) );

The resulting array will contain values for the 'date' and 'id' fields from the articles who have a status of "published", and whose dates are just before and after the date '2008-03-24'.

Array
(
    [prev] => Array ([Article] => 
             Array ([date] => 2008-03-20, [id] => 99 )
    ),
    [next] => Array ( [Article] => 
             Array( [date] => 2008-03-27, [id] => 15 )
    )
);

Note that the comparison was made on date field, and that the id values were not used to determine neighboring data.

This method can also be called with the $field value being a single string. When an array is used, the first field listed will be the field used in the comparison query.

class ImagesController extends AppController {
    function view($id) {
        // Say we want to be able to show the image...
        $this->set('image', $this->Image->findById($id);

        // But we also want links to the previous and next images...
        $this->set(
            'neighbors', 
            $this->Image->findNeighbours(null, 'id', $id);
        )
    }
}

1. class ImagesController extends AppController {
2. function view($id) {
3. // Say we want to be able to show the image...
4. $this->set('image', $this->Image->findById($id);
5. // But we also want links to the previous and next images...
6. $this->set(
7. 'neighbors',
8. $this->Image->findNeighbours(null, 'id', $id);
9. )
10. }
11. }

This gives us the full $image['Image'] array, along with $neighbors['prev']['Image']['id'] and $neighbors['next']['Image']['id'] for use in the view.

query(string $query)

SQL calls that you can't or don't want to make via other model methods (careful - there are very few circumstances this is true) can be made using the model's query() method.

If you’re ever using this method in your application, be sure to check out CakePHP’s Sanitize library, which aids in cleaning up user-provided data from injection and cross-site scripting attacks.

query() does not honour $Model->cachequeries as its functionality is inherently disjoint from that of the calling model. To avoid caching calls to query, supply a second argument of false, ie: query($query, $cachequeries = false)

query() uses the table name in the query as the array key for the returned data, rather than the model name. For example,

$this->Picture->query("SELECT * FROM pictures LIMIT 2;");

1. $this->Picture->query("SELECT * FROM pictures LIMIT 2;");

might return

Array
(
    [0] => Array
        (
            [pictures] => Array
                (
                    [id] => 1304
                    [user_id] => 759
                )
        )

    [1] => Array
        (
            [pictures] => Array
                (
                    [id] => 1305
                    [user_id] => 759
                )
        )
)

1. Array
2. (
3. [0] => Array
4. (
5. [pictures] => Array
6. (
7. [id] => 1304
8. [user_id] => 759
9. )
10. )
11. [1] => Array
12. (
13. [pictures] => Array
14. (
15. [id] => 1305
16. [user_id] => 759
17. )
18. )
19. )

To use the model name as the array key, and get a result consistent with that returned by the Find methods, the query can be rewritten:

$this->Picture->query("SELECT * FROM pictures AS Picture LIMIT 2;");

1. $this->Picture->query("SELECT * FROM pictures AS Picture LIMIT 2;");

which returns

Array
(
    [0] => Array
        (
            [Picture] => Array
                (
                    [id] => 1304
                    [user_id] => 759
                )
        )

    [1] => Array
        (
            [Picture] => Array
                (
                    [id] => 1305
                    [user_id] => 759
                )
        )
)

1. Array
2. (
3. [0] => Array
4. (
5. [Picture] => Array
6. (
7. [id] => 1304
8. [user_id] => 759
9. )
10. )
11. [1] => Array
12. (
13. [Picture] => Array
14. (
15. [id] => 1305
16. [user_id] => 759
17. )
18. )
19. )

This syntax and the corresponding array structure is valid for MySQL only. Cake does not provide any data abstraction when running queries manually, so exact results will vary between databases.

generateList(string $conditions, string $order, int $limit, string $keyPath, string $valuePath)

generateList is deprecated and replaced by usage of find('list'), or find('all') combined with a call to Set::combine().

This function is a shortcut to getting a list of key/value pairs - especially handy for creating an HTML select tag from a list of your models. Use the $conditions, $order, and $limit parameters just as you would for a findAll() request.

If $primaryKey and $displayField have been set in the model, you don’t need to supply the last two parameters, as they act as $keyPath and $keyValue, respectively. Additionally, if neither $keyPath nor $displayField have been supplied, CakePHP will try to load the information using ‘title’ or ‘name’.

The $keyPath and $valuePath specify where to find the keys and values for your generated list. For example, if you wanted to generate a list of roles based on your Role model, keyed by their integer ids, the full call might look something like:

$this->Role->generateList(
    null, 
    'role_name ASC', 
    null, 
    '{n}.Role.id', 
    '{n}.Role.role_name'
);

//This would return something like:
array(
    '1' => 'Head Honcho',
    '2' => 'Marketing',
    '3' => 'Department Head',
    '4' => 'Grunt'
);

1. $this->Role->generateList(
2. null,
3. 'role_name ASC',
4. null,
5. '{n}.Role.id',
6. '{n}.Role.role_name'
7. );
8. //This would return something like:
9. array(
10. '1' => 'Head Honcho',
11. '2' => 'Marketing',
12. '3' => 'Department Head',
13. '4' => 'Grunt'
14. );

Many people are a little bewildered by the ‘{n}’ syntax used by generateList(). Fret not, for it serves as a place holder for switching between model DataSources, covered later on in this chapter.

findCount(string $conditions, int $recursive)

This method has been deprecated, use find('count').

Returns the number of records that match the given conditions. Use the $recursive parameter to have CakePHP fetch more (or fewer) levels of associated models.

field(string $name, array $conditions = null, string $order = null)

Returns the value of a single field, specified as $name, from the first record matched by $conditions as ordered by $order. If no conditions are passed and the model id is set, will return the field value for the current model result. If no matching record is found returns false.

$model->id = 22;
echo $model->field('name'); // echo the name for row id 22

echo $model->field('name', array('created <' => date('Y-m-d H:i:s')), 'created DESC'); // echo the name of the last created instance

1. $model->id = 22;
2. echo $model->field('name'); // echo the name for row id 22
3. echo $model->field('name', array('created <' => date('Y-m-d H:i:s')), 'created DESC'); // echo the name of the last created instance

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($conditions);

1. $conditions = array("Post.title" => "This is a post");
2. //Example usage with a model:
3. $this->Post->find($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');

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