Welcome to the Cookbook

このセクションには保留されている変更があります. More information about translations

In SQL you can combine related tables using the JOIN statement. This allows you to perform complex searches across multiples tables (i.e: search posts given several tags).

In CakePHP some associations (belongsTo and hasOne) performs automatic joins to retrieve data, so you can issue queries to retrieve models based on data in the related one.

But this is not the case with hasMany and hasAndBelongsToMany associations. Here is where forcing joins comes to the rescue. You only have to define the necessary joins to combine tables and get the desired results for your query.

To force a join between tables you need to use the "modern" syntax for Model::find(), adding a 'joins' key to the $options array. For example:

$options['joins'] = array(
    array(
        'table' => 'channels',
        'alias' => 'Channel',
        'type' => 'LEFT',
        'conditions' => array(
            'Channel.id = Item.channel_id',
        )
    )
);

$Item->find('all', $options);

1. $options['joins'] = array(
2. array(
3. 'table' => 'channels',
4. 'alias' => 'Channel',
5. 'type' => 'LEFT',
6. 'conditions' => array(
7. 'Channel.id = Item.channel_id',
8. )
9. )
10. );
11. $Item->find('all', $options);

Note that the 'join' arrays are not keyed.

In the above example, a model called Item is left joined to the channels table. You can alias the table with the model name, so the retrieved data complies with the CakePHP data structure.

The keys that define the join are the following:

• table: The table for the join.
• alias: An alias to the table. The name of the model associated with the table is the best bet.
• type: The type of join: inner, left or right.
• conditions: The conditions to perform the join.

With joins, you could add conditions based on related model fields:

$options['joins'] = array(
    array('table' => 'channels',
        'alias' => 'Channel',
        'type' => 'LEFT',
        'conditions' => array(
            'Channel.id = Item.channel_id',
        )
    )
);

$options['conditions'] = array(
	'Channel.private' => 1
);

$pirvateItems = $Item->find('all', $options);

1. $options['joins'] = array(
2. array('table' => 'channels',
3. 'alias' => 'Channel',
4. 'type' => 'LEFT',
5. 'conditions' => array(
6. 'Channel.id = Item.channel_id',
7. )
8. )
9. );
10. $options['conditions'] = array(
11. 'Channel.private' => 1
12. );
13. $pirvateItems = $Item->find('all', $options);

You could perform several joins as needed in hasBelongsToMany:

Suppose a Book hasAndBelongsToMany Tag association. This relation uses a books_tags table as join table, so you need to join the books table to the books_tags table, and this with the tags table:

$options['joins'] = array(
	array('table' => 'books_tags',
		'alias' => 'BooksTag',
		'type' => 'inner',
		'conditions' => array(
			'Books.id = BooksTag.book_id'
		)
	),
	array('table' => 'tags',
		'alias' => 'Tag',
		'type' => 'inner',
		'conditions' => array(
			'BooksTag.tag_id = Tag.id'
		)
	)
);

$options['conditions'] = array(
	'Tag.tag' => 'Novel'
);

$books = $Book->find('all', $options);

1. $options['joins'] = array(
2. array('table' => 'books_tags',
3. 'alias' => 'BooksTag',
4. 'type' => 'inner',
5. 'conditions' => array(
6. 'Books.id = BooksTag.book_id'
7. )
8. ),
9. array('table' => 'tags',
10. 'alias' => 'Tag',
11. 'type' => 'inner',
12. 'conditions' => array(
13. 'BooksTag.tag_id = Tag.id'
14. )
15. )
16. );
17. $options['conditions'] = array(
18. 'Tag.tag' => 'Novel'
19. );
20. $books = $Book->find('all', $options);

Using joins with Containable behavior could lead to some SQL errors (duplicate tables), so you need to use the joins method as an alternative for Containable if your main goal is to perform searches based on related data. Containable is best suited to restricting the amount of related data brought by a find statement.