which cakephp relationship should be used for groups of users? can haveMany use array for foreign_ID?

Question

In my tennis application, my 'match' table has two players (obviously). I've used player1_id and player2_id as a way to track the user ids. I haven't included a user_id foreign key though.

There is also a 'user' table where I want to pull player's names from.

Because 'match' has more than one user and users have more than one model, I'm wondering if the following model configuration will work:

I've setup the user model like this:

var $name = 'User';
var $hasMany = array(
'Match' => array(
'className' => 'Match',
'foreignKey' => array( 'player1_id', 'player2_id'),
'dependent' => true,
)

and the match model like this:

var $name = 'Match';
var $belongsTo = array(
'User' => array(
'className' => 'User',
'foreignKey' =>  'user_id',
'conditions' => '',
)

I need to display the user first/last name for each player where user_id = player1_id or player2_id. Will this work? Can a foreign key use an array? And can a model operation use an 'or' when searching?

Or is there a better way to structure a table (like a match, or could be a group meeting) with more than one user?

Cheers, Paul

Answer 1

I don't think an array as a foreign key on the User > Match relationship would work, but then again I have never tried. The single Match > User relationship with user_id as the foreign won't work though, since, as you said, that foreign id does not exist.

Conceptually the cleanest way would be a proper hasAndBelongsToMany relationship. After all, a match has many players, and players have many matches. So you're really looking at a many-to-many relationship.

To fake it with a belongsTo/hasMany relationship, you would need to do this:

// user model
var $hasMany = array(
   'MatchAsPlayer1' => array(
       'className'  => 'Match',
       'foreignKey' => 'player1_id',
   ),
   'MatchAsPlayer2' => array(
       'className'  => 'Match',
       'foreignKey' => 'player2_id',
   )
);

// match model
var $belongsTo = array(
    'Player1' => array(
        'className'  => 'User',
        'foreignKey' =>  'player1_id'
    ),
    'Player2' => array(
        'className'  => 'User',
        'foreignKey' =>  'player2_id'
    )
);

The ugly part is in the User model, where you'd receive related matches split into ['MatchAsPlayer1'] and ['MatchAsPlayer2']. You could do some trickery in the afterFind callback to merge those, but it's not a nice solution overall. Just go for the hasAndBelongsToMany. :)