SUM IF ?

Here is the scenario:

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

Figured it out.

For those wondering how its done:

in the model:

declare your var publicly

public $total_hours

add it to the attributes:

public $total_hours;

	public function attributeLabels()

	{

		return array(

			'id' => 'ID',

			'user_id' => 'User',

			'date' => 'Date',

			'company_id' => 'Company',

			'asset_id' => 'Asset',

			'hours' => 'Hours',

			'notes' => 'Notes',

			'total_hours' => 'total_hours',

		);

	}

public function search3()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.

		if(Yii::app()->user->group_id=='1') {

			$myco=	$this->user_id;

			}

			else {

			$myco= Yii::app()->user->user_id;

			}

		$criteria=new CDbCriteria;

		$criteria->select =array('id,user_id,date,company_id,asset_id,hours,notes,

                                         SUM( IF(date = date, 1 * hours, 0 ) ) AS total_hours');

				$criteria->group='date';

		 $result = Timesheet::model()->find($criteria); 

		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

Then in the view total_hours is accessible … woo hoo!

Note on related models:

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.