This is probably going to be the first of many postings of my exploration with CakePHP. This post will briefly look at the CakePHP’s ‘Associations’ feature. Associations is “the relational mapping provided by the [CakePHP] model”.

In non-Cake speak, we’re talking about standard SQL joins – the mapping of relations between SQL tables. As you may know, doing SQL joins can be quite messy with unwieldy SQL strings. Thankfully, CakePHP provides a very simple way of joining tables.

This tutorial will extend from the Cake Blog Tutorial, by adding comments to each blog post. So you need to be have completed the Cake Blog Tutorial if you want to ‘hands-on’.

In this tutorial, I won’t be showing add, edit, delete comment actions since it follows pretty much the same idea as add, edit and delete posts. I’m only going as far as displaying comments associated with each blog post.

According to the Cake docs, there are four types of associations within CakePHP. These are…

  • hasOne
  • hasMany
  • belongsTo
  • hasAndBelongsTo

Here, I’m just going to use the hasMany associations between posts and comments. We’re basically saying that each post hasMany comments.

Create the comments table
First thing to do is to create the comments table and fill it up with one or two rows of data. Execute the following SQL statements in your favourite MySQL DB Manager.

CREATE TABLE `comments` (
`id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`body` TEXT NOT NULL ,
`post_id` INT( 10 ) NOT NULL ,
`created` DATETIME NULL ,
PRIMARY KEY ( `id` ) 
);
INSERT INTO `comments` (`id` ,`body` ,`post_id` ,`created` ) 
VALUES ( NULL , 'this is the first comment', '1', '2008-05-08 15:59:22');
INSERT INTO `comments` (`id` ,`body` ,`post_id` ,`created` ) 
VALUES ( NULL , 'this is the second comment', '1', '2008-05-08 16:38:20');

Create a Comment model
Now that you have the DB table and data rows in place, you can create a new Comment model. Cake’s models go into the /app/models directory. Create a file with the follow code and save it to /app/models/comment.php.

Create the association
The next thing to do is to create the hasMany association between Post and Comment models. I didn’t bother to do specify any Comments Controllers, since I won’t be implementing the add, edit, delete functions for comments.

Open up the /app/models/post.php file, and add the $hasMany definition to your existing post.php. You should have something like this:


		array('className' => 'Comment',
			'conditions' => '',
			'order' => 'Comment.created DESC',
			'limit' => '5',
			'foreignKey' => 'post_id',
			'dependent' => true,
			'exclusive' => false,
			'finderQuery'   => '',
			'fields' => '',
			'offset' => '',
			'counterQuery' => ''
		)
	);
    
    var $validate = array(
        'title'  => VALID_NOT_EMPTY,
        'body'   => VALID_NOT_EMPTY

    );
}
?>

let’s look at the $hasMany array in more detail:

  1. className (required) – is the name of the model we are associating with. In this case it’s the Comment model.
  2. conditions – is where you would specify any SQL conditions if required. This is like specifying the “WHERE” portion of a normal SQL statement.
  3. order – is where you define the sort order of the comments. This is equivalent to the “ORDER BY” portion of a standard SQL statement. In my case, I’m listing the comments in descending order based on comment created timestamp.
  4. limit – is where you define the number of comments to show, similar to “LIMIT” in a standard SQL statement. In my case, I’m specifying a maximum of 5 comments per post.
  5. foreignKey – is the name of the foreign key that points to the associated model, which is the post_id field in the comments table.
  6. dependant – if set to true, the associated Comment model is destroyed when the Post model is.
  7. exclusive – If set to true, all the associated objects are deleted in one SQL statement without having their beforeDelete callback run.
  8. finderQuery – This is a good way to do those complex associations, for example over multiple tables, if Cake’s automatic assocations aren’t working for you.
  9. fields – You can use this to specify only the fields from the associated model you wish to fetch. Since I didn’t specify anything, it will fetch all the fields from the Comment model.
  10. offset – This is number of records to skip before associating to the current model.
  11. counterQuery – Here you can use an SQL statement to define the number of records that should be associated from the Comment model.

Modify view.thtml to show comments
After you have specified the $hasMany array, the next thing to do is to change the /app/views/posts/view.thtml file to show comments after the post body.

Created:


Comments:

'.$comment['created'].'
'.$comment['body'].'

'; } ?>

Done!
If all has gone well, you should get something like the following screen capture. There will only be comments for the first post, since we only specify post_id as ‘1’ in the comments table.

CakePHP view blog post

Final thoughts…
All in all, it took me about 30-40 mins to digest the documentation on associations, and to work out this simple example. I would say that’s pretty fast to go from zero to a working example of how table joins are done.