Associations - Double foreign key

Need Help
1

Hi,

is there a smooth way to implement an association where it will match either of two foreign keys?

My problem is best described by using an example:

In a football match (MatchesTable), you have two teams (TeamsTable) competing, so you’d need to have two foreign keys (home_id and away_id), which would each contain a Team entity. A match would normally have one home team and one away team, which gives the foreign key name as such.

You’d need to create two belongsTo associations in the MatchesTable and a hasMany association in the TeamsTable.

What I need:
Whenever I call the method $this->Teams->get($id, ['contain' => 'Matches']), it should contain all records that either match the home_id or the away_id foreign keys.

That way I can get all the matches a team has played, not only its home games.

What happens:
Right now, I can only set one foreign key (for instance home_id) and then it loads only the team’s home matches.

What I’ve tried:
I have added an hasMany association to Matches with two foreign keys, but then I also need to add bindingKeys.

// MatchesTable
$this->hasMany('Matches', [
    'foreignKey' => ['home_id', 'away_id'],
    'bindingKey' => ['id', 'id'],
]);

// TeamsController
public function view(string $id = null): void
{
    $team = $this->Teams->get(1, ['contain' => 'Matches']);

    $this->set(compact('team'));
}

The resulting query gives me this condition:

WHERE 
  (
    Matches.home_id, Matches.away_id
  ) IN (
    (1, 1)
  )

This query will never be true, as a team won’t play against itself.

I tried adding a custom finder:

// TeamsTable
$this->hasMany('Matches', [
    'foreignKey' => 'home_id',
    'finder' => 'relative',
]);

// MatchesTable
public function findRelative(Query $query, array $options): Query
{
    $or = $query->newExpr()->or([
        'home_id' => 1,
        'away_id' => 1,
    ]);

    return $query->where($or);
}

Which results in the following query:

WHERE 
  (
    (
      home_id = 1 
      OR away_id = 1
    ) 
    AND Matches.home_id in (1)
  )

As you might see, the Cake built query appends the foreignKey condition to my custom finder method.

My current workaround

//MatchesTable
public function findRelative(Query $query, array $options): Query
{
    if (!isset($options['id'])) {
       throw new MissingArgumentException('Missing `id` when find relative matches.');
    }
    $or = $query->newExpr()->or([
        'team_id' => $options['id'],
        'opponent_id' => $options['id'],
    ]);

    return $query->where($or);
}

// TeamsController
public function view(string $id = null): void
{
    $team = $this->Teams->get($id);
    $matches = $this->Teams->Matches->find('relative', compact('id'));
    $team->matches = $matches->toArray();

    $this->set(compact('team'));
}
2

Personally, I would try this:

  • Teams hasMany(‘HomeGames’)
  • Teams hasMany(‘AwayGames’)

From here it would be possible to use the mapper/reducer feature of queries (or write your own merging routine) to place all the records found on the two associations into a matches property of the Team entity.

I would use this approach because I prefer to have a small number of queries that I use widely through my application. And I like them to deliver entities with a rich context of contained data so they can be used in most situations. Because of this, my entities tend to have quite a few methods to ease access to the contained data.

For example, an entity built with the three properties described here might have

/**
 * @param int $venue_id
 */
public function matchesAt($venue_id) : array {
   //implementation here
}

/**
 * @param int $team_id
 * @param string $filter all, home, or away
 */
public function hasPlayed($team_id, $filter = 'all') : bool {
   //implementation here
}

public function hasPlayedAt($venue_id) : bool {
   //implementation here
}
3

Your current workaround is the answer for me.

I think the only other solution would be denormalize the database (like having a matches_teams with just match_id and team_id) but you would need to have checks to make sure the database is sync (ie each match having two matchs_teams rows)

4

Or

  • Teams belongsToMany Matches
  • Matches belongsToMany Teams

MatchesTeams has at least these three fields:

  • match_id
  • team_id
  • home_game (boolean)

I think because you have extra data on the join table you would need to use the ‘through’ option on your associations. But this could eliminated the ‘double key’ problem entirely.

5

Thanks for your input, @raul338 and @dreamingmind

I forgot to mention that I had tried a belongsToMany association as you both suggested. Yes, that will work, no doubt, but I had hoped there was a simpler way. A belongsToMany association is perfect when there are numerous combinations between the tables (take the tags example from the CakePHP book), but in my case there is always exactly 2 teams, so I thought a belongsToMany association was unneccesary work.

I guess I’ll have to figure out which is the lesser of the evils. Appreciate your time, guys.

1 Like