CakePHP find conditions

CakePHP find conditions (plus find all, find list, and find count)

CakePHP find FAQ: Can you share some "CakePHP find conditions" examples?

One thing you need to get used to when working with CakePHP is the CakePHP find method. To that end, I thought I'd put together this page of CakePHP find conditions and find query examples.
CakePHP find conditions

To get started, here's a list of the possible CakePHP find conditions you can use:

type      

• 'all'
• 'first' (default)
• 'list'

conditions

• array containing the find (select) conditions as key/value pairs
• default (null)

fields    

• array specifying which fields should be retrieved in the resulting select query
• default (null)

order

• sql 'order by conditions. field name must be followed by ASC or DESC
• default (null)

page      

• page number, used for paged data
• default (null)

limit

•  a limit on the number of results returned, like 'select * from orders limit 20'.
• default (1)

offset

• sql offset value (i haven't used this query much myself, but i think it refers to skipping X number of rows returned in a query)

recursive

• the cakephp recursive value, relating to associated model data

CakePHP find condition examples

Given that reference information, let's take a look at some CakePHP find conditions examples.

First, a very simple CakePHP find query that retrieves all records from the Post model (probably not something you'll want to do in a production application):

$this->Post->find('all');

Next, a "CakePHP find all" query with one find condition:

$this->Post->find('all', array('conditions'=>array('User.id'=>5)));

A CakePHP find query with one "not equal to" find condition:

$this->Post->find('all', array('conditions'=>array('User.id'=>'<> 5')));

A find query with multiple CakePHP find conditions:

# this is a little lame, but i'm trying to avoid dates
$this->Post->find('all', array('conditions'=>array('User.id'=>1, 'Post.id'=>'> 50')));

A CakePHP find query that uses all the find function parameters:

# TODO - i'm not sure that this is right; i think 'fields' is supposed to be an array
$this->Post->find('all',
                  array('conditions'=>array('User.id'=>5),
                        'fields'=>'Post.name',
                        'order'=>'Post.id ASC',
                        'limit'=>20,
                        'recursive'=>0));

A CakePHP find conditions query using a date:

# note: you can search for date or datetime fields by enclosing the table's field name
#       in the SQL DATE() function.
$this->Post->find('all',
                   array('conditions'=>array('User.id'=>5,
                   'DATE(Post.date)'=>'CURDATE()')));

# TODO demonstrate "date >" and "date <" conditions

CakePHP find conditions arrays with ORDER BY examples:

array('order'=>'date ASC')
array('order'=>'date DESC')
array('order'=>'User.id DESC')

A collection of other CakePHP find conditions/examples:

These CakePHP find examples are lines of code that would be used in an OrderController class:

$this->Order->find('all');
$this->Order->find(null, null, 'date DESC');
$this->Order->find('all', array('conditions'=>array('User.id'=>1)));
$this->Order->find('all', array('conditions'=>array('User.id'=>array(1,2,3,4))));
$this->Order->find('all', array('conditions'=>array('User.id'=>'<> 1')));
$this->Order->find('all', array('conditions'=>array('User.id'=>1, 'DATE(Post.date)'=>'CURDATE()')));
$this->Order->find('all', array('order'=>'date ASC', 'limit'=>20, 'recursive'=>0);

Here are some CakePHP find examples from the CakePHP retrieving your data book page:

$params can contain all these:

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
  'offset'=>n, //int  
  'callbacks' => true //other possible values are false, 'before', 'after'
)

Here's a CakePHP function showing several different CakePHP find examples:

# find('first', $params) syntax

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)));
  ...
}

CakePHP find count examples

Here's a CakePHP find count example:

# find('count', $params)

# more cakephp find count 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')
   ));
   ...
}

CakePHP find all examples

Here is a list of some CakePHP find all examples:

# find('all', $params) syntax

# a cakephp find all example
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')));
   ...
}

CakePHP find list examples

Here are some CakePHP find list examples, useful for creating select boxes:

# find('list', $params) syntax

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->find('list', array(
        'fields' => array('User.id', 'User.name'),
        'conditions' => array('Article.status !=' => 'pending'),
        'recursive' => 0
    ));
   ...
}

See the CakePHP retrieving your data book page for many more CakePHP find examples, including:

    find threaded
    find neighbors
    findAllBy
    findBy
    query
    field
    read
    More complex CakePHP find examples