Yii grid view: sort by multiple level related values

Submitted by boaz on Saturday, June 9, 2012.

I have this CGridView table that shows 'articles' and I needed to be able user to sort based on "Author's name" column. Problem is, the main AR class here is Article, which related to User class, and the latter is related to Profile class, which holds the user's first and last name. How do I render the first and last name in the grid? It took me a while to find, but eventually, its done as described here, and shown below:

In the view file, the data provider property of CGridView will be the search method of the AR class. Also, note the column defined for the CGridView, and especially with the virtual attribute named 'author'. This attribute is referenced in the CSort object:

$dataProvider = $model->search();

$columns = array (
  array(

    'class' => 'PcLinkButton',
    'urlExpression' => 'expression for calculation of url here...',
    'labelExpression' => '$data->authorUser->profile->last_name . " " . $data->authorUser->profile->first_name',
   'header' => "Author",
   'name' => 'author',
),

 

$this->widget('zii.widgets.grid.CGridView', array(
  'id' => 'my-grid',
  'dataProvider' => $dataProvider,
  'columns' => $columns,
));
 
Inside the Article model class:
public function relations() {
  return array(
    'authorUser' => array(self::BELONGS_TO, 'User', 'author_userid'),
    //.. more relation definitions...
  );
}

 

Also in the Article class:

public function search() {
$criteria = new CDbCriteria;
 
$criteria->with = array('authorUser');
 
// 'compare() statements specs below, if needed to...
// $criteria->compare('... 
 
$criteria->with = array('authorUser', 'authorUser.profile');
 
$sort = new CSort;
$sort->modelClass = __CLASS__;
$sort->attributes = array(
  'category' => array(
    'asc' => 'category.name',
    'desc' => 'category.name DESC'
  ),
  'author' => array(
    'asc' => 'profile.last_name, profile.last_name',
    'desc' => 'profile.last_name DESC, profile.last_name DESC',
  ),
  'created_on',
);

}

The key is in the last part - we define the values of the sort attributes definitions array using relationship stated as "profile.last_name" and not "authorUser.profile.last_name". This is working since after we used the with() method above the resulted SQL will be using an alias named "profile" and not "authorUser.profile".

 

1 comment:

Great article
Marek Paul says:
October 2, 2012 at 07:57 am
Thanks for this article.

Leave a Comment

Fields with * are required.