Self-Join using composite Foreign Key


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, playerLeft and playerRight reference

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,


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

SELECT, 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