My search() method takes an argument that is a list of values of a relational table attribute; this is used when building the criteria. Given that my criteria uses a condition that requires a relational table, am unable to use createFindCommand as it creates a select command only on a single table. How should I handle such a scenario? Should I rebuild the entire command, including joins, separately without reusing the getSearchCriteria() code to get the totals?
Thank you.
The given example was taylored to the given use case. If you need something else, you can build any command you want in totals(). You can also save the arguments to search() in another private variable and access them from totals(), in case you need them for calculating the sum.
I guess I should have phrased the question better. Was wondering if it was possible to to re-use the getSearchCriteria() method for both search() and totals(), and build a query in totals() using the CDbCriteria object returned by getSearchCriteria() when this object has a relational table condition. createFindCommand can run a select command only on one table but the criteria object uses another relational table, so I can’t use createFindCommand(). I was basically looking for an alternative to this.
[Passing/storing the arguments is not an issue.]
After looking at my example code again i wonder, if anyone has tested it at all 
. I guess it doesn’t work, because when the dataprovider fetches the data, it applies a limit to the search criteria. So when calling totals() the same limit will still be set in the criteria. To make it work, we would have to unset the limit in the totals() method first.
Anyway …
@yiifan: I think in this case it’s not so easy. You could study CActiveFinder. But you will find that it’s very hard to retrieve the full SQL there. So you can’t create your custom command to query for the SUM() over some column.
It’s great! Many Thanks
In my case im using a component that do the query with commandController and also return an array, so thats why im using also the CArrayDataProvider and id like to know how to get subtotal with pagination.
Anyone knows how to solve it?
It’s a little old topic, but I just want to say thanks to Mike for this wonderful solution.
It helped me a lot.
And I want to share my bit of experience.
The following is the abbreviated code of what I did to satisfy my specific needs.
class MyModel extends CActiveRecord
{
...
public $amount_sum; // virtual attribute for sum(amount)
public $price_sum; // virtual attribute for sum(price)
...
public function getSearchCriteria()
{
$criteria=new CDbCriteria;
$criteria->compare('id',$this->id);
$criteria->compare('name',$this->name);
// ... more search conditions
return $criteria;
}
public function search()
{
$this->fetchSums();
$criteria = $this->getSearchCriteria();
return new CActiveDataProvider(get_class($this), array(
'criteria' => $criteria,
'sort' => array(
...
),
'pagination' => array(
...
),
));
}
public function fetchSums()
{
$criteria = $this->getSearchCriteria();
$criteria->select = array(
'SUM(t.amount) as amount_sum',
'SUM(t.price) as price_sum'
);
$ret = MyModel::model()->find($criteria);
if ($ret)
{
$this->amount_sum = $ret->amount_sum;
$this->price_sum = $ret->price_sum;
}
else
{
$this->amount_sum = 0;
$this->price_sum = 0;
}
}
}
I introduced 2 virtual attributes for the totals.
I called fetchSums() method from search() method.
I used CActiveRecord::find() instead of CDbCommand::queryScalar() or CDbCommand::queryRow().
CDbCommandBuilder::createFindCommand() didn’t work when I specified a condition on the related table. It’s maybe because createFindCommand “creates a SELECT command for a single table”, as the class reference says.
http://www.yiiframework.com/doc/api/1.1/CDbCommandBuilder#createFindCommand-detail
The calculation of sum works on the total available rows, not limited to the rows in the current page.
As far as I understand, ‘OFFSET’ and ‘LIMIT’ is (and should be) added to the criteria later by the CGridView or CListView, not by search() method.
Anyway, it’s working great. Thanks a lot, Mike. 
example i have created for another one project. hope it will help to solve this problem.
in cgridview
‘columns’=>array(
'patientname',
array(
'name'=>'payment',
'type'=>'text',
'footer'=>$model->getTotals($model->search()->getKeys()),
),
'mode',
'employeerel.emp_name',
'recdate',
array(
'class'=>'bootstrap.widgets.TbButtonColumn',
),
in model
public function getTotals($ids)
{
$ids = implode(",",$ids);
$connection=Yii::app()->db;
$command=$connection->createCommand("SELECT SUM(payment)
FROM `payment` where id in ($ids)");
return "Total Rs: ".$amount = $command->queryScalar();
}This is really interesting thread to follow.
There arises two scenario.
1.Getting total of a field in a single page.
2.Getting total for a particular field in all the pages.
1.The first scenario is well explained by Mike.
I have a model Wage.
I have a field Hours.
In the model,
Wage.php
public function fetchTotalHours($wages)
{
$hours=0;
foreach($wages as $wage)
$hours+=$wage->hours;
return $hours;
}
We can declare the column for the attribute in the following way.
array(
'name'=>'hours',
'footer'=>"Total: ".$model()->fetchTotalHours($model->search()->data),
),
2.In the second scenario, we can modify the model method in the following way.
public function fetchTotalHours($criteria)
{
$wages=self::model()->findAll($criteria);
$hours=0;
foreach($wages as $wage)
$hours+=$wage->hours;
return $hours;
}
In admin.php, we can declare the column in the following way.
array(
'name'=>'hours',
'footer'=>"Total: ".$model->fetchTotalHours($model->search()->criteria),
),
Now we can get total of all the records, spanning across all the pages.
Hey Elite plz help me… I am getting error in this line
‘footer’=>dataProvider->itemCount===0 ? ‘’ : $model->getTotals(),
How to access this total out of footer in yii??
I have done
Thanks mate … Bang on target… Great solution…
Cheers