Blog

CakePHP now has deprecated some queries. The findALL, findCount, etc etc etc. This now uses the find() method for all of these, and has a basic syntax for this. If it is basic, why blog about it then? Good question. The documentation is there in the API, and is there in the Cookbook, it took me some time to really get a good idea on an applicable example and took many times of “trial and error” to help me get along. Because finding is good, and will give you all the information you need, if it is a simple, 1 table find. If you need to query two or three tables, how does this work. So this will go into those as well.

The basic idea of find, as listed by the API is this:

find(
	array $conditions, 
	array $fields, 
	string $order, 
	int $recursive
);

This will find one record based on conditions, return the desired fields (or all of them if nothing is specified), order the results, and go so many levels deep (-1 for just the current table).

To get all the results instead of just one field, you would follow this:

find(
	string $notation_find, 
	array( 
		array $conditions, 
		array $fields, 
		string $order,
		int $recursive
	)
);

To find just one record, you need to use the first method, to find all records or a specific “notation” find, you would use the second method. The list of some of the “notations” are listed below:
all
neighbors
list
count
threaded

So to use examples of this, lets say we have a “forum” area of a site, where it reads from a User table, a Forum table and a Post table. We have three tables, and we need to find certain things:

1. The current Forum information (1 record, 1 table only)
2. The current number of posts in a forum (1 table, count)
3. The last post, and who posted it, in the selected forum (3 tables, 1 record, certain fields)
4. All posts in a forum topic, paginate results (3 tables, all records on specific conditions, certain fields)

The way these tables (Forums, Posts and Users) relate to each other is by use of table-name_id (user_id, forum_id, post_id). User can have many posts, post belongs to a user, post belongs to a forum, forum has many posts. Now we need to do a simple query to get current forum info. I am doing this all in a forums_controller.php file. These can be a myriad of different function names, but each function would always be looking for some type of input. NOTE: fields are arbitrary to this example. You can have as many or as little fields needed.

1. The current Forum information (1 record, 1 table only)

$forums = $this->Forum->find(
	array('Forum.forum_id' => $id),
	array('Forum.forum_id', 'Forum.forum_name', 'Forum.description', 'Forum.created'),
	null,
	-1
);

This grabs the current, or selected forum (passing in the $id from the function parameters), grabbing four fields from the table, no need to sort 1 record, and I only want this table’s data. This would return something like:

Array(
	[Forum] => Array(
		[forum_id] => 1
		[forum_name] => Sample Forum
		[description] => A Forum to discuss anything, upcoming and past. 
		[created] => 2008-07-23 20:15:41
	)
)

2. The current number of posts in a forum (1 table -Posts-, count)

$post_count = $this->Post->find(
	'count', 
	array(
		'conditions' => array('forum_id' => $id), 
		'recursive' => -1
	)
);

With using count, there is no need to get fields or sort order, all we really need to do is get the conditions set, which are: all posts with the selected forum_id value. This returns a number of the records returned. Let’s say for this example it returned 12.

3. The last post, and who posted it, in the selected forum (3 tables -Users Posts Forums- , 1 record, certain fields)
For this we can do a trick in the query, instead of getting just all the records, or the last entered one, we could try this:

$lastP = $this->Post->find(
	array('Post.forum_id' => $id), //array of conditions
	array('Post.created', 'User.username', 'Post.parent_topic', 'Post.title', 'Post.post_id', 'Forum.forum_name'), //array of field names
	'Post.created DESC', //string or array defining order
	-1, //int recursive level
	1 //int number of records to return
);

This is going to query the tables, where these conditions are met, order them descending order, and then return 1 record, the first one, which will be the last entry made in the table with that forum_id. Now we get into the “meat” of this thing.

4. All posts in a forum topic, paginate results (3 tables, all records on specific conditions, certain fields)

// Get the parent post
$parent = $this->Post->find(
	array('Post.post_id' => $id), 
	array('Post.post_id', 'Post.forum_id', 'Post.title', 'Post.details', 'Post.created', 'User.username', 'User.avatar', 'User.biography', 'User.created', 'Forum.name'), 
	'Post.created DESC', 
	0
);

In this example, and as previously noted in my earlier blog posts, the “Topic” is an entry in the Posts table, and all replies to that post reside in the same table, but reference that post_id as the parent_topic value. So the first thing I do is get this “Topic” information. I do some checks on this (which is outside the scope of this entry). Then I am ready to grab the replies to this topic, and then paginate the results. Since I already referenced the controller to use the paginator, I need to make a call to the paginator, and I need to apply a condition to it:

// grab all of the posts for this parent topic, and get the parent topic in the pagintor
$cond = array( 'OR' =>array( 'Post.parent_topic' => $id, 'Post.post_id' => $id ) );
$this->set( 'posts', $this->paginate( "Post", $cond ) );

One of the important things to note in this query, is the use of the $cond variable. I set this to use an “OR” clause. If nothing is specified, then the find() or paginator() assumes it is an “AND” clause, which is not the case here. So I set the conditions to look for the the $id value in either the post_id or the parent_topic columns. Be sure to check out the Cookbook for more information on complex queries.

But that is about it. Real world examples in use today using the new find() methods. The best part about these, is that with a little tweak here or there, you can have queries do what you want them to do, with little coding needed to accomplish the task.