We need some sample models and data to work with. Follow the Getting ready section of the recipe, Performing GROUP and COUNT queries.

To illustrate the difference between normal binding operations and what is shown in this recipe, we need the Containable behavior. Create a file named app_model.php and place it in your app/ folder, with the following contents. If you already have one, make sure that, either you add the actsAs property shown as follows, or your actsAs property includes Containable.

<?php
class AppModel extends Model {
public $actsAs = array('Containable'),
}
?>

We also need the Blog model. Create a file named blog.php and place it in your app/models folder with the following contents:

<?php
class Blog extends AppModel {
public $belongsTo = array('User'),
}
?>

We want to obtain the first post with the Blog it belongs to, and the User information that owns the Blog. Using Containable (refer to the recipe Limiting the bindings returned in a find in Chapter 2, Model Bindings for more information), we do:

$post = $this->Post->find('first', array(
'contain' => array(
'Blog' => array(
'fields' => array('name'),
'User' => array('fields' => array('name'))
)
)
));

This operation is performed by CakePHP using three SQL queries:

SELECT `Post`.`id`, `Post`.`blog_id`, `Post`.`title`, `Post`.`body`, `Post`.`created`, `Post`.`modified`, `Blog`.`name`, `Blog`.`user_id` FROM `posts` AS `Post` LEFT JOIN `blogs` AS `Blog` ON (`Post`.`blog_id` = `Blog`.`id`) WHERE 1 = 1 LIMIT 1;
SELECT `Blog`.`name`, `Blog`.`user_id` FROM `blogs` AS `Blog` WHERE `Blog`.`id` = 1;
SELECT `User`.`name` FROM `users` AS `User` WHERE `User`.`id` = 1;

We can save some of these queries if we JOIN the relevant tables into one single operation. We specify these JOIN statements using the appropriate join find setting:

$post = $this->Post->find('first', array(
'fields' => array(
'Post.id',
'Post.title',
'Blog.name',
'User.name'
),
'joins' => array(
array(
'type' => 'inner',
'alias' => 'Blog',
'table' => $this->Post->Blog->table,
'conditions' => array(
'Blog.id = Post.blog_id'
)
),
array(
'type' => 'inner',
'alias' => 'User',
'table' => $this->Post->Blog->User->table,
'conditions' => array(
'User.id = Blog.user_id'
)
)
),
'recursive' => -1
));

The preceding statement will produce the following SQL query:

SELECT `Post`.`id`, `Post`.`blog_id`, `Post`.`title`, `Post`.`body`, `Post`.`created`, `Post`.`modified` FROM `posts` AS `Post` inner JOIN blogs AS `Blog` ON (`Blog`.`id` = `Post`.`blog_id`) inner JOIN users AS `User` ON (`User`.`id` = `Blog`.`user_id`) WHERE 1 = 1 LIMIT 1
And would generate the following data structure:
array(
'Post' => array(
'id' => 1,
'title' => 'John's Post 1'
),
'Blog' => array(
'name' => 'John Doe's Blog'
),
'User' => array(
'name' => 'John Doe'
)
)

The joins find setting allows us to define which JOIN statements to add to the generated SQL query. We have full control when defining the operation, being able to change the type (one of left, right, and inner), the table to which to join, the alias to use, and the conditions used when joining.

We used this setting to join the Post model with two models: Blog, by means of its table and required condition, and User, using its appropriate table and condition. As the Post model belongsTo the Blog model, CakePHP will automatically try to do a LEFT JOIN with it, unless we tell it not to.

We therefore set recursive to -1, forcing CakePHP to only use our defined JOIN. If the recursive statement is removed, we would have to choose a different alias for our Blog JOIN definition, as it would conflict with CakePHP's built-in binding.