Count of Related Items YII

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',
        ),
    ),
));