Cakephp 3 - Additional ID-field in belongsToMany association's through table
<p>I have been working on a CakePHP 3 project that has parts on it that are a bit more complex and it seems that the framework's official documentation does not give me the answer I am looking for. A following thing is needed to be done:</p>

<p>Users can belong to multiple Projects. In each Project each User that belongs to the current Project can access into different sections of the application, this is handled by a table called Resources, which stores the resource ids. </p>

<p>So in short, User A can have access to different amount of Resources in Project A and Project B.</p>

<p>I have setup following associations:</p>

<ol>
<li>Users belongsToMany Projects through UsersProjects</li>
<li>Users belongsToMany Resources through UsersResources</li>
</ol>

<p>In users_resources -table I have the following fields:</p>

<p>user_id | resource_id | project_id</p>

<p>project_id -field is added to the table to make the user's resources project-specific.</p>

<p><strong>Association configuration is as follows:</strong></p>

<p>UsersTable:</p>

<pre><code>        $this-&gt;belongsToMany('Resources', [
            'foreignKey' =&gt; 'user_id',
            'targetForeignKey' =&gt; 'resource_id',
            'joinTable' =&gt; 'users_resources',
            'through' =&gt; 'UsersResources'
        ]);
</code></pre>

<p>ResourcesTable:</p>

<pre><code>        $this-&gt;belongsToMany('Users', [
            'foreignKey' =&gt; 'resource_id',
            'targetForeignKey' =&gt; 'user_id',
            'joinTable' =&gt; 'users_resources',
            'through' =&gt; 'UsersResources'
        ]);
</code></pre>

<p>UsersResourcesTable:</p>

<pre><code>        $this-&gt;belongsTo('Users', [
            'foreignKey' =&gt; 'user_id',
            'joinType' =&gt; 'INNER'
        ]);

        $this-&gt;belongsTo('Resources', [
            'foreignKey' =&gt; 'resource_id',
            'joinType' =&gt; 'INNER'
        ]);

        $this-&gt;belongsTo('Projects', [
            'foreignKey' =&gt; 'project_id',
            'joinType' =&gt; 'INNER'
        ]);
</code></pre>

<p>There is also a buildRules-function implemented in the UsersResourcesTable-class to help with the data integrity:</p>

<pre><code>  public function buildRules(RulesChecker $rules)
    {
        $rules-&gt;add($rules-&gt;existsIn(['user_id'], 'Users'));
        $rules-&gt;add($rules-&gt;existsIn(['resource_id'], 'Resources'));
        $rules-&gt;add($rules-&gt;existsIn(['project_id'], 'Projects'));
        $rules-&gt;add($rules-&gt;isUnique(['user_id', 'resource_id', 'project_id']));
        return $rules;
    }
</code></pre>

<p><strong>The data is saved like this:</strong></p>

<pre><code>        $usersTable = TableRegistry::get('Users');
        $user = $usersTable-&gt;get(129);
        $data = [
            'resources' =&gt; [
                [
                    'id' =&gt; 48,
                    '_joinData' =&gt; [
                        'project_id' =&gt; 2
                    ]
                ]
            ]
        ];
        $user = $usersTable-&gt;patchEntity($user, $data, ['associated' =&gt; ['Resources']]);
        if ($usersTable-&gt;save($user)) {
            $this-&gt;out('success');
        } else {
            $this-&gt;out('fail');
        }
</code></pre>

<p><strong>The entity data after patchEntity:</strong></p>

<pre><code>   {
        "id": 129,
        "email": "john.doe@example.com",
        "created": null,
        "modified": "2016-02-05T20:50:06+0000",
        "firstname": "John",
        "lastname": "Doe",
        "type": 1,
        "resources": [
            {
                "id": 48,
                "type": "r",
                "_joinData": {
                    "project_id": 2
                }
            }
        ]
    }
</code></pre>

<p>When the data is saved with project id 2, the save works as expected. When I save the same data but I change the project id to 1 (<em>user_id</em> and <em>resource_id</em> will remain the same), the previous row with project id 2 is deleted from the table. The desired behaviour would obviously be that the older record would remain in the table. I noticed though that adding a saveStrategy-option with value 'append' to UsersTable's Resources-association definition will make the save go through correctly (the row with project_id 2 will not be deleted). But when I try to save an existing record while append-strategy is used, the save will fail (the save result is false).</p>

<p>So if I use replace-strategy, previous data gets deleted and if I use append-strategy, the update of an existing row will result to an error in cake's perspective. I think the error is thrown during rules checking due to the unique-rule.</p>

<p>I think the replace strategy would be the way to go but it seems that the project_id -field value gets ignored and it leads to the situation that previous data (with same user_id and resource_id) will be deleted whenever a new row is added with a different project_id.</p>

<p>So my question is:</p>

<p>Is it possible to add a third id-field to belongsToMany-association's trough-table so that the extra field would also be considered as an id-field so the framework would know when to update or insert records to the join-table? </p>

<p>The idea is that all the three fields in the table would be unique. Now it seems that the framework discards the extra field so inserts and updates do not work as expected. Or is there a design flaw in the database structure that is needed to be fixed? </p>

<p>I would prefer sticking with the CakePHP-conventions but it seems this scenario is quite hard to tackle. Any guidance is appreciated!</p>