Cactiverecord->Updateall With A Calulated Field?

Yii 1.1 General Discussion
1

Hi folks,

I am currently trying to use CActiveRecord->updateAll to update a calcuated field in a table.

The calculation will look at ‘price1’ and ‘price2’, then calculate the % difference between the two and enter the figure into ‘percentage_change’.

I have done a thorough search for an answer to this but I cannot seem to find one. Can anyone help?

Also, is the calculation can be done by referencing a custom function which does the calculation rather than using SQL syntax?

Any help would be greatly appreciated!

2

I don’t think that updateAll is made for this purpose. What I would do is a creating a function within your CActiveRecord-based object that calculates the value and call it, for example, updatePercentageChange. You could then call updatePercentageChange and save to persist the percentage_change.

3

Not sure I follow.

Are you suggesting I build a function into the model (i.e. the AR’s class)?

4

I am thinking that another, easier solution would be to pull an array from the table using AR->findAll. From there, iterate it, pulling out the values, calculating them then updateByPK().

Seems like a good idea? Cant see any drawbacks to it?

5

in your model, try


public function beforeSave(){

   $this->percentage_change = ($this->price2-$this->price1)/$this->price1;

}
6

Does beforeSave/afterSave work with updates? I can’t seem to get it working.

7

beforeSave/afterSave will be called only when you save (insert or update) a single AR object. So you can modify a column value of the AR object before saving.

But updateAll() execute a single SQL to update multiple records. It does that without fetching their data into AR objects. You may update a column value using SQL functions in this case.

8

Thanks!

9

good to know, softark, thanks

10

Old but you can use CDbExpression


$model->updateAll(['percentage_change' => new CDbExpression('(price2-price1)/price1')], $model->dbCriteria);