How to sort the fields from relation table in Yii?

Question

I am trying to implement sorting with Yii list view. I have joined 2 tables named provider_favourite and service_request in list view. And the fields and contents from both tables are listing in the list view. But sorting is working only in provider_favourite table, not from service_request table.How can I sort the fields from service_request table? Iam using csort for sorting. I also tried CGrid view. But the same problem is happening in grid view also ..
Iam using the following code to join

 $criteria = new CDbCriteria;
    $criteria->select = 'favourite_notes,favourite, favourite_added_date,max_budget,preferred_location,service_name';
    $criteria->join = 'LEFT JOIN service_request AS s ON  service_request_id = favourite';
    $criteria->condition = 'favourite_type = 1';
    $sort=new CSort('ProviderFavourite');
 //        $sort->defaultOrder='s.max_budget ';
    $sort->applyOrder($criteria);
    $sort->attributes = array(
            'max_budget' => 'service_request.max_budget',
            'service_name' => 'service_request.service_name',
            'favourite_added_date'
    );
        $type = 2;
    $data = new CActiveDataProvider('ProviderFavourite', array('criteria' => $criteria, 'pagination' => array('pageSize' => 4),'sort'=>$sort
            ));
    $this->renderPartial('favourites', array(
        'ModelInstance' => ProviderFavourite::model()->findAll($criteria),
        'dataProvider' => $data, 'type' => $type, 'sort'=>$sort,
    ));

and also Iam providing sortable attributes in list view

 $this->widget('zii.widgets.CListView', array('dataProvider'=>$dataProvider,'itemView'=>'index_1',     
         'id'=>'request',
         'template' => '  {items}{pager}',
         'sortableAttributes'=>array('favourite_notes','max_budget','service_name')
 ));

If more details needed, I will provide. Thanks in advance

ezze · Accepted Answer · 2013-04-17T11:56:32.700

2

You have to specify attributes property of your $sort instance. By default only fields of $modelClass (ProviderFavourite in your case) are sortable.

I think it could look like this (not tested):

$sort->attributes = array(
    'service_name' => array(
        'asc' => 's.service_name ASC',
        'desc' => 's.service_name DESC'
    ),
    // ...another sortable virtual attributes from service_request table
    "*"
);

edited Apr 17 '13 at 11:56

answered Apr 17 '13 at 11:22

ezze

3,933
2
34
58

I think I had provided it . Can you please check my question . I had updated the code. But the same problem is showing. The cursor is changing while hovering through it and on clicking , the page is also getting reloaded. But sorting is not happening – anu Apr 17 '13 at 11:38
Documentation on [sortableAttributes](http://www.yiiframework.com/doc/api/1.1/CListView#sortableAttributes-detail) says that in order for an attribute to be sortable, it must also appear as a sortable attribute in the `IDataProvider::sort` property of `dataProvider`. In other words this attribute must be declared for sorting in `attributes` property of `CSort`. – ezze Apr 17 '13 at 11:49
`CSort` instance itself can also be declared using `sort` property of `CActiveDataProvider` like shown [here](http://www.yiiframework.com/forum/index.php/topic/9632-newbie-question-how-to-best-sort-with-clistview-using-relations/page__p__47656#entry47656). – ezze Apr 17 '13 at 12:01

score 1 · Answer 2 · answered Apr 17 '13 at 13:10

You should not create a CSort object in $sort. The CActiveDataProvider will already provide you with the right sort object. The way you do it, you apply the sort criteria to $criteria before you configure the sort attributes. That can not work.

You should try a simple setup like this instead:

$data = new CActiveDataProvider('ProviderFavourite', array(
    'criteria' => $criteria, 
    'pagination' => array('pageSize' => 4),
    'sort'=> array(
        'attributes' => array(
            'service_name' => array(
                'asc' => 's.service_name ASC',
                'desc' => 's.service_name DESC'
           ),
           // ...
    ),
));

If you need to access the related CSort object (which you usually don't if you use a CGridView or CListView, because they deal with that for you), then you get it via $data->sort.

How to sort the fields from relation table in Yii?

2 Answers2