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
