Hi Guys,
Im having an issue with ActiveRecords joins that i hope someone can help me with or explain? I have 2 tables One called GameFixtures and one called predictions. Idea is that people will predict the results of the fixtures. Relations shown below.
GameFixtures Relation
'predictions' => array(self::HAS_MANY, 'Predictions', 'fixtureId'),
Predictions Relation
'fixture' => array(self::BELONGS_TO, 'GameFixtures', 'fixtureId'),
I want to display in a form the details from GameFixtures (i.e who is playing and when) alongside the predicted homeScore and awayScore from Predictions (which they may or may not have entered yet i.e they may be no entry yet in predictions for that user/fixtureId).
I know i need a Left or right join but im getting funny results when i use it one way but not the other
When i do this it returns a populated array:
$models = GameFixtures::model()->with(array('predictions'=>array('joinType'=>'LEFT JOIN')))->together()->findAll();
When i do this it returns an empty array:
$models = Predictions::model()->with(array('fixture'=>array('joinType'=>'RIGHT JOIN')))->together()->findAll();
The one that doesnt work executes the query below which when run direct against the Database returns all the rows i need but when run through Yii it returns an empty array!
SELECT `t`.`predictionId` AS `t0_c0`, `t`.`gameId` AS
`t0_c1`, `t`.`fixtureId` AS `t0_c2`, `t`.`userId` AS `t0_c3`, `t`.`predHS`
AS `t0_c4`, `t`.`predAS` AS `t0_c5`, `t`.`creator` AS `t0_c6`,
`t`.`created` AS `t0_c7`, `t`.`editor` AS `t0_c8`, `t`.`edited` AS `t0_c9`,
`t`.`enabled` AS `t0_c10`, `fixture`.`fixtureId` AS `t1_c0`,
`fixture`.`gameweek` AS `t1_c1`, `fixture`.`homeCompTeamId` AS `t1_c2`,
`fixture`.`awayCompTeamId` AS `t1_c3`, `fixture`.`homeScore` AS `t1_c4`,
`fixture`.`awayScore` AS `t1_c5`, `fixture`.`kickoff` AS `t1_c6`,
`fixture`.`creator` AS `t1_c7`, `fixture`.`created` AS `t1_c8`,
`fixture`.`editor` AS `t1_c9`, `fixture`.`edited` AS `t1_c10`,
`fixture`.`enabled` AS `t1_c11` FROM `predictions` `t` RIGHT
JOIN `game_fixtures` `fixture` ON (`t`.`fixtureId`=`fixture`.`fixtureId`)
Can anyone explain why this this happens? Am i missing something?
Many thanks!
Ross