CakePHP SQL Lists
As promised, we continue the discussion on how to use $model->find and its various types. Earlier we looked at getting neighbors so today let's look at the "list" type. Just like neighbors, we fetch the list using the following method:
$model->find("list", $conditions);
But what is the purpose of list?. To understand that, let's look at the output from an actual find method with a "list" type.
Let's use a real world example. Say you're looking at creating a list of all Posts that have been published in the current month by a particular posts' author.
First make sure your Post model has the proper relationship with the User/Author model. This would be created in the Model definition using the $belongsTo relationship. Here's what your Post model might look like:
//Your Post model class Post extends AppModel { public $name = "Post"; //Create belongsTo relationship public $belongsTo = array("User"=>array("conditions"=>
array("User.active"=>1), "fields"=>array("User.id"))); }
This binds the User Model to the Post Model automatically so now we can create the "query" in cake which matches the above mentioned conditions. So your Controller action would look like this:
/*Fetches a list of author's posts for a given month and year * Defaults to current month and year if date not provided * @param $author_id - Number - the user_id of the author * @param $month - Number - single digit month * @param $year - Number - four digit year * @return Array - List of id with Post titles for matching results */ public function getAuthorPostsForMonth($author_id, $month=null, $year=null){ //set defaults to current time if ($month == null) $month = date("n"); if ($year == null) $year = date("Y"); //get first and last date timestamps for the month/year $first = mktime(0, 0, 0, $month, 1, $year); $daysInMonth = date("t", $first); $last = mktime(23, 59, 59, $month, $daysInMonth, $year); //Convert timestamps to SQL dates $first = date("Y-m-d H:i:s", $first); $last = date("Y-m-d H:i:s", $last); //Create findConditions $cond["fields"] = array("Post.id", "Post.title"); $cond["order"] = "Post.pubdate DESC"; $cond["conditions"]["Post.publish"] = 1; $cond["conditions"]["Post.pubdate BETWEEN ? AND ?"] = array($first, $last); $cond["conditions"]["User.id"] = $author_id; $cond["recursive"] = 1; return $this->Post->find("list", $cond); }
When you run this method from your controller:
$controller->getAuthorPostsForMonth(1, 7, 2008);
the following SQL get's generated:
SELECT `Post`.`id`, `Post`.`title` FROM `posts` AS `Post` LEFT JOIN `users` AS `User` ON (`Post`.`user_id` = `User`.`id` AND `User`.`active` = 1) WHERE `Post`.`publish` = 1 AND `Post`.`pubdate` BETWEEN '2008-07-01 00:00:00' AND '2008-07-31 23:59:59' AND `User`.`id` = 1 ORDER BY `Post`.`pubdate` DESC
Note the LEFT JOIN which automatically get's created because our Post model has a belongsTo relationship with the User Model and our find method sets the recursive flag to 1. Running this gets us the following type of result:
Array
(
[13] => A title
[12] => Title of this post
[11] => CakePHP is easy and fast
[10] => Another kick-ass site for CakePHP
[9] => Kalabaaz really rocks!
[8] => Don't you think so?
)
As you can see, this is a numeric array where the ids are the keys and the titles are the value. This is because, we searched the "list" with 2 fields, Post.id and Post.title. Now you may use a foreach loop to display this as a list of all posts with their titles. This is obviously very helpful when you want to display a simple list or create a drop-down box of countries/states etc. This kind of structure is also ideal for using the $form->select method in FormHelper.
Hope that was helpful :) Next we'll look at the "threaded" type. Stay tuned.
