Model Attribute Custom SQL

Yii 1.1 General Discussion
1

Hi,

I’ve been puzzeling over this for a while and haven’t been able to find a good solution.

I have table/model(created by gii) with the columns id, created_at, completed_at and name.

I’m tring to get the total amount of time from completed_at minus created_at for each record.

I know i can create a getTotalTime() function in my model but it would be much faster if i could somehow do "TIMEDIFF(completed_at, created_at) as total" in sql instead of converting the date to seconds and then trying to divide it down to the time.

I’ve tried $criteria->select = '*, TIMEDIFF(completed_at, created_at) AS total'; in the search funtion of my model but no luck there.

Is there a way to create a virtual attribute/column that allows for a simple sql select as part of my already created model?

Let me know

Thanks

Oliver

2

In your model class, you should have a public variable $total, smth like:




class SomeClassModel extends CActiveRecord{


   public $total=0;


}

The when you query using CDbCriteria:




$criteria=new CDbCriteria;

$criteria->select='t.*, TIMEDIFF(t.completed_at, t.created_at) AS `total`';

$model=SomeClassModel::model()->find($criteria);


echo $model->total;
3

I haven’t used it for this, but I guess you could use a default scope that modifies the “select” criteria. Then add you virtual attribute.

http://www.yiiframework.com/doc/guide/1.1/en/database.ar#default-scope