1. The sample table
2. Using CGridColumn
3. Generalizing it
4. Running totals in SQL
5. A little bit more
6. Extra

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 How-to introduces the [CGridColumn] component and how to extend it to provide this running total (which may inspire other uses as well).

The sample table ¶

Our example will use a simple inventory table (we'll omit the Model and Controller code, as they are straightforward): ~~~ [sql] CREATE TABLE inventory (

id          INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
name        VARCHAR(64),
quantity    INTEGER

);

insert into inventory values ( 1, 'Beer', 4 ); insert into inventory values ( 2, 'Wine', 2 ); insert into inventory values ( 3, 'Vodka', 7 ); ~~~ Our goal is to show this in a table with all the product detail, plus a running total of the quantities in a fourth column.

Using CGridColumn ¶

To do this, we define that fourth column with our own helper class, TotalColumn. This extends the grid's column class that does the actual rendering of cell data, and this class has a private variable with that running total.

Our view code is:

<?php
// protected/views/inventory/index.php

Yii::import('zii.widgets.grid.CGridColumn');

class TotalColumn extends CGridColumn {

    private $_total = 0;

    public function renderDataCellContent($row, $data) { // $row number is ignored

        $this->_total += $data->quantity;

        echo $this->_total;
    }
}

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $dp,   // provided by the controller
    'columns' => array(
        'id',
        'name',
        'quantity',
        array(
            'header' => 'Total',
            'class'  => 'TotalColumn'
        )
)));

This produces a grid with four columns, the last being the running total: just like we wanted.

Some points on this approach:

• The class name is a PHP class, not a CSS class, and this creates a new instance of TotalColumn, one time per CGridView, not one time per row.
• Because CGridColumn is part of the Zii hierarchy and not in the usual import path, we must import the class explicitly so Yii can use it.
• It's perfectly acceptable to define this helper class directly in the view. Because the attribute name (quantity) is coded directly into the class, this limits its reuse. The next section talks about making this a more general extension.
• If the CGridView is in some kind of loop within the view, a new TotalColumn object is created each time around, which resets the running total. No static/global variables to get in the way.
• All the other columns defined are of the type CGridColumn too, created automatically by CGridView; they can each be extended in their own way.

Generalizing it ¶

Because the original version hardcodes the attribute name ($data->quantity), we can't reuse this generally, but we can expand the class a bit to allow assignment of the attribute name as part of the object's creation.

In the same array('class'=>'TotalColumn') found in the CGridView, we can add other attribute assignments, including get/set functions we define in the helper class. By adding setAttribute() and getAttribute() methods, we can make our column totalizer more general and move it into the protected/components/ folder (and out of the view code).

<?php

// protected/components/TotalColumn.php

Yii::import('zii.widgets.grid.CGridColumn');

class TotalColumn extends CGridColumn {

    private $_total = 0;
    private $_attr  = null;

    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;
    }
}

This done, we can remove the helper class from the view code and add the attribute name as one of the initializers:

<?php

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $dp,
    'columns' => array(
        'id',
        'name',
        'quantity',
        array(
            'header'    => 'Total',
            'class'     => 'TotalColumn',
            'attribute' => 'quantity',         // THIS IS NEW
        )
    )));

Running totals in SQL ¶

It is possible to do this in SQL directly, but it's very computationally expensive and doesn't fit in well with the Yii way of doing things. But because it's interesting - and people inevitably ask about it - the same query can be performed this way:

mysql> SELECT a.id,
    ->         a.name,
    ->         a.quantity,
    ->         ( SELECT SUM( quantity )
    ->           FROM inventory b
    ->           WHERE b.id <= a.id) AS running_qty
    ->    FROM inventory a
    ->    ORDER BY id;
+----+-------+----------+-------------+
| id | name  | quantity | running_qty |
+----+-------+----------+-------------+
|  1 | Beer  |        4 |           4 | 
|  2 | Vodka |        2 |           6 | 
|  3 | Milk  |        7 |          13 | 
+----+-------+----------+-------------+
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
 
// 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:

$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 CGridColumn we don't have access to the CDataColumn::$type. So, if you want to be able to use 'type'=>'number' so the running total has a numeric format you must alter the class TotalColumn like this

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;
    }
}