1. The sample table
2. Using CGridColumn
3. Generalizing it
4. Running totals in SQL

This tip created in conjunction with IRC #yii channel user emilsedgh - 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.