Associations for Link Table

Need Help
#1

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.

#2

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-)

#3

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'
    ]);
}
#4

Which version ? 2.x or 3.x ?

#5

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.

#6

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 :frowning: I assume.

#7

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.

#8

Your association is :

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.

#9

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

#10

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

#11

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

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.

:frowning:

#12

Sorry but I don’t know what to do :frowning:
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

#14

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/entities.html#entities-mass-assignment