CakePHP Model Relationship with Multiple Foreign Keys

Question

In my CakePHP app I have models for Matches and Teams. Each Match has a home_team_id and an away_team_id, both of which reference a different Team.

In my team.php file, I am able to form the relationship for a Team's home matches:

var $hasMany = array(
  'HomeMatch' => array('className' => 'Match', 'foreignKey' => 'home_team_id'),
  'AwayMatch' => array('className' => 'Match', 'foreignKey' => 'away_team_id')
);

My problem is that I cannot automatically retrieve a Team's home and away Matches in a single array. That is, the retrieved Matches are returned in separate HomeMatch and AwayMatch arrays, which causes sorting difficulties.

I have tried the following:

var $hasMany = array(
  'Match' => array('foreignKey' => array('home_team_id', 'away_team_id'))
);

...with no luck.

Any ideas on how to combine these two foreign keys into a single relationship?

Thanks, Ben

Mala

Answer 1

A custom finderQuery should do the trick:

public $hasMany = array(
    'Match' => array(
        'className'   => 'Match',
        'foreignKey'  => false,
        'finderQuery' => 'SELECT *
                            FROM `matches` as `Match`
                           WHERE `Match`.`home_team_id` = {$__cakeID__$}
                              OR `Match`.`away_team_id` = {$__cakeID__$}'
    )
);

Illconsidered

@Illconsidered any solutions for CakePHP 3.x. – Celenacelene

Answer 2

I was having a similar issue and instead of creating a finderQuery I used the conditions operator and it worked great!

public $hasMany = array(
    'Match' => array(
        'className'   => 'Match',
        'foreignKey'  => false,
        'conditions' => array(
            'OR' => array(
                array('Match.home_team_id' => '{$__cakeID__$}'),
                array('Match.away_team_id' => '{$__cakeID__$}')
            )
        ),
    )
);

Madison

any solutions for CakePHP 3.x. – Celenacelene

Answer 3

They are returned in seperate array's because the sort of represent different models (in this particular case the model is the same).

You should probably build a helper method to go over the retrieved data (in the model object or in a separate helper class) and "flatten" it. then you'd be able to sort it.

Ken.

Untruthful