Generated database field with migrations

Boban Published at Dev

Boban

I have two float fields: fieldA and fieldB. I would like to store in DB float field fieldC, which will always have value: fieldA * fieldB.

I would like to achieve that with migrations. This is what I tried

$table->float('fieldC', 12, 2)->storedAs('fieldA * fieldB');

and

$table->float('fieldC', 12, 2)->storedAs('MULTIPLY(fieldA, fieldB)');

Both didn't work. Errors are Column not found: 1054 Unknown column 'fieldA' and General error: 1901 Function or expression 'MULTIPLY()' cannot be used.

Any suggestions?

katsarov

Laravel migrations dosn't support that. But you can make trigger with raw statement. Something like that:

DB::unprepared("
    DELIMITER $$

CREATE TRIGGER after_update
AFTER UPDATE
ON tableName FOR EACH ROW
BEGIN
    IF old.fieldA <> new.fieldA OR old.fieldB <> new.fieldB THEN
        UPDATE tableName SET fieldC=fieldA+fieldB WHERE id=new.id;
    END IF;
END$$

DELIMITER ;
");

You can make it more simple using Laravel model

<?php
class YourModel extends Model {
    public static function boot()
    {
        parent::boot();
        self::saving(function($model){
            $model->fieldC = $model->fieldA + $model->fieldB;
        });
    }
}

Collected from the Internet

Please contact [email protected] to delete if infringement.