Cakephp HABTM with multiple joins needed

Question

I'm very new to Cakephp. I have all my model associations working fine but this one is stumping me and I haven't found anyone who has solved a similar problem. Here goes..

I have 4 tables:
people

+-----+-------------------+
| Id  | first_name        |
+-----+-------------------+
|   1 | jim               | 
|   2 | bob               | 
+-------------------------+

facility_project_people

+-----+---------------------+-----------+
| Id  | facility_project_id | person_id |
+-----+---------------------+-----------+
|   1 |          5          |     1     |
|   2 |          6          |     1     |
|   3 |          7          |     2     |
+---------------------------+-----------+

facility_projects

+-----+-------------+-----------+-----------+
| Id  | facility_id | project_id|  type_id  |
+-----+-------------+-----------+-----------+
|   1 |       1     |     1     |     1     |
|   2 |       2     |     3     |     2     |
|   3 |       2     |     5     |     3     |
+-------------------+-----------+-----------+

projects

+-----+-------------+
| Id  | name        |
+-----+-------------+
|   1 |  Audit      | 
|   2 |  Inventory  | 
|   3 |  Tax        |
|   4 |  Consulting |
+-------------------+

So I have people who can be involved in many projects per facility
I'm trying to make a model association to get from Person to the Projects they are involved with.

The MySQL query would be something like:
SELECT p.first_name, pr.name FROM people p left join facility_project_people fpp ON p.id = fpp.person_id left join facility_projects fp ON fpp.facility_project_id = fp.id left join projects pr ON fp.project_id = pr.id

Using a hasAndBelongsToMany Assocation, I understand how to get as far as the facility_projects table.

class Person extends AppModel {
    public $hasAndBelongsToMany = array(
    PersonFacilityProject' => array(
            'className' => 'Project',
            'joinTable' => 'facility_project_people',
            'foreignKey' => 'person_id',
            'associationForeignKey' => 'facility_project_id',
        ),
    }
}

Obviously this doesn't give me what I need

When viewing a person, I'd like to see related projects, but I'm stumped in how to get to the projects table. I'd like to be able to add another join table and foreign key to the association, but cakephp doesn't seem to allow this.

I appreciate any insight.

How did you try to retrieve the data and what is the result of your try? — bancer
The facility_projects table should be its own model (see hasMany through). That new model belongsTo Projects (and belongsTo Facility and belongsTo Type). Then you can have Person HABTM facilty_projects, which is already related to your projects. — AgRizzo

Compiler Compiler · Accepted Answer · 2016-10-18T14:36:52

You didn't state the version of cakephp you were using. However, you can use "contain" to do this (since your models are not all directly related) as shown below:

    $conditions = [
        'contain' => [
            'FacilityProjectPerson'=>[
                'FacilityProject'=>[
                    'Project'=>[
                        'fields'=>['Project.name']
                    ],
                ],
            ], 
        ],
    //  'conditions' => [//just in case you need conditions for selecting people
    //      'Person.id' => $this->id, 
    //  ],
        'fields'=>['Person.first_name']
    ];

    $this->Person->find('all',$conditions);

Cakephp HABTM with multiple joins needed

1 Answers