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:
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.
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?
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)
foreach($provider->data as $item)
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.
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()
// ... more search conditions
public function search()
return new CActiveDataProvider(get_class($this), array(
public function totals()
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.
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.