CODEWITHSUNDEEP
cakephpjoinmodelcontainable
user1385637
user1385637

Reputation: 23

CakePHP: ContainableBehavior Query Structure/Joins

My Application's Data Model

Find call

$groups = $this->Group->find('all', array( 'contain' => array(
    'User' => array(
        'Punch' => array(
            'conditions' => array(
                'Punch.payperiod_id' => null
            )
        )
    )

)));

Query Debug

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:

Query I'd Prefer

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.

Upvotes: 2

Views: 1061

Answers (2)

RichardAtHome
RichardAtHome

Reputation: 4313

You can join manually:

$groups = $this->Group->find('all', array(
    'fields'=>array(
        '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'
    ),
    'conditions'=>array(
        'Punch.payperiod_id IS NULL'
    ),
    'joins'=>array
        array(
           'table'=>'users',
           'alias'=>'User',
           'conditions'=>array(
               'Group.id = User.group_id'
           )
        ),
        array(
            'table'=>'punches',
            'alias'=>'Punch',
            'conditions'=>array(
                'User.id = Punch.user_id'
            )
        )
    )
));

Upvotes: 2

dogmatic69
dogmatic69

Reputation: 7585

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.

https://github.com/rafaelbandeira3/linkable

Upvotes: 2

Related Questions