I have a timesheet table and what I would like to do is run a sql statement that includes a sum so I can output it to CGridView.
This is a typical search() function:
public function search()
{
// Warning: Please modify the following code to remove attributes that
// should not be searched.
$criteria=new CDbCriteria;
$criteria->compare('id',$this->id);
$criteria->compare('user_id',$this->user_id);
$criteria->compare('date',$this->date,true);
$criteria->compare('company_id',$this->company_id);
$criteria->compare('asset_id',$this->asset_id);
$criteria->compare('hours',$this->hours);
$criteria->compare('notes',$this->notes,true);
//$criteria->group='date';
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
));
}
This is the sql I am trying to accomplish, how would I go about it:
SELECT username, DATE, company_name, asset_no, timesheet.notes, SUM( IF( timesheet.date = timesheet.date, 1 * timesheet.hours, 0 ) ) AS total_hours
FROM assets, companies, user, timesheet
WHERE user_id = user.id
AND timesheet.company_id = companies.id
AND timesheet.asset_id = assets.id
GROUP BY DATE
LIMIT 0 , 30
If you are using SUM(IF()) to calculate values from a related model, then the virtual attribute ($total_hours) needs to be declared in the related model - and not in the parent model containing the search() function.
After creating the dataprovider, you could iterate over its data to access the virtual attribute in the related model’s object.