[SOLVED] Relationship in Criteria question

Yii 1.1 General Discussion
1

I have 3 tables with these relation

port (countryid) ->BELONGS_TO-> country

country (regionid) ->BELONGS_TO-> region

In Port listing page, I need to show all 3 columns Cgridview including Region, which I have no problem using




        array(

            'header'=>$attributeLabels['regionName'],

            'value'=>'$data->country->region->name',

            ),

But how do I ORDER BY REGION NAME in the controller?

in PortController->actionList




// no problem sorting by country using with

        $dataProvider=new CActiveDataProvider('Port', array(

                    'criteria'=>array(                 

                        'order'=>'`t`.`countryid` ASC',

                         'with'=>array('country'),

                        ),

                    )

                )




// how to do something to this effect ?? (sort by region name)


        $dataProvider=new CActiveDataProvider('Port', array(

                    'criteria'=>array(                 

                        'order'=>'country.region.name ASC, `t`.`countryid` ASC', //obviously not working

                         'with'=>array('country.region'),

                        ),

                    )

                )

I read about using "through" but its only for HAS_MANY and HAS_ONE…

2

Try with this:


        $dataProvider=new CActiveDataProvider('Port', array(

                    'criteria'=>array(                 

                        'order'=>'country.region.name ASC, `t`.`countryid` ASC', //obviously not working

                         'with'=>array('country.region'),

                         'toghether'=>true,

                        ),

                    )

                )
3

got this error




CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'country.region.name' in 'order clause'

is this the correct way to reference it?

4

I solved it by randomly trying out the arrays like this…Yii is magical




                    'criteria'=>array(                 

                         'order'=>'region.name ASC, country.name ASC, `t`.`name` ASC',

                         'with'=>array('country'=>array('with'=>array('region'))

                         ),

                         'together'=>true,

                        ),