With Statement Generating Unneeded Orderby Fields Causing Error

Yii 1.1 General Discussion
1

Hi,

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[23000]: 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.

Greg J

2

Is there a scope/defaultScope defined in the XrefCompteam table? Is teamLabel in the XrefCompteam table? Not sure if it will help, just a late night thought :)

3

Hi jkofsky,

you are correct! didnt even think about that so thanks for pointing it out. thats one of the first things i tried when i started learning yii and had forgotten about it.

thank you very much

cheers

Greg J

4

Greg,

It really was just a late night random thought. Glad it helped :)