Self-Join using composite Foreign Key

Hi,

I’ve spent hours running against this wall… I’m pretty sure I made a mistake somewhere, but I just don’t get it…

I have an SQL table:




CREATE TABLE `policy` (

 `game` int(11) NOT NULL,

 `playerLeft` int(11) NOT NULL,

 `playerRight` int(11) NOT NULL,

 `type` enum('War','Trade','NAP','Pact','Alliance') NOT NULL

) 



‘game’ references game.id, playerLeft and playerRight reference player.id.

Inside the Policy-Class I have a self-join (fkConfirmed) defined as


	

return array(

  ...

  'fkGame' => array(self::BELONGS_TO, 'Game', 'game'),

  ...

  'fkConfirmed' => array(self::HAS_ONE, 'Policy', 'game,playerRight,playerLeft'),

)

The reason behind this relation is that if a policy between users has been confirmed, there will be two records in the table. Once with playerLeft, playerRight, once swapped.

To check for this confirmation I need to find out if a record with reversed player ids is present.

When I try to get the contents of fkConfirmed, either by using with(‘fkConfirmed’) or by accessing the member, I get an error about a missing column Policy.‘id’. This is because ‘game’ relates to the table ‘Game’, column ‘id’. I can see the logic behind this, but this is not what I want.

Is there any way I can get what I’m looking for?

Thanks in advance,

Olli

The result I want to get is generated by the following SQL:


SELECT p1.game, p1.playerLeft, p1.playerRight

FROM policy p1 

LEFT JOIN policy p2 ON p1.playerLeft = p2.playerRight AND p1.playerRight = p2.playerLeft AND p1.type = p2.type 

WHERE p2.playerLeft IS NOT NULL