CakePHP join table

Question

I have a join model UserCommission which joins User model and Commission model. User model belongs to Organisation model. And Organisation model has many User model.

In my model UserCommission, with $this->find('all') I get this array :

array(
    (int) 0 => array(
        'UserCommission' => array(
            'id' => '1247',
            'user_id' => '872',
            'commission_id' => '13'
        ),
        'User' => array(
            'id' => '872',
            'organisation_id' => '6', // I need data related to Organisation model
        ),
        'Commission' => array(
            'id' => '13',
            'nom' => 'SALARIES CE'
        )
    ),
    ...
)

How can I get data from Organisation model so that the returned array is like so :

array(
    (int) 0 => array(
        'UserCommission' => array(
            'id' => '1247',
            'user_id' => '872',
            'commission_id' => '13'
        ),
        'User' => array(
            'id' => '872',
            'organisation_id' => '6',
        ),
        'Commission' => array(
            'id' => '13',
            'nom' => 'SALARIES CE'
        ),
        'Organisation' => array(
            'id' => '6',
            'nom' => 'The Organisation #6'
        )
    ),
    ...
)

Model associations :
User belongsTo Organisation
Organisation hasMany User
User hasAndBelongsToMany Commission (with UserCommission)
Commission hasAndBelongsToMany User (with UserCommission)
UserCommission belongsTo User
UserCommission belongsTo Commission

Model tables :
User (id; organisation_id; username; ...)
Commission (id; commission_name; ...)
Organisation (id; organisation_name; ...)
UserCommission (id; user_id; commission_id; ...)

It doesn't help me. I believe I have to use the JOINS option to join UserCommission, User, and Organisation, but I don't know how to do. — Patudek
You generally don't need to use JOINs as long as your model relations are properly defined. Share the User model's $hasAndBelongsToMany, $hasMany and $belongsTo configurations in your question. — AgRizzo

LuisClemente LuisClemente · Accepted Answer · 2014-09-18T21:16:07

If relations are properly configured you can use:

$User->recursive=2;
$User->find('all')

The problem with this workaround is that you will get Organization info nested within user array, not at the same level and that is really bad resources wise. It really depends on how many organizations are in your database but sometimes is better to just make a second query to retrieve needed info instead of make huge queries just to have it on one go.

CakePHP join table

1 Answers