CGridView Sorting of nested relations

Yii 1.1 General Discussion
1

First off, Yii is a great product. It’s really speeding my development along.

I have nested relations that I would like to sort/filter on in the CGridView.

The one level of relations works (InventoryStatus), but I can’t get the 2nd level of relation (Edition Name) to sort/filter. I also have the offending relation working in a different model (1 level of nested relation)

I think it has something to do with the dot notation in the With() edition.editionType and the related CSort array.

If I try to decouple the names, I get compile errors. Relation "whatever the attempt was" is not defined in active record class "Inventory".

Am I missing something basic here?

Inventory belongsTo Edition belongsTo EditionType

The FKs are related by ids

In Inventory Model (CDbCriteria)




      $criteria->with = array('inventoryStatus','edition.editionType');

      $criteria->addSearchCondition('inventoryStatus.name',$this->inventoryStatus);

      $criteria->addSearchCondition('editionType.name',$this->edition->editionType);

In Inventory Model (CSort)




        $sort = new CSort();

        $sort->defaultOrder = 'editionType.name ASC';

        $sort->attributes = array(

           'id',

           'edition.editionType' => array(

             'asc'=>'edition.editionType.name',

             'desc'=>'edition.editionType.name DESC',

           ),

           'inventoryStatus' => array(

             'asc'=>'inventoryStatus.name',

             'desc'=>'inventoryStatus.name DESC',

           ),	 

        );

In the view:




<?php $this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'inventory-grid',

	'dataProvider'=>$model->search(),

	'filter'=>$model,

	'columns'=>array(

	        'id',

		array(

		 'name'=>'inventoryStatus',

		 'value'=>'$data->inventoryStatus->name',

		 'filter'=>CHtml::listData(InventoryStatus::model()->findAll(array('order'=>'name')), 'name', 'name'),

		),

		array(

		 'name'=>'edition.editionType',

		 'value'=>'$data->edition->editionType->name',

		 'filter'=>CHtml::listData(editionType::model()->findAll(array('order'=>'name')), 'name', 'name'),

		),

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>
2

It is possible to sort by second level relations, but you should use last level alias in the sort settings:




$sort->attributes = array(

           'id',

           //key should be the same as 'name' in column settings

           'editionType' => array(

             //use editionType.name instead of edition.editionType.name

             'asc'=>'editionType.name',

             'desc'=>'editionType.name DESC',

           ),

           ...

        );


'columns'=>array(

    array(

        'name'=>'editionType',

        'value'=>'$data->edition->editionType->name',

     ...

This is because when you use ‘with’ => ‘edition.editionType’ then table alias in the resulting SQL will be ‘editionType’, not ‘edition.editionType’.

3

I tried the suggestions you made, but got the "Property "Inventory.editionType" is not defined" error.

This makes sense, based on the documentation of the columns array - the name is the attribute in the model.

Because the editionType is one level removed, it can only be referenced by edition.editionType which is not defined in the inventory model.

Any other thoughts?

4

I should try ‘together’=>true next. If still no luck, inspect the generated SQL.

/Tommy

5

I took this code from my current project and it actually works for two level relations.

Regarding documentation for column name - it says "string the attribute name of the data model. The corresponding attribute value will be rendered in each data cell. If value is specified, this property will be ignored unless the column needs to be sortable.".

Please show you current grid configuration and generated SQL (you can see it in the log file) when error appears.

Update: and regarding together=>true - yes, you should set it. Otherwise yii will use lazy loading for relations and sorting will fail because initial SQL query does not contain fields from related records.

One more suggestion - for complex grids it can be easier to add special database view for this grid. This way you will get your dataset as plain table without joins, so CGridView configuration will be very simple.