Linking same table with two foreign keys in cakephp 3

Question

I have a table match_schedules which stores matches between two teams. There is table teams to store team information.

Columns of match_schedules are

+-----+---------+---------+-------+-------+
| id  | team_a  | team_b  | date  | venue |
+-----+---------+---------+-------+-------+

Since I have two columns team_a and team_b referencing teams table, I can't use team_id as foreign key in both columns.

Now, I want to associate these two columns with teams table so that I can easily retrieve associated data like

$matches = $this->MatchSchedules->find('all', [
  'contain' => [
      'Teams'
  ]
]);

I have tried this In TeamsTable.php

$this->belongsTo('MatchSchedules', [
    'foreignKey' => 'team_a',
    'joinType' => 'INNER'
]);
$this->belongsTo('MatchSchedules', [
    'foreignKey' => 'team_b',
    'joinType' => 'INNER'
]);

In MatchSchedulesTable.php

$this->hasMany('Teams', [
    'foreignKey' => 'team_a'
]);
$this->hasMany('Teams', [
    'foreignKey' => 'team_b'
]);

But this is not working.

Change 'MatchSchedules' to 'MatchSchedulesA' and 'MatchSchedulesB', 'Teams' to 'TeamsA'and 'TeamsB' — Salines, Aug 22 '16 at 19:03
this gives `MatchSchedules is not associated with Teams` on `$matches = $this->MatchSchedules->find('all', [ 'contain' => 'Teams' ]);` — Anuj TBE, Aug 22 '16 at 19:09
this gives `Base table or view not found: 1146 Table 'teams_a' doesn't exist`. — Anuj TBE, Aug 22 '16 at 19:12
this gives `Column not found: 1054 Unknown column 'TeamsA.team_a' in 'where clause' ` **SQL QUERY** `SELECT TeamsA.id AS `TeamsA__id`, TeamsA.title AS `TeamsA__title`, TeamsA.logo AS `TeamsA__logo`, FROM teams TeamsA WHERE TeamsA.team_a in (:c0,:c1)` — Anuj TBE, Aug 22 '16 at 19:20

Salines · Accepted Answer · 2016-08-22T19:52:22.433

You did not properly set up associations

TeamsTable.php

$this->hasMany('MatchSchedulesA', [
    'foreignKey' => 'team_a',
    'className' => 'MatchSchedules'
]);
$this->hasMany('MatchSchedulesB', [
    'foreignKey' => 'team_b',
    'className' => 'MatchSchedules'
]);

In MatchSchedulesTable.php

$this->belongsTo('TeamsA', [
    'foreignKey' => 'team_a',
    'joinType' => 'INNER',
    'className' => 'Teams'
]);
$this->belongsTo('TeamsB', [
    'foreignKey' => 'team_b',
    'joinType' => 'INNER',
    'className' => 'Teams'
]);

and

$matches = $this->MatchSchedules->find('all', [
  'contain' => [
      'TeamsA',
      'TeamsB
  ]
]);

Is nice to be if you rename:

MatchSchedulesA to HomeMatches 
MatchSchedulesB to GuestMatches 
team_a to home_team 
team_b to guest_team 
TeamsA to HomeTeams 
TeamsB to GuestTeams

Linking same table with two foreign keys in cakephp 3

1 Answers1

Linked