How to Join Database Tables (Models) in CakePHP

CakePHP has four association types to handle all database table (model) joining operations.
1. hasOne
2. hasMany
3. belongsTo
4. hasAndBelongsToMany (HABTM)

In our database model, each category should have many posts.
First add a foreign key, category_id.  in the post table.
SQL:


ALTER TABLE `posts` ADD `category_id` INT NOT NULL AFTER `id` ;

Foreign Key Convention
Notice the use of (model_name)_id field in the post table. This is a Cake's convention to name foreign key in related tables. Also notice that I have used category_id, and not categories_id. So, the foreign key rule is to name the foreign key field as (model_name_in_singular)_id in the related table. That's it.

Now I'll update both category model and post model to include their association.

Category Model
File: // app/models/category.php

<?php
class Category extends AppModel {
var $name = 'Category';
var $actsAs = array('Tree');
var $hasMany = 'Post'; /* each category has many posts */
}
?>

Post Model
File: // app/models/post.php

<?php
class Post extends AppModel {
var $name = 'Post';
var $belongsTo = 'Category'; /* each post belongs to a category */
var $validate = array(
'title' => 'notEmpty',
'body'  => 'notEmpty'
);


}
?>

Modify PostsController Class
Now in posts_controller.php, modify your add function.
file: // app/controllers/posts_controller.php


 function add() {
if (!empty($this->data)) {


if ($this->Post->save($this->data)) {
$this->Session->setFlash('Your post has been saved.');
$this->redirect(array('action' => 'index'));
}
} else {


$this->set('categories', $this->Post->Category->generatetreelist(null,null,null," - "));


}


}

Note:

$this->set('categories', $this->Post->Category->generatetreelist(null,null,null," - "));

This will fetch the category tree from categories table.

Modify Related Views to show category tree.

file: // app/views/posts/add.ctp

<h1>Add Post</h1>

<?php

echo $form->create('Post');

echo $form->input('category_id',array('label'=>'Select a category'));

echo $form->input('title');

echo $form->input('body', array('rows' => '3'));

echo $form->end('Save Post');

?>

Now, point your browser to:
http://caketest.local/posts/add

In this example: Cake has created the magical associations to take care of everything. Cake generates Category Tree, displays it in the form, and save category_id in the posts table on the fly.

For a complete tutorial on Models, Saving/ Retrieving Data, and building associations between different models in CakePHP, please see this tutorial.

Now in my next example, I'll try to list posts by categories.

Take care.

Update
Besides, you might just want to update posts as well.

Modify PostsController
Modify edit() function in the following file.
File: // app/controllers/posts_controller.php

function edit($id = null) {
$this->Post->id = $id;
if (empty($this->data)) {
$this->data = $this->Post->read();
$this->set('categories', $this->Post->Category->generatetreelist(null,null,null," - "));


} else {
if ($this->Post->save($this->data)) {
$this->Session->setFlash('Your post has been updated.');
$this->redirect(array('action' => 'index'));
}
}
}

2. Now modify post view
file: // app/views/posts/edit.php


<h1>Edit Post</h1>
<?php
echo $form->create('Post', array('action' => 'edit'));
echo $form->input('category_id',array('label'=>'Select a category'));
echo $form->input('title');
echo $form->input('body', array('rows' => '3'));
echo $form->input('id', array('type'=>'hidden')); 
echo $form->end('Save Post');
?>

Now if you try to edit an existing post, you can see the category tree with the selected category being displayed.
Done.