1. Count of Related Items
2. Printing Is Easy
3. STAT Relation Doesn't Work for Sorting and Searching
4. A Solution
5. Acknowledgment

Count of Related Items ¶

Let's say we have two models and relation between them:

class Author extends CActiveRecord 
{
    ...
    function relations() {
        return array(
            '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.

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $model->search(),
    'filter' => $model,
    'columns' => array(
        'username',
        array(
            // 'name' => 'posts',
            'header' => 'Post Count',
            '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.

class Author extends CActiveRecord 
{
    ...
    function relations() {
        return array(
            'posts' => array(self::HAS_MANY, 'Post', 'author_id'),
            'postCount' => array(self::STAT, 'Post', 'author_id'),
        );
    }
    ...
}

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $model->search(),
    'filter' => $model,
    'columns' => array(
        '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.

class Author extends CActiveRecord 
{
    public $post_count;
    ...
    public function rules() {
        return array(
            ...
            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:

public function search() 
{
    $criteria=new CDbCriteria;

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

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

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

    return new CActiveDataProvider(get_class($this), array(
        'criteria' => $criteria,
        'sort' => array(
            'defaultOrder' => 't.username',
                'attributes' => array(
                    ...
                    // order by
                    'post_count' => array(
                        'asc' => 'post_count ASC',
                        'desc' => 'post_count DESC',
                    ),
                    '*',
                ),
            ),
            'pagination' => array(
                '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:

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $model->search(),
    'filter' => $model,
    'columns' => array(
        '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 written by redguy. Any resemblance to it is intentional.