Yii Framework Forum: Self-Join using composite Foreign Key - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Self-Join using composite Foreign Key Rate Topic: -----

#1 User is offline   Olli 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 08-February 10

Posted 08 February 2010 - 08:00 PM

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
0

#2 User is offline   Olli 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 08-February 10

Posted 08 February 2010 - 08:03 PM

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

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users