Fetching a nested array of associated records:
If your table has parent_id field you can also use find(‘threaded’) to fetch nested array of records using
a single query without setting up any associations.Joining tables
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.
Note: Remember you need to set the recursion to -1 for this to work. I.e: $this->Channel->recursive = -1;
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);
Note: 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’,