CODEWITHSUNDEEP
phpmysqlcakephp
maafk
maafk

Reputation: 6876

Cakephp HABTM with multiple joins needed

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.

Upvotes: 0

Views: 162

Answers (1)

Compiler
Compiler

Reputation: 115

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);

Upvotes: 1

Related Questions