Sorting CListView in yii

Question

Please consider this:

class User extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            ...    
            'articleCount' => array(self::STAT, 'Article', 'userid'),
            ...    
            );
    }
    ...
}

Now, I need to create a $dataProvider = new CActiveDataProvider(...) to feed a CListView widget to which I want add articleCount to the property sortableAttributes so that I can sort User records according to the number of articles the user is the author for.

What are the most convenient method? what are the other alternatives?

Answer 1

Well, it took me a while, but I finally got it figured out. ;)

First, make sure you still have the same STAT relation you mention in your question.

Then, in your search() method where you are building your CDbCriteria for the CActiveDataProvider, do something like this:

public function search() {
  $criteria=new CDbCriteria;
  $criteria->select = 't.*, IFNULL( count(article.id), 0) as articleCount';
  $criteria->join = 'LEFT JOIN article ON article.userid = t.id';
  $criteria->group = 't.id';
  // other $criteria->compare conditions

  $sort = new CSort();
  $sort->attributes = array(
    'articleCount'=>array(
      'asc'=>'articleCountASC',
      'desc'=>'articleCountDESC',
    ),
    '*', // add all of the other columns as sortable
  );

  return new CActiveDataProvider(get_class($this), array(
    'criteria'=>$criteria,
    'sort'=>$sort,
    'pagination'=> array(
      'pageSize'=>20,
    ),
  ));
}

Then, in your View where you output your CGridView, do this like so:

<?php $this->widget('zii.widgets.grid.CGridView', array(
  'dataProvider'=>$model->search(),
  'columns'=>array(
    'articleCount',
    // other columns here
  )
)); ?>

This works, I tested it (although I did not use the exact same names, like 'article', but the basic idea should work :) I did add some bonus features so it works better (like the IFNULL magic) but most of the credit goes to this Yii forum post:
http://www.yiiframework.com/forum/index.php?/topic/7061-csort-and-selfstat-to-sort-by-count/

I hope this helps! They should add better support for this though, so you don't need to make these tricky SELECT statements. Seems like something that should 'just work', I would file an 'enhancement' request in the Yii bug tracker.