Calculate the sum of a column in related models using CActiveRecord in Yii

Question

I have 2 tables: user(id,email,...) and task(id,uid,COMPLAINTS,..)

The relation is

'tasks' => array(self::HAS_MANY, 'Task', 'uid'),

I am displaying all the users in a grid view and want to add a column that shows the sum of the complaints column for tasks belonging to the user. I was able to achieve this with this STAT relation in the User model:

'complaints'=>array(self::STAT, 'Task', 'uid', 'select'=>'SUM(complaints)', 'defaultValue'=>'0'),

However, this will create a lot of queries to the database when displaying in grid view. But it can be done in a single query:

select user.*, sum(task.complaints) 
from user 
left join task 
on user.id=task.uid 
group by user.id

so I came up with this:

In the action method of the controller:
$criteria = new CDbCriteria;
$criteria->with = array('tasks'=>array('select'=>'sum(complaints) AS compl'));
$criteria->group = 't.id';

I can see this generates the right query when looking at the logs, however I'm not able to access the value for the sum. Is this the right way of doing it? If it is, then how can I access this new column?

Answer 1

then how can I access this new column?

In GridView you just add this column among others:

$this->widget('zii.widgets.grid.CGridView', array(  
    'dataProvider'=>$dataProvider,  
    'columns'=>array(
        'id',
        'group',
        'tasks',
 ...

Update

As Ali said previously, you need to define this property in model.

class User {
  public $complain;
  ...
}

Then you assign it with values thru sql:

$sql->db->createCommand('SELECT SUM(complaints) AS compl, id FROM <table_name>.t GROUP BY t.user_id');
$dataReader=$sql->queryAll(); 

this suppose to return the array of values groupped by user (user_id). See docs.

Now you do next step to join them into models:

$models=User::model()->findAll();
foreach($dataReader as $row)
{  
   $models[$row['id']]->complain=$row['compl'];
}
//the resulting $models you leverage for displaying in GridView.

Or you add these data $row['compl'] into ArrayDataProvider to join them with original model data for displaying in grid as well.