CGridView Totals or Summary Row

Hi All,

I’m using CGridView to display spreadsheet-like data for a report. For the most part it works swimmingly. However, I seem to be lacking a way to add a summary row for totals. For example, if I had a table with columns “name” and “age” and “weight”, and rows of data for each person with their associated values, I might want to have a final row to display the total or average weight or age for all people in my data set. Is this possible? If not natively available in the CGridView widget, can I add a custom data row to the end of the $data returned by my data provider before it gets rendered? Any suggestions or ideas are much appreciated!

I’ve added a method getTotals() to my model, that calculates some total value depending on the current attributes (similar to how the CActiveDataProvider is built in search() ). Then in the grid view i use a footer for one column like this:


array(

    'name'=>'Time',

    'value'=>'sprintf(\'%02s:%02s\',$data->hours,$data->minutes)',

    'type'=>'text',

    'footer'=>$provider->itemCount===0 ? '' : $model->getTotals(),

),



Thanks, Mike! I’ve looked at the footer attribute in the docs several times, but was never really sure what it was for and could never find any examples of usage anywhere. This is just what I need. I really appreciate the tip.

Anyway to do this with a CArrayDataProvider?

How do you create this provider? You should have some sort of model class that provides the provider. That class would be the best place for the method mentioned above.

The provider is created through an array retrieved from a SQL query command. Since the data is not retrieved from the site’s own database.

The question is: Where do you have this code? Like i suggested above i’d wrap this into some sort of model class. It does not necessarily have to extend CModel - but it should contain all the related business logic for reading/writing this data (a.k.a db query code).

This is great and what I am looking for. But, what if I only want the totals for the displayed rows?

For example, I have 50 rows, and the pagination is every 10 rows. If I am in page 2 which showing rows 11-20. How can I get only the totals for rows 11-20? How can I pass the parameter currentPage and rows per page to the model? and how can I limit the calculation (additions) to only involve result rows 11-20?

Thanks a lot.

Daniel

You can add another helper to your model (could be a static method) that accepts the data provider as argument. The data property of this object will contain the current page data. You can loop over this data to create the total sum of the items from the current page:




public static function pageTotal($provider)

{

    $total=0;

    foreach($provider->data as $item)

        $total+=$item->someValue;

    return $total;

}



I just test it and works fine, but how if i want the total of rows returned by


$model->search()

? not only the total of displayed rows. And one more, how to get the values of attributes returned by this search model. Any example will be much appreciated. Thanks.

Well, refactor your model to suit your requirements. Add another method, that calculates (or queries) the total for the current filter settings. I’d probably use a method like getSearchCriteria() that i can use in both, search()/totals(). Whereas search() returns the data provider, totals() uses the same criteria for calculation of the sum.

Totally a newbie…could you please give an example please :P. I think there are the other need it too. Thanks be4.

@itmagetan - please do not post same questions on two different threads.

Here is the other thread - http://www.yiiframework.com/forum/index.php?/topic/16709-

Ouch…Please forgive me for that. It’s unawares writing because i need a solution in a short time for my recent project. No more double post again. Thank you for any kind intention. :)

Ok, i found that this issue maybe requires some deeper understanding of what’s going on behind the scenes of AR. Check out this code:


public function getSearchCriteria()

{

    $criteria=new CDbCriteria;


    $criteria->compare('id',$this->id);

    $criteria->compare('name',$this->name);

    // ... more search conditions


    return $criteria;

}


public function search()

{

    return new CActiveDataProvider(get_class($this), array(

        'criteria'=>$this->getSearchCriteria(),

    ));

}


public function totals()

{

    $criteria=$this->getSearchCriteria();

    $criteria->select='SUM(somecolumn)';

    return $this->commandBuilder->createFindCommand($this->getTableSchema(),$criteria)->queryScalar();

}



We’ve “outsourced” the creation of CDbCriteria into another method getSearchCriteria() because we need the same criteria like in search(), to calculate the sum over some column. The non trivial part is in totals(): Here we use the CDbCommandBuilder to let it create a CDbCommand object with “SELECT SUM(somecolum)” from our search criteria. Then we call queryScalar() because that’s all we need.

Now you can use $model->totals() to retrieve the SUM() over some column for use in your datagrid.

Sure Mike, i realize that anyone who decided to use framework for building application needs to have good understanding in OOP and MVC concepts. I’ve been programming PHP in procedural style in last 4 years, so, this is a big jump for me.

I don’t know which one is more effective related to Yii’s rules, but i’ve found a solution for my problem led by mdomba. Here’s the post http://www.yiiframework.com/forum/index.php?/topic/16709-cgridview-filter/page__p__82860__fromsearch__1#entry82860.

Anyway, thank you very much for the enlightenment. I really appreciate it. Go forward Yii… ;D

Take your time. It takes some practice to learn “thinking in objects”. But i know where you’re coming from. And i can say: It brings back a lot of fun into programming - at least for me, because i enjoy to create orderliness where before was only mess :)

mdomba’s solution will work, too. But it has an important difference: It loads all records from the search result and calculates the sum in PHP. This might be o.k. for small amounts of data. But it’s a no go for huge datasets. The above solution puts the calculation into the DB (where it should be) and only retrieves 1 single number.

The current solution on the other link ( why do we have two threads talking about the same problem ??? ) can be easily changed to the solution offered here by Mike… as in the getTotal() function the criteria is sent…

Yes mdomba, solution for any of my problem is a happiness for me, but a better solution (as delivered by Mike) is a challenge. Thank you for all of you. This has been triggering me to out of my newbie status. ;D

If i put in getSearchCriteria $criteria->select=array(‘SUM(colname) as colname’…

And in total i need to get a total sum of aliases colname

totals()$criteria->select=‘SUM(colname)’;

In total i get the same value as in first raw of colname. Can anybody help?