Say i have a table with columns: prices, quantity and total. I was the make the total column be computed automatically by multiplying the price times the quantity and storing it in total at all times. How can I achieve this?
Say i have a table with columns: prices, quantity and total. I was the make the total column be computed automatically by multiplying the price times the quantity and storing it in total at all times. How can I achieve this?
You can either calculate it at runtime or calculate and store it when a row is inserted/updated. If you’re using AR, see: http://www.yiiframework.com/doc/api/1.1/CActiveRecord#beforeSave-detail
Like Artificial said above, you have two choice:
...
public function getTotalColumn() {
return ($this->quantity * $this->price);
}
...
and we can call it using
$model->getTotalColumn()
or even, you can use virtual attribute,
...
class MyModel extends CActiveRecord {
public $totalColumn;
...
public function afterFind() {
parent::afterFind();
$this->totalColumn = $this->price * $this->quantity;
}
}
we can call it using
$model->totalColumn
The thing is I am not sure that the $totalColumn can be directly filtered or sorted in the CGridView. It is possible though, just could not find the link on the wiki ;(
...
public function beforeSave() {
$this->totalColumn = $this->quantity * $this->price;
return parent::beforeSave();
}
...
Much more comprehensive post, though:
$totalColumn = $this->price * $this->quantity;
Should be changed to:
$this->totalColumn = $this->price * $this->quantity;
Thanks Artificial, You spot on my mistake. Do you have any suggestion for the filter and sort of the first approach?
Below is not tested and not sure if working.
...
public function search() {
...
$criteria->compare('quantity*price', $this->totalColumn);
...
$sort->attributes = array(
...
'totalColumn' => array(
'asc' => '(quantity*price) ASC',
'desc' => '(quantity*price) DESC',
),
....
);
}
...
I don’t think your compare method will work. I believe the first parameter only points to a single column. What you probably can do is check if your totalColumn attribute isn’t null (when searching) then use the addCondition method
Edit:
I was looking at the source code and it looks like compare uses the addCondition function too and directly passes the 1st attribute to it as a column so maybe your suggestion will work fine.
Yes, You are right.
What do you think about performance wise?
Saving the value in table probably quicker than calculate it, but to me the maintenance of the saved value could be complex and difficult. But, does the calculation will takes much longer time?
Thanks in advance.
It’s almost always better to let db do these calculations first and keep the result for later, either as a new column or view column.
not sure with that, back to database lesson in uni, we should avoid have the calculated column in the table. By having calculated column saved on the table you need to maintain it and make sure every changes must change the calculated column as well. In yii, the maintenance can be very simple by overriding beforeSave method and force the recalculation every time we save the model/row.
Is there any possibility to save the model without yii calling for beforeSave() method?
my concern is the data integrity vs performance. save the calculated column in the table will have better performance, but the integrity of the data can be questionable while set it as virtual attribute will enforce the data integrity but might have performance effect? But is that slower performance is still tolerable?
Hi @Bizley I would like to know by “view column” you mean database view (https://dev.mysql.com/doc/refman/8.0/en/create-view.html)?
Yes. (please avoid necroing topics)
Just adding a note here with no intention to re-open discussion: I have published a blog post + gist (schema+data) demonstrating calculated field in a database table using view.