CGridView - Order by a Relations Columns

Yii 1.1 General Discussion
1

I know its a common question but I get a specific error about a not joined table.

My main Model (Group) has this relation (1:1):


return array(

        'groupType'=>array(self::BELONGS_TO, 'GroupType', 'groupTypeId','order'=>'name ASC');

As you can see, I’ve tried to put an order attributes thats is not working.

As you can immagine, my other module, GroupType, has an attribute called ‘name’.

In the grid view, the relation model is printed well simply using the name of relation:


array(

    'name'=>'groupType',

    'value'=>'$data->groupType->name'

),

Autoload is working fine in GridView.

Unfortunally the column is not sortable. So I put a CSort parameter to DataProvider provided in the Search Method as below:


$sort = new CSort();

    $sort->defaultOrder = 'id asc';

    $sort->attributes = array(

            'groupType'=>array(

                'asc'=>'groupType.name asc',

                'desc'=>'groupType.name desc'

            ),

            'description'=>array(

                    'asc'=>'t.description asc',

                    'desc'=>'t.description desc'

            ),

            'name'=>array(

                    'asc'=>'t.name asc',

                    'desc'=>'t.name desc'

            ),

            '*');


    return new CActiveDataProvider($this, array(

        'criteria'=>$criteria,

        'sort'=>$sort

    ));

Ordering by description and name (attribute of Group) works fine. When I tried to order by groupType, i get this simply error:


SQL: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'groupType.id' in 'order clause'. The SQL statement executed was: SELECT * FROM `cprol_groups` `t` ORDER BY groupType.name asc LIMIT 20

As you can see, framework is not joining my related table. And in this case, what Alias I have to use due to same-property name?

Thanks you for any advice!

2

I’ve resolved forcing the join.

In the criteria, I have to force join:


$criteria->join = 'join '.GroupType::model()->tableName().' gt on gt.id = t.groupTypeId';

or using the ->with statement:


$criteria->with = 'groupType';

removing the default order in relation and adding an alias:




return array('groupType'=>array(self::BELONGS_TO, 'GroupType', 'groupTypeId','alias'=>'gt'));

so you can order by relation field:


$sort = new CSort();

		$sort->defaultOrder = 'gt.name asc';

		$sort->attributes = array(

				'groupTypeId'=>array(

					'asc'=>'gt.name asc',

					'desc'=>'gt.name desc'

				),
3

Hi

It is easier to use my RelatedSearchBehavior extension

Kind regards

Mario