How to connect 3 tables in cakephp? (relations)

Question

I have 3 tables:

user:
id;name

usercompany:
id;user_id, company_id

company:
id;company_name

How can I connect all 3 tables in cake? I want to be able to see which companies are connected to the user. The user can belong to more than one company.

In the user controller I just simply want to use $this->User->find("all") and it should show me all the users with the connected companies (including the company name).

How can I do this in cake? what should I use, hasAndBelongsToMany? Thanks for the insights.

did you take a look at the [manual](http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html)? — arilia, Jan 17 '14 at 10:19

score 2 · Accepted Answer · edited May 23 '17 at 12:06

I guess you did not look at the manual, everything you need to know is on this page.

This is a HABTM, hasAndBelongsToMany association. Your join table does not follow the CakePHP convention either, it should be named companies_users. See the page about conventions. If you don't follow conventions you have to declare everywhere what table, model and foreign keys you're using. If it's a legacy DB and you can't change them, fine. But if it's a new app you should really follow the frameworks conventions, it will make things more easy.

User <-> CompaniesUser <-> Company

class User extends AppModel {
    public $hasAndBelongsToMany = array(
        'Company' =>
            array(
                'className' => 'Company',
                'joinTable' => 'companies_users',
                'foreignKey' => 'user_id',
                'associationForeignKey' => 'company_id',
                'with' => 'CompaniesUser'
            )
    );
}

You'll have to define the assoc on the "other side", the company model as well.

To be able to fetch all companies for the user in a pagination query you'll have to define the joins manually. There are plenty of questions and answer on SO that explain how to do this, here is an one, another one.

score 1 · Answer 2 · answered Jan 17 '14 at 10:57

Follow the steps:

Step 1: Create 3 models named User.php, UserCompany.php and Company.php

Step 2: Inside User.php

var $hasMany = array(
'UserCompany' => array(
    'className'     => 'UserCompany',
    'foreignKey'    => 'user_id'
)
);

Step 3: Inside UserCompany.php

   var $hasMany = array(
    'Company' => array(
        'className'     => 'Company',
        'foreignKey'    => 'company_id'
    )
    );

Step 4: From controller $this->User->find("all")

How to connect 3 tables in cakephp? (relations)

2 Answers2