Yii 1.1: Searching and Sorting by Count of Related Items in CGridView

39 followers

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.

Total 13 comments

#15161 report it
Anil Konsal at 2013/10/14 03:55am
Thanks for this guide! Worked like a charm.

Hi,

Thanks for this guide, it worked like a charm. I am working on a project where we need to show course count of universities based on the filters like field and level of study. Earlier, I did it with STAT relation, but we need to update count as conditions differ with filters.

Thanks again!

Anil Konsal

http://www.anilkonsal.com

#14923 report it
zqrt at 2013/09/22 01:38am
thanks

wow , thanks .

but can we apply this is scope ?

#14523 report it
Fábio Martins at 2013/08/20 12:34pm
Thank you but there was something missing

Great piece of code...although filtering wasn't working for me.

I've needed to add a "safe" rule to $post_count variable because of the massive assignment that need to be done with the GET parameter, in the controller side, when you apply some filter.

"safe" on search scenario wasn't enough!

Hope this info was useful for someone!

Thank you again.

#12154 report it
Daniel at 2013/03/01 05:54pm
Re: How to handle 0

Hi softark,

Here is the question in forum, "wiki-searching-and-sorting-by-count-of-related-items-in-cgridview"

Many thanks in advance.

Daniel

#12130 report it
softark at 2013/02/28 05:38am
RE: How to handle 0

@Daniel

I'm using this in my projects myself, and it is working fine even when the counted value is 0.

Would you please state your problem in details, not as a comment here, but as a discussion in the forum?

#12124 report it
Daniel at 2013/02/28 02:27am
How to handle 0

Hi,

This tutorial is really helpful, but I got issue with 0 value, if the field result for example $post_count = 0. Then I cannot filter to display it. For example, I have two rows, one has post_count = 0 and the other one has = 5. If I filter "> 3". it will show row which value is 5, but no post_count display for 0. Even, if I filter with "= 0", = "0.0" and "< 2.0".

Can you help?

#10836 report it
softark at 2012/11/26 10:44pm
@daniel

It's a very rough guess, but your problem seems to have its cause on eager loading of a HAS_MANY relation. Would you please try to elaborate your problem a little more, not as a comment here, but as a topic in the forum?

#10806 report it
Daniel at 2012/11/25 05:18pm
Pagination problem

Hi,

I followed your wiki and successfully did the sort and filter, my problem is now with the pagination, It gave me wrong number of rows ;( Hence, some pages at the end are showing no result.

Is there any workaround?

Cheers,

Daniel

#10725 report it
Reinier at 2012/11/19 08:36am
Thanks!

Wow thanks for the nice tutorial and explanation! It helps me a lot. But I unfortunatly have one question. When I load the admin page the default value of the searchfields is set to 0 and 0 (I have two counters)

See my problem on the forum

#9172 report it
kamankily at 2012/07/26 12:57am
it works like magic

it works like magic. thanks you very much

#8305 report it
vastzp at 2012/05/24 08:10am
Thank you

Thank you very much.

#7724 report it
Peter JK at 2012/04/11 10:45pm
as simple as that?

wow,

i never know this before... thx for tips and trick.. Fortunately, i never use CGridView internal filtering mechanism, so this a good wiki.. thank you very much...

#7514 report it
redguy at 2012/03/27 09:27am
nice :)

nice extension

Leave a comment

Please to leave your comment.

Write new article