Yii Framework Wiki
Difference between
#2 and
#6 of
Keeping a running total in a CGridView
Changes
Title unchanged
Keeping a running total in a CGridView
Category unchanged
How-tos
Yii version unchanged
Tags unchanged
CGridView
Content changed
This tip created in conjunction with IRC #yii channel users **emilsedgh** and **tydeas** - thanks!
When using [CGridView] to display rows of data, each one is independent of the other - this is normally what we want. But a cases requiring a running total (say, the current balance in a bank account register or a running inventory count) are more tricky because of no shared context.
```
This method gets exponentially slower as the number of rows increases. This is **not** recommended.
A little bit more
-----------------
### Transaction example
In the above example refers we had an inventory table, instead of it if we may have a transaction table like:
~~~
[mysql]
CREATE TABLE transaction (
id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
quantity INTEGER,
date TIMESTAMP,
);
~~~
That would look like:
~~~
+----+----------+------------+
| id | quantity | date |
+----+----------+------------+
| 1 | 10 | 2011-03-01 |
| 2 | 10 | 2011-03-10 |
| 3 | -1 | 2011-03-19 |
| 4 | -2 | 2011-03-25 |
| 5 | -9 | 2011-04-01 |
+----+----------+------------+
~~~
The above simple example will work here as well with no problem. But what will happen if the user wants to add a from date to date condition in the criteria that init the dataprovider for the view?
Because the TotalColumn will always start with $_total = 0; the result will be false. What we can do is alter the TotalColumn code and add setter and getter for the total value like:
```php
<?php
// protected/components/TotalColumn.php
Yii::import('zii.widgets.grid.CGridColumn');
class TotalColumn extends CGridColumn {
private $_total = 0;
private $_attr = null;
public function getTotal()
{
$this->_total;
}
public function setTotal($value)
{
$this->_total = $value;
}
public function getAttribute()
{
return $this->_attr;
}
public function setAttribute($value)
{
$this->_attr = $value;
}
public function renderDataCellContent($row, $data) {
$this->_total += $data->{$this->attribute};
echo $this->_total;
}
}
```
And doing the following will result in a proper transaction grid:
```php
$criteria = new CDbCriteria();
$criteria->addCondition('date > 2011-03-20');
$dp = new CActiveDataProvider('Transaction', array('criteria'=>$criteria));
$this->widget('zii.widgets.grid.CGridView', array(
'dataProvider' => $dp,
'columns' => array(
'id',
'name',
'quantity',
array(
'header' => 'Total',
'class' => 'TotalColumn',
'attribute' => 'quantity', // THIS IS NEW
'total' => 19, // The true total for date < 2011-03-20
)
)));
```
Extra
-----
Its a common case that you will need a numeric format for this "running total" field. Because we extend <code>CGridColumn</code> we don't have access to the <code>[CDataColumn::$type](http://www.yiiframework.com/doc/api/1.1/CDataColumn#type-detail). </code>
So, if you want to be able to use <code>'type'=>'number'</code> so the running total has a numeric format you must alter the <code>class TotalColumn</code> like this
```php
class TotalColumn extends CGridColumn
{
...
public $type = 'number'; //If you want to be able to access it.
...
public function renderDataCellContent($row, $data) {
$this->_total += $data->{$this->attribute};
echo (isset($this->type)) ? $this->grid->getFormatter()->format($this->_total, $this->type) : this->_total;
}
}
```
When using [CGridView] to display rows of data, each one is independent of the other - this is normally what we want. But a cases requiring a running total (say, the current balance in a bank account register or a running inventory count) are more tricky because of no shared context.
[...]
3 rows in set (0.00 sec)```
This method gets exponentially slower as the number of rows increases. This is **not** recommended.
A little bit more
-----------------
### Transaction example
In the above example refers we had an inventory table, instead of it if we may have a transaction table like:
~~~
[mysql]
CREATE TABLE transaction (
id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
quantity INTEGER,
date TIMESTAMP,
);
~~~
That would look like:
~~~
+----+----------+------------+
| id | quantity | date |
+----+----------+------------+
| 1 | 10 | 2011-03-01 |
| 2 | 10 | 2011-03-10 |
| 3 | -1 | 2011-03-19 |
| 4 | -2 | 2011-03-25 |
| 5 | -9 | 2011-04-01 |
+----+----------+------------+
~~~
The above simple example will work here as well with no problem. But what will happen if the user wants to add a from date to date condition in the criteria that init the dataprovider for the view?
Because the TotalColumn will always start with $_total = 0; the result will be false. What we can do is alter the TotalColumn code and add setter and getter for the total value like:
```php
<?php
// protected/components/TotalColumn.php
Yii::import('zii.widgets.grid.CGridColumn');
class TotalColumn extends CGridColumn {
private $_total = 0;
private $_attr = null;
public function getTotal()
{
$this->_total;
}
public function setTotal($value)
{
$this->_total = $value;
}
public function getAttribute()
{
return $this->_attr;
}
public function setAttribute($value)
{
$this->_attr = $value;
}
public function renderDataCellContent($row, $data) {
$this->_total += $data->{$this->attribute};
echo $this->_total;
}
}
```
And doing the following will result in a proper transaction grid:
```php
$criteria = new CDbCriteria();
$criteria->addCondition('date > 2011-03-20');
$dp = new CActiveDataProvider('Transaction', array('criteria'=>$criteria));
$this->widget('zii.widgets.grid.CGridView', array(
'dataProvider' => $dp,
'columns' => array(
'id',
'name',
'quantity',
array(
'header' => 'Total',
'class' => 'TotalColumn',
'attribute' => 'quantity', // THIS IS NEW
'total' => 19, // The true total for date < 2011-03-20
)
)));
```
Extra
-----
Its a common case that you will need a numeric format for this "running total" field. Because we extend <code>CGridColumn</code> we don't have access to the <code>[CDataColumn::$type](http://www.yiiframework.com/doc/api/1.1/CDataColumn#type-detail). </code>
So, if you want to be able to use <code>'type'=>'number'</code> so the running total has a numeric format you must alter the <code>class TotalColumn</code> like this
```php
class TotalColumn extends CGridColumn
{
...
public $type = 'number'; //If you want to be able to access it.
...
public function renderDataCellContent($row, $data) {
$this->_total += $data->{$this->attribute};
echo (isset($this->type)) ? $this->grid->getFormatter()->format($this->_total, $this->type) : this->_total;
}
}
```
19 followers
Viewed: 80 273 times
Version: 1.1
Category: How-tos
Written by: Steve Friedl
Last updated by: tydeas_dr
Created on: Mar 12, 2011
Last updated: 13 years ago
Revisions
View all history-
13 years ago by
tydeas_dr corrected syntax error
-
13 years ago by
tydeas_dr added type
-
13 years ago by
Steve Friedl Added tydeas to the acknowledgements
-
13 years ago by
Steve Friedl Minor cleanups