I am trying to create a data provider that incorporates 4 joins on 3 tables:
Draw = details of each match to be played
Drawround = a group of matches played on a particular day (relationship = ‘drawround’)
XrefCompteam = the hometeam for each match in Draw (relationship = ‘hteam’)
XrefCompteam = the awayteam for each match in Draw (relationship = ‘ateam’)
Here are the relationships
return array( 'drawround' => array(self::BELONGS_TO, 'Drawround', 'drawroundid'), 'hteam' => array(self::BELONGS_TO, 'XrefCompteam', 'hometeamid'), 'ateam' => array(self::BELONGS_TO, 'XrefCompteam', 'awayteamid'),
If I run the following code with only one of the Team tables connected, everything works fine. As soon as I run it with both team tables the error further down occurs.
$dataProvider=new CActiveDataProvider($this, array( 'criteria'=>array( 'condition'=>'drawround.compid='.$id.' AND t.poolno='.$pool, 'with'=>array('ateam', 'hteam', 'drawround'), ), 'pagination'=>array( 'pageSize'=>200, ) )); return $dataProvider;
Error produced when both ‘hteam’ and ‘ateam’ are in the with clause:
CDbCommand failed to execute the SQL statement: SQLSTATE: Integrity constraint violation: 1052 Column 'teamlabel' in order clause is ambiguous..... ...ORDER BY teamlabel ASC, teamlabel ASC LIMIT 200
Now its pretty obvious what is wrong and that is that the 2 fields in the order by statement need to be preceded by their relationship name (hteam and ateam). What is not obvious is why the order is even there as I have not specified any order anywhere.
Any advice would be greatly appreciated.