# Associations for Link Table

**URL:** <https://discourse.cakephp.org/t/associations-for-link-table/4133>\
**Category:** Need Help\
**Created:** [April 4, 2018, 5:52am UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133 "2018-04-04T05:52:27Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![cakenewbie](https://avatars.discourse-cdn.com/v4/letter/c/9de0a6/32.png) [@cakenewbie](https://discourse.cakephp.org/u/cakenewbie)\
**Post date:** [April 4, 2018, 5:52am UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/1 "2018-04-04T05:52:28Z")

</div>

Hi, I am have trouble when saving associations for a link tables. Perhaps my design is unnecessarily complex, but here goes.

I have 4 tables

teams  
members  
officialtypes  
seasons

all linked with a table

team\_officials

which contains

id, members\_id, teams\_id, officialtype\_id, season\_id

All these fields in one record represent a unique and valid entry, and there can be many combinations of all the ids in the table as long as only one combination of all 4 exist. eg

team\_id member\_id officialtype\_id season\_id  
1 100 2 4  
1 101 3 4  
1 89 2 3  
etc

All the fields must also exist in one of the other tables. ie member\_id must exist in members etc.

I have gone with this design because it is possible that a team can have an unlimited number of officialtypes which are user defined and not code defined. Theoretically a member can also have multiple officialtypes in the same team.

I currently have the associations set up with respect to the Teams model and I have been able to build an array of data and pass it to newEntity and save the data. It all saves correctly, however my problem starts when I run an update then the associated record in team\_officials is not detected as unique and another duplicate record is saved instead of it being updated.

I am also hoping that with the correct associations I can get all the related official\_types (and hence member\_ids) back in a query for a specific team.

Therefore, I am looking for the correct way to define all this.

I haven’t posted code because firstly I am after some advice on if I am going about this the best way and I therefore have something wrong in a model (Table) somewhere OR if the design should be better and therefore an easier set of associations.

If you suggest a better design would you please describe the associations I would require to make this work properly when saved from the team model.

Thanks in Advance,

Jason.

---

<div class="post-metadata">

**Author:** ![6120](https://avatars.discourse-cdn.com/v4/letter/6/278dde/32.png) [@6120](https://discourse.cakephp.org/u/6120)\
**Post date:** [April 4, 2018, 7:45am UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/2 "2018-04-04T07:45:37Z")

</div>

Well you should at least post the code for your main model (Teams) and probably the others so we could see how you did implement the associations.  
In your post, I read : “id, members\_id, teams\_id, officialtype\_id, season\_id” but it should be :  
id, member\_id, team\_id, officialtype\_id, season\_id (no “s” at member-s- or team-s-)

---

<div class="post-metadata">

**Author:** ![cakenewbie](https://avatars.discourse-cdn.com/v4/letter/c/9de0a6/32.png) [@cakenewbie](https://discourse.cakephp.org/u/cakenewbie)\
**Post date:** [April 4, 2018, 8:32am UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/3 "2018-04-04T08:32:47Z")

</div>

Thanks 6120. Yeah they were typos with the extra s’s.

Here is the Teams and TeamsOfficials Models.

Teams Model (Table):

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

    $this->table('teams');
    $this->displayField('name');
    $this->primaryKey('id');

    $this->addBehavior('Timestamp');
    $this->addBehavior('Csv');        

    $this->hasMany('TeamsOfficials', [
        'foreignKey' => 'team_id'
    ]);

    $this->belongsToMany('Members', [
        'foreignKey' => 'team_id',
        'targetForeignKey' => 'member_id',
        'joinTable' => 'members_teams'
    ]);

    $this->hasMany('TeamsSeasons', [
        'foreignKey' => 'team_id'
    ]);        
}

```

TeamsOfficials Model (Table):

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

    $this->table('teams_officials');
    $this->displayField('id');
    $this->primaryKey('id');

    $this->addBehavior('Timestamp');

    $this->belongsTo('Seasons', [
        'foreignKey' => 'season_id',
        'joinType' => 'INNER'
    ]);
    $this->belongsTo('Teams', [
        'foreignKey' => 'team_id',
        'joinType' => 'INNER'
    ]);
    $this->belongsTo('Members', [
        'foreignKey' => 'member_id',
        'joinType' => 'INNER'
    ]);
    $this->belongsTo('Officialtypes', [
        'foreignKey' => 'officialtype_id',
        'joinType' => 'INNER'
    ]);
}
```

---

<div class="post-metadata">

**Author:** ![6120](https://avatars.discourse-cdn.com/v4/letter/6/278dde/32.png) [@6120](https://discourse.cakephp.org/u/6120)\
**Post date:** [April 4, 2018, 12:02pm UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/4 "2018-04-04T12:02:14Z")

</div>

Which version ? 2.x or 3.x ?

---

<div class="post-metadata">

**Author:** ![donnidani](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/donnidani/32/744_2.png) [@donnidani](https://discourse.cakephp.org/u/donnidani)\
**Post date:** [April 4, 2018, 8:29pm UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/5 "2018-04-04T20:29:43Z")

</div>

try to debug($this-request-\>data()), than examine returned entity object. and try to find the linked entity, to make sure it exist in entity object/array.  
in your case the entity name that should appear is (if follow cakephp convention):

- team(s) =\> (object)
- member(s) =\> (object)
- official\_type(s) =\> (object)
- season(s) =\> (object)

if it not there try to see your Entity/Team.php  
and add ‘teams’, inside $\_accessible if it not already in a list.

---

<div class="post-metadata">

**Author:** ![cakenewbie](https://avatars.discourse-cdn.com/v4/letter/c/9de0a6/32.png) [@cakenewbie](https://discourse.cakephp.org/u/cakenewbie)\
**Post date:** [April 5, 2018, 1:42am UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/6 "2018-04-05T01:42:22Z")

</div>

Its is Cake v3.3 at present. Want to solve this issue in this version before upgrading if possible as I have just found it is happening in a few other models with similar design so I it must be me ☹ I assume.

---

<div class="post-metadata">

**Author:** ![cakenewbie](https://avatars.discourse-cdn.com/v4/letter/c/9de0a6/32.png) [@cakenewbie](https://discourse.cakephp.org/u/cakenewbie)\
**Post date:** [April 5, 2018, 1:54am UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/7 "2018-04-05T01:54:05Z")

</div>

Hi donnidani,

Here is my array before patchEntity on Team Model:

array:8 [  
“name” =\> “Team1”  
“team\_group\_id” =\> 37  
“club\_id” =\> “2”  
“coach\_id” =\> 392  
“assistantcoach\_id” =\> false  
“manager\_id” =\> false  
“teams\_seasons” =\> array:1 [  
0 =\> array:2 [  
“season\_id” =\> “4”  
“team\_group\_id” =\> 37  
]  
]  
“teams\_officials” =\> array:1 [  
0 =\> array:3 [  
“season\_id” =\> “4”  
“member\_id” =\> 392  
“officialtype\_id” =\> 1  
]  
]  
]

Here is the result after patchentity.

App\Model\Entity\Team {#819  
+“id”: 81  
+“name”: “Team1”  
+“searchname”: null  
+“age\_group”: null  
+“team\_group\_id”: 37  
+“competition\_id”: null  
+“club\_id”: 2  
+“created”: Cake\I18n\FrozenTime {#813  
+“time”: “2018-04-03T12:39:53+10:00”  
+“timezone”: “Australia/Brisbane”  
+“fixedNowTime”: false  
}  
+“modified”: Cake\I18n\Time {#160  
+“time”: “2018-04-05T11:45:40+10:00”  
+“timezone”: “Australia/Brisbane”  
+“fixedNowTime”: false  
}  
+“teams\_seasons”: array:1 [  
0 =\> App\Model\Entity\TeamsSeason {#652  
+“season\_id”: 4  
+“team\_group\_id”: 37  
+“team\_id”: 81  
+“created”: Cake\I18n\Time {#630  
+“time”: “2018-04-05T11:45:40+10:00”  
+“timezone”: “Australia/Brisbane”  
+“fixedNowTime”: false  
}  
+“modified”: Cake\I18n\Time {#622  
+“time”: “2018-04-05T11:45:40+10:00”  
+“timezone”: “Australia/Brisbane”  
+“fixedNowTime”: false  
}  
+“id”: 80  
+"[new]": false  
+"[accessible]": array:1 [  
“_" =\> true  
]  
+"[dirty]": []  
+"[original]": []  
+"[virtual]": []  
+"[errors]": []  
+"[invalid]": []  
+"[repository]": “TeamsSeasons”  
}  
]  
+“teams\_officials”: array:1 [  
0 =\> App\Model\Entity\TeamsOfficial {#657  
+“season\_id”: 4  
+“member\_id”: 392  
+“officialtype\_id”: 1  
+“team\_id”: 81  
+“created”: Cake\I18n\Time {#647  
+“time”: “2018-04-05T11:45:40+10:00”  
+“timezone”: “Australia/Brisbane”  
+“fixedNowTime”: false  
}  
+“modified”: Cake\I18n\Time {#619  
+“time”: “2018-04-05T11:45:40+10:00”  
+“timezone”: “Australia/Brisbane”  
+“fixedNowTime”: false  
}  
+“id”: 6  
+"[new]": false  
+"[accessible]": array:1 [  
"_” =\> true  
]  
+"[dirty]": []  
+"[original]": []  
+"[virtual]": []  
+"[errors]": []  
+"[invalid]": []  
+"[repository]": “TeamsOfficials”  
}  
]  
+"[new]": false  
+"[accessible]": array:1 [  
“\*” =\> true  
]  
+"[dirty]": []  
+"[original]": []  
+"[virtual]": []  
+"[errors]": []  
+"[invalid]": []  
+"[repository]": “Teams”  
}

When I then save this record it creates new record in team\_officials every time. and now I have found is also doing the same in teams\_seasons with both of these tables having a hasMany relationship with Team model. and both tables requiring more complex ‘isUnique’ Rules as described in my original post.

Thanks for your help.

---

<div class="post-metadata">

**Author:** ![6120](https://avatars.discourse-cdn.com/v4/letter/6/278dde/32.png) [@6120](https://discourse.cakephp.org/u/6120)\
**Post date:** [April 5, 2018, 3:09am UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/8 "2018-04-05T03:09:54Z")

</div>

Your association is :

> [@cakenewbie](#):
>
> $this-\>belongsToMany(‘Members’, [  
> ‘foreignKey’ =\> ‘team\_id’,  
> ‘targetForeignKey’ =\> ‘member\_id’,  
> ‘joinTable’ =\> ‘members\_teams’  
> ]);

If you respect cakephp conventions, you should have (in MembersTable.php) :  
$this-\>belongsToMany(‘Teams’, [  
‘through’ =\> ‘MembersTeams’,  
]);  
and in TeamsTable.php :  
$this-\>belongsToMany(‘Members’, [  
‘through’ =\> ‘MembersTeams’,  
]);

You don not need the extra stuff.

---

<div class="post-metadata">

**Author:** ![cakenewbie](https://avatars.discourse-cdn.com/v4/letter/c/9de0a6/32.png) [@cakenewbie](https://discourse.cakephp.org/u/cakenewbie)\
**Post date:** [April 5, 2018, 6:20am UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/9 "2018-04-05T06:20:44Z")

</div>

Thanks 6120 but I think I have confused you because I am actually having trouble with the teams\_officials records. As you pointed out I will also try the members\_teams association change as well.

Can you see why my teams\_officials keeps creating duplicate records instead of recognizing the existing records. Is it something in my validation rules or something else in the teams\_officials Model???

As I mentioned before this is also happening to teams\_seasons records so I clearly have the some problem here which I still think is related to how cake determines in a record ‘isUnique’ when it has complex rules (ie my case 4 columns combine to create a unique record.

Thanks

---

<div class="post-metadata">

**Author:** ![6120](https://avatars.discourse-cdn.com/v4/letter/6/278dde/32.png) [@6120](https://discourse.cakephp.org/u/6120)\
**Post date:** [April 5, 2018, 6:52am UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/10 "2018-04-05T06:52:31Z")

</div>

are you sure you have an auto-increment “id” column in each table ?

---

<div class="post-metadata">

**Author:** ![cakenewbie](https://avatars.discourse-cdn.com/v4/letter/c/9de0a6/32.png) [@cakenewbie](https://discourse.cakephp.org/u/cakenewbie)\
**Post date:** [April 5, 2018, 7:44am UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/11 "2018-04-05T07:44:34Z")

</div>

absolutely, I checked in my debugging and just again because I didn’t want to look silly 🙂

Although owning up, the teams\_officials table didn’t have a unique id but the teams\_seasons table does. even though both are duplicating records.

☹

---

<div class="post-metadata">

**Author:** ![6120](https://avatars.discourse-cdn.com/v4/letter/6/278dde/32.png) [@6120](https://discourse.cakephp.org/u/6120)\
**Post date:** [April 5, 2018, 12:05pm UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/12 "2018-04-05T12:05:28Z")

</div>

Sorry but I don’t know what to do ☹  
I happened to have a similar problem when I did not collect the Ids correctly.  
Maybe you could try debug just before saving so you could see what you are going to save.  
And check the logs-\>error.log file

---

<div class="post-metadata">

**Author:** ![donnidani](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/donnidani/32/744_2.png) [@donnidani](https://discourse.cakephp.org/u/donnidani)\
**Post date:** [April 12, 2018, 3:51pm UTC](https://discourse.cakephp.org/t/associations-for-link-table/4133/14 "2018-04-12T15:51:04Z")

</div>

sorry for my late reply.

this problem often occurs when you try to update record recursively, when you try to update the data along with related data at once. the reason why this is happened because the “id” is not present in your array data, so when you try to update the record it will duplicate or saved as new record since cakephp doesn’t have the target “id” so cakephp assumed to create a new records, IMHO.

```
you might try this.
$this->patchEntity($newEntity, $data, [
    'associated' => [
        'YourAssociatedTable1' => [
            'accessibleFields' => ['id' => true]
        ],
        'YourAssociatedTable2' => [
            'accessibleFields' => ['id' => true]
        ]
    ]
]);

```

some link that might worth to read  
[https://book.cakephp.org/3.0/en/orm/saving-data.html#changing-accessible-fields](https://book.cakephp.org/3.0/en/orm/saving-data.html#changing-accessible-fields)  
[https://book.cakephp.org/3.0/en/orm/entities.html#entities-mass-assignment](https://book.cakephp.org/3.0/en/orm/entities.html#entities-mass-assignment)