CakePHP find with two HABTM relationships

Question

This is a messy one because I’m trying to write a cake find that uses two HABTM relationships.

I have one table named projects and it has a HABTM relationship with the users table. It uses the intermediary table projects_users, for the HABTM relationship.

Then users used to just have a belongsTo relationship with the departments table. The users table had a foreign key called department_id for this. But now that relationship has changed to HABTM; and the foreign key, department_id, was dropped and the table department_users was created in its place.

So the old find statement, I’m working on changing, used to look like this:

$projectsUsers = $this->Project->ProjectsUser->find('all', array(
    'conditions' => array(
        'ProjectsUser.project_id' => 1,
        'User.department_id' => 25
    ),
    'recursive' => 0,
    'contain' => array(
        'User' => array(
            'fields' => array(
                'id',
                'department_id'
            )
        )
    )
));

Which must be changed since the users table no longer has a department_id.

So my first goal was to change the above to include the departments_users and departments. I tried commenting out the department_id fields and setting the recursive to 2. This is my cake find statement:

$projectsUsers = $this->Project->ProjectsUser->find('all', array(
        'conditions' => array(
                'ProjectsUser.project_id' => $projectId
        ),
        'recursive' => 2,
        'contain' => array(
                'User' => array(
                        'fields' => array(
                                'id'
                        )
                )
        )
));

This is what it made in the debugger:

Array
(
    [0] => Array
        (
            [ProjectsUser] => Array
                (
                    [id] => 9999
                    [user_id] => 999
                    [project_id] => 999
                    [status] => active
                )

            [User] => Array
                (
                    [id] => 999
                )

        )
    ...

I hoped setting recursive to 2 would include the departments and produce something like this in the debugger:

Array
(
    [0] => Array
        (
            [ProjectsUser] => Array
                (
                    [id] => 9999
                    [user_id] => 999
                    [project_id] => 999
                    [status] => active
                )

            [User] => Array
                (
                    [id] => 999
                )

            [Department] => Array
            (
                [0] => Array
                    (
                        [id] => 9
                        [DepartmentsUser] => Array
                            (
                                [id] => 99999
                                [department_id] => 9
                                [user_id] => 9999
                            )
                    )
            )
        )
    ...

But this is where I’m stuck. I've found a lot of posts online about displaying one HABTM relationship in cakePHP but not two. I've tried doing a contain within a contain; but that makes an error. I've even just done plain SQL within cake; but it produced an empty array with no other output. Any help would be appreciated. Thanks.

Dave Dave · Accepted Answer · 2015-01-15T17:42:49

You'll want to use JOINs (see details here) instead of contain. If you try that, and can't get it working exactly right, post your new attempt as a question and we can help from there.

PS - there are a number of issues w/ the code you posted, but since I don't think it's the right direction anyway, it's probably not necessary to go through in detail.

CakePHP find with two HABTM relationships

1 Answers