I have Three tables Projects, Tasks and Tags. Projects.id is the primary key of the first table, Tasks.id is the PK of the second table and Tags.id is the PK of Third table.
Projects Model Code snippet
public $primaryKey = 'id';
public $hasMany = array(
'Tasks' => array('className' => 'Tasks','foreignKey' => 'project_id')
);
Tasks Model Code snippet
public $primaryKey = 'id';
public $hasMany = array(
'Tags' => array('className' => 'Tags','foreignKey' => 'task_id')
);
The below query return some unexpected result.
$data = $this->Projects->find('all', array(
'recursive' => 2,
'contain' => array(
'Tags' => array(
'conditions' => array('Tags.tag_name =' => "Driver")
)
)
));
The response data i am getting is
[Projects] => Array
(
[id] => 1
[project_id] => 1234
[project_name] => XYZ
)
[Tasks] => Array
(
[0] => Array
(
[id] => 1
[project_id] => 1
[task_id] => 12
[task_name] => task1
[Tags] => Array
(
[0] => Array
(
[id] => 1
[task_id] => 1
[tag_id] => 3444
[tag_name] => Driver
)
)
)
[1] => Array
(
[id] => 2
[project_id] => 1
[task_id] => 343242
[task_name] => task2
[Tags] => Array
(
)
)
See i am even getting tasks without tag as Driver. How i can exclude tasks without tag Driver?
In another word task1 do have Tag as Driver, so that should be there. But task2 don't have any tag, even then the return data contain task2. How to fix it?
You cannot limit the results of the main or parent model based on conditions on child models because "contain" actually creates separate queries for each contain model.
To get the proper result you must use join.