Updating belongsToMany association data

Question

I am setting up a form that updates person and the associated person_to_role tables, person_to_role is a intermediate table that links person to role in a n..n relationship. role has a predefined list of roles, that shouldn't be modified from a person's scope.

I only need to update role_id and description in the person_to_role table and add/remove records.

SQL

--person
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| ID          | int(11)     | NO   | PRI | NULL    | auto_increment |
| NICK_NAME   | varchar(16) | YES  |     | NULL    |                |
| FIRST_NAME  | varchar(24) | NO   |     | NULL    |                |
| MIDDLE_NAME | varchar(8)  | YES  |     | NULL    |                |
| LAST_NAME   | varchar(24) | NO   |     | NULL    |                |
| BIRTH_DATE  | date        | NO   |     | NULL    |                |
| GENDER      | varchar(8)  | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

-- role
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| ID          | int(11)      | NO   | PRI | NULL    | auto_increment |
| NAME        | varchar(24)  | NO   |     | NULL    |                |
| DESCRIPTION | varchar(100) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

--person_to_role
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| ID          | int(11)      | NO   | PRI | NULL    | auto_increment |
| PERSON_ID   | int(11)      | NO   | MUL | NULL    |                |
| ROLE_ID     | int(11)      | NO   | MUL | NULL    |                |
| DESCRIPTION | varchar(100) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

PersonTable Model

public function initialize(array $config) { parent::initialize($config);

    $this->setTable('person');
    $this->setDisplayField('ID');
    $this->setPrimaryKey('ID');

    // define relations
    $this->belongsToMany('Role', [
        'joinTable'=> 'person_to_role',
        'foreignKey' => 'PERSON_ID'
    ]);        
}

Person controller -> edit()

public function edit($id = null)
{
    $person = $this->Person->get($id, [
        'contain' => ['role']
    ]);

    $this->set('roles', $this->Person->Role->find('list'));

    if ($this->request->is(['patch', 'post', 'put'])) {            
        $person = $this->Person->patchEntity($person, $this->request->getData());
        if ($this->Person->save($person)) {
            $this->Flash->success(__('The person has been saved.'));

            return $this->redirect(['action' => 'index']);
        }
        $errors = print_r($person->errors(),1);
        $this->Flash->error(__('The person could not be saved. Please, try again.<br><pre>'. $errors .'</pre>'),['escape'=> false]);
    }

    $this->set(compact('person'));
    $this->set('_serialize', ['person']);
}

edit.ctp form fields

echo $this->Form->control('NICK_NAME');
echo $this->Form->control('FIRST_NAME');
echo $this->Form->control('MIDDLE_NAME');
echo $this->Form->control('LAST_NAME');
echo $this->Form->control('BIRTH_DATE');
echo $this->Form->control('GENDER',['type'=>'select','options'=>[''=> '- 
Please Select -', 'Male'=>'Male','Female'=>'Female']]);

Assigned roles

...
<?php foreach ($person->role as $k=>$role) { ?>
    <tr><td><?php 

   echo $this->Form->input("role.$k._joinData.ID",['type'=>'hidden','value'=>$person->role[$k]['_joinData']['ID']]);

    echo $this->Form->input("role.$k._joinData.ROLE_ID",['value'=>$role-
>ID,'options'=>$roles,'templates'=>['formGroup' =>'{{input}}']]) 

            ?></td><td>
<?php echo $this->Form->input("role.$k._joinData.DESCRIPTION",    ['value'=>$person->role[$k]['_joinData']['DESCRIPTION'],'templates'=>
['formGroup' =>'{{input}}']]);  ?></td></tr>
        <?php } ?>
...

This gives me the following POST data:

'NICK_NAME' => 'Johny',
    'FIRST_NAME' => 'John',
    'MIDDLE_NAME' => 'J.',
    'LAST_NAME' => 'Smith',
    'BIRTH_DATE' => '1961-01-01',
    'GENDER' => 'Male',
    'role' => [
        (int) 0 => [
            '_joinData' => [
                'ID' => '1',
                'ROLE_ID' => '5',
                'DESCRIPTION' => 'person role description'
            ]
        ]
    ]
]

But the update fails with following errors:

The person could not be saved. Please, try again.

  Array
  (
  [role] => Array
  (
    [0] => Array
    (
      [NAME] => Array
      (
        [_required] => This field is required
      )

      [_joinData] => Array
      (
        [PERSON_ID] => Array
        (
          [_required] => This field is required
        )
      )
    )
  )
)

It asks to provide person_to_role.person_id which is the foreignKey (it should know current person ID), and wants a value for role.name. Did I set my association wrong? Any help is appreciated.

UPDATE 2017-08-20

Still no go, tried all possible variations from the docs and other Internet resources. Currently I am able to pass through validation to the save action, but an INSERT query is generated instead of UPDATE and errors out on unique constraint violation.

I have person.ID and person_to_role.ID accessible:

protected $_accessible = [
    '*' => true,
    'ID' => true
];

My POST data looks like this:

[
  'NICK_NAME' => '',
  'FIRST_NAME' => 'test',
  'MIDDLE_NAME' => '',
  'LAST_NAME' => 'user',
  'BIRTH_DATE' => '1996-10-01',
  'GENDER' => 'Male',
  'role' => [
    (int) 0 => [
      '_joinData' => [
        'ID' => '153',
        'DESCRIPTION' => 'test edited text'
      ],
      'ID' => '2'
    ]
  ]
]

I tried both, with and without person_to_role record ID in _joinData, same result:

INSERT INTO person_to_role (PERSON_ID, ROLE_ID, DESCRIPTION) 
VALUES (129, 2, 'test edited text')

Answer 1

While trying different methods, including using 'through' association, I had the following lines added to my PersonToRoleTable.phpmodel:

$this->belongsTo('Person');
$this->belongsTo('Role');

After commenting these out, everything worked as expected, I am able to save updates as well as add new and delete existing records in the join table.