Difference between #5 and #6 of Searching and Sorting by Count of Related Items in CGridView

unchanged
Title
Searching and Sorting by Count of Related Items in CGridView
unchanged
Category
How-tos
unchanged
Tags
CGridView, search, sort, filter, STAT, relation, count
changed
Content
Count of Related Items
----------------
Let's say we have two models and relation between them:
~~~
[php]
class Author extends CActiveRecord 
{
    ...
    function{
...
	function relations() {
        return
		return array(
            'posts'
			'posts' => array(self::HAS_MANY, 'Post', 'author_id'),
        );
    }
    ...
		);
	}
	...
}
 
class Post extends CActiveRecord 
{
    ...{
...
}
~~~
When you list Authors in grid you would like to print the **count** of posts in
column, allow sorting on this column and probably filtering by the count.

Printing Is Easy
----------------
When you want just to print the count, without sorting nor filtering, it's quite
easy.
### Print using HAS_MANY
You may make use of the 'posts' HAS_MANY relation and count the posts in the
grid.
~~~
[php]
$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider'	'dataProvider' =>
$model->search(),
    'filter'
	'filter' => $model,
    'columns'
	'columns' => array(
        'username',
        array(
            //
		'username',
		array(
			// 'name' => 'posts',
            'header'
			'header' => 'Post Count',
            'value'
			'value' => 'count($data->posts)',
        ),
    ),
		),
	),
));
// You can't use 'posts' relation as 'name' property.
~~~
### Print using STAT
Or, you may establish a STAT relation for that purpose.
~~~
[php]
class Author extends CActiveRecord 
{
    ...
    function{
...
	function relations() {
        return
		return array(
            'posts'
			'posts' => array(self::HAS_MANY, 'Post', 'author_id'),
            'postCount'
			'postCount' => array(self::STAT, 'Post', 'author_id'),
        );
    }
    ...
		);
	}
	...
}

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider'	'dataProvider' =>
$model->search(),
    'filter'
	'filter' => $model,
    'columns'
	'columns' => array(
        'username',
        'postCount',
    ),
		'username',
		'postCount',
	),
));
~~~

STAT Relation Doesn't Work for Sorting and Searching
----------------
But when you want to sort or filter by the count of posts, things are totally
different.
You may probably try to use the STAT relation, but will eventually know that you
can't use the STAT relation for sorting or filtering.

A Solution
----------------
A decent working solution (in my opinion) to provide these functionalities is:
### Add an Attribute for the Count
First you have to add a new attribute to Author model that is meant for the
count of posts. Note that this attribute works in 2 ways: 1) It holds the actual
count of posts when the model instance is loaded from the database. 2) It holds
the search parameter regarding the count when the model instance is created for
a data provider. You have to add this new attribute to 'safe' attributes in
search scenario.
~~~
[php]
class Author extends CActiveRecord 
{
    public{
  public $post_count;
    ...
    public
  ...
  public function rules() {
        return
    return array(
            ...
            array('username,
      ...
      array('username, xxx, yyy, post_count', 'safe', 'on' => 'search'
),
        );
    }
    );
  }
}
~~~
### Use Sub Query to Retrieve the Count
Now we have to use this attribute in search criteria (in standard implementation
- there is a search() function in every model). And, we don't rely on the STAT
relation in the search. Instead, we will manually construct a sub query to
retrieve the count of posts and use it in the main criteria:
~~~
[php]
public function search() 
{
    $criteria=new	$criteria=new CDbCriteria;

    //	// sub query to retrieve the count of posts
    $post_table
	$post_table = Post::model()->tableName();
    $post_count_sql
	$post_count_sql = "(select count(*) from $post_table pt
where pt.author_id$post_table.author_id =
t.id)";

    //
	
	// select
    $criteria->select
	$criteria->select = array(
        '*',
        $post_count_sql
		'*',
		$post_count_sql . " as post_count",
    );
	);

    ...
    //	...
	// where
    $criteria->compare($post_count_sql,
	$criteria->compare($post_count_sql, $this->post_count);
    ...
	...

    return	return new
CActiveDataProvider(get_class($this), array(
        'criteria'
		'criteria' => $criteria,
        'sort'
		'sort' => array(
            'defaultOrder'
			'defaultOrder' => 't.username',
                'attributes'
			'attributes' => array(
                    ...
                    //
				...
				// order by
                    'post_count'
				'post_count' => array(
                        'asc'
					'asc' => 'post_count ASC',
                        'desc'
					'desc' => 'post_count DESC',
                    ),
                    '*',
                ),
            ),
            'pagination'
				),
				'*',
			),
		),
		'pagination' => array(
                'pageSize'
			'pageSize' => 20,
            ),
        ));
		),
	));
}
~~~
'attributes' section of sort configurations lets us overload default searching.
This configuration says, that when user wants to sort by 'post_count' field it
should be done like specified. The last entry '*' says that every other field of
this model should be treated normally.
### CGridView
Now we have prepared everything for our grid:
~~~
[php]
$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider'	'dataProvider' =>
$model->search(),
    'filter'
	'filter' => $model,
    'columns'
	'columns' => array(
        'username',
        'post_count',
        array(
            'class'=>'CButtonColumn',
        ),
    ),
		'username',
		'post_count',
		array(
			'class'=>'CButtonColumn',
		),
	),
));
~~~
Thats it. We have sorting and filtering by the count of posts. Note that you can
optionally prepend a comparison operator (<, <=, >, >=, <> or
=) to the search parameter.

Acknowledgment
--------------
This is an homage to a useful article named [Searching and sorting by related
model in
CGridView](http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview
"Searching and sorting by related model in CGridView") written by
redguy. Any resemblance to it is intentional.