I am sorry if the title is a bit confusing, I just don't know how this is properly called. I have a table structure like this for my CakePHP project. users id, name, surname, userrecords userrecords id, user_id, records_id records id, description I understand that to access the userrecords middle table in my users view I have to do something like $user['userrecords']['id']; How can I neatly access description in records table through a users view ?

Access Foreign Tables CakePHP

Reputation: 627

You didn't specify whether you're using CakePHP 2.x or 3.x, so I provided solutions for both.

The relationship you are referring to is called a "Has And Belongs To Many" relationship. Since both of your model are associated to a linking table (userrecords), you can freely associate as many records to as many users as you want.

First, I would consider renaming your 'userrecords' table to 'users_records' to play nicely with CakePHP.

First, define your relationship within your Users model:

// Using CakePHP 2.x:
class User extends AppModel {
    public $actsAs = array('Containable'); // Instantiate Containable behavior
    public $hasAndBelongsToMany = array(
        'Record' =>
            array(
                'className' => 'Record',
                'joinTable' => 'users_records',
                'foreignKey' => 'user_id',
                'associationForeignKey' => 'record_id'
            )
    );
}

// Using CakePHP 3.x:
class UsersTable extends Table
{
    public function initialize (array $config)
    {
        $this->belongsToMany('Records', [
            'joinTable' => 'users_records' // Defines our linking table
        ]);
    }
}

Now, we must define our relationship within our Records model:

// Using CakePHP 2.x:
class Record extends AppModel {
    public $actsAs = array('Containable'); // Instantiate Containable behavior
    public $hasAndBelongsToMany = array(
        'User' =>
            array(
                'className' => 'User',
                'joinTable' => 'users_records',
                'foreignKey' => 'record_id',
                'associationForeignKey' => 'user_id'
            )
    );
}

// Using CakePHP 3.x:
class RecordsTable extends Table
{
    public function initialize (array $config) 
    {
        $this->belongsToMany('Users', [
            'joinTable' => 'users_records' // Defines our linking table
        ]);
    }
}

Now, we can access the associated records freely from each model using the ORM's contain method:

// Using CakePHP 2.x:
// Getting 'User' and associated 'Record' models in Controller:
$this->loadModel('User');
$this->User->find('all', array('contain' => 'Record'));

// Getting 'Record' and associated 'User' models in Controller:
$this->loadModel('Record');
$this->Record->find('all', array('contain' => 'User'));

// Using CakePHP 3.x:
// Getting 'User' and associated 'Record' models:
$users_table = TableRegistry::get('Users');
$users = $users_table->find()->contain('Records')->all();

// Getting 'Record' and associated 'User' models:
$records_table = TableRegistry::get('Records');
$records = $records_table->find()->contain('Users')->all();

Read the Cookbook, it will make your life a million times easier:

CakePHP 2.x Containable Behavior

CakePHP 2.x Has And Belongs To Many Relationship

CakePHP 3.x belongsToMany Relationship

CakePHP 3.x Retrieving Associated Data

CakePHP 3.x Eager Loading Associations

Upvotes: 2