Yii Framework Forum: CGridView Sorting of nested relations - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

CGridView Sorting of nested relations Rate Topic: -----

#1 User is offline   larry1 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 02-November 10

Posted 03 November 2010 - 03:33 PM

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',
		),
	),
)); ?>

Attached File(s)


0

#2 User is offline   seb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 240
  • Joined: 29-June 09

Posted 04 November 2010 - 02:33 AM

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'.
1

#3 User is offline   larry1 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 02-November 10

Posted 04 November 2010 - 09:26 AM

View Postseb, on 04 November 2010 - 02:33 AM, said:

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'.


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?
0

#4 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,801
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 04 November 2010 - 10:00 AM

View Postlarry1, on 04 November 2010 - 09:26 AM, said:

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?


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

/Tommy
Don't forget to read The Definitive Guide to Yii (1.1 en) (1.1 sv) (2.0 en) | The class reference (1.1) (2.0) has the details
0

#5 User is offline   seb 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 240
  • Joined: 29-June 09

Posted 05 November 2010 - 07:19 AM

View Postlarry1, on 04 November 2010 - 09:26 AM, said:

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?


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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users