$groups = $this->Group->find('all', array( 'contain' => array(
'User' => array(
'Punch' => array(
'conditions' => array(
'Punch.payperiod_id' => null
)
)
)
)));
SELECT `Group`.`id`, `Group`.`name`
FROM `pclock`.`groups` AS `Group`
WHERE 1 = 1 ORDER BY `name` ASC
SELECT `User`.`id`, `User`.`name`, `User`.`group_id`
FROM `pclock`.`users` AS `User`
WHERE `User`.`group_id` IN (4, 3, 5, 2, 1)
SELECT `Punch`.`id`, `Punch`.`user_id`, `Punch`.`time_in`, `Punch`.`time_out`, `Punch`.`payperiod_id`
FROM `pclock`.`punches` AS `Punch`
WHERE `Punch`.`payperiod_id` IS NULL AND `Punch`.`user_id` IN (1, 2, 3, 4, 5)
Once my application scales to hundreds of Users each with thousands of Punches, these queries can become very inefficient. I would expect Containable to perform the following query:
SELECT
Group.id, Group.name,
User.id, User.name, User.group_id,
Punch.id, Punch.user_id, Punch.time_in, Punch.time_out, Punch.payperiod_id
FROM groups AS Group
LEFT JOIN users AS User
ON (Group.id = User.group_id)
LEFT JOIN punches AS Punch
ON (User.id = Punch.user_id)
WHERE Punch.payperiod_id IS NULL
Is there any way to optimize this? The join attribute in the options array is seemingly ignored, and doing joins manually without Containable returns results that aren't hierarchical.
This is how containable works. You can use the join params in the find or look up the linkable behavior which does the joins for you.