Laravel Virtual Columns fail to save

Question

I added a couple of virtual columns to my database tables using Laravels virtualAs column modifier:

 $table->decimal('grand_total')->virtualAs( '(total_value + (total_value*tax_rate))');

Basically it keeps a mysql virtual column that automatically calculates the grand total based on the total and tax rate stored in another column.

However, Laravel does not seem to play nice with virtual columns at all. When saving a record, it attempts to INSERT or UPDATE the virtual column, which is obviously not allowed in mySQL. I could not find a way to configure in the Eloquent model which fields are actually written to the database on an update or insert.

I've tried adding the field to the models $hidden, and $appends but nothing seems to work.

Looking at the Laravel Source code for an insert (https://github.com/laravel/framework/blob/5.6/src/Illuminate/Database/Eloquent/Model.php#L733), it seems to just insert whatever attributes are in $this->attributes. When the record is read from the database the grand_total field is read from the table and set as an attribute and then it is tried to be written again once the record is saved.

Is there any way to get this Laravel to stop trying to save columns that are virtual?

Answer 1

Here's a quick trait I wrote to solve your problem that will filter fields residing in the $virtualFields property before saving. It requires a select (refresh) after the save to get the new value for the virtual field. If you don't need to query this virtual field, I'd highly recommend you look into a mutator instead.

trait HasVirtualFields 
{
     public function save(array $options = [])
     {
          if (isset($this->virtualFields)) {
              $this->attributes = array_diff_key($this->attributes, array_flip($this->virtualFields));
          }

          $return = parent::save($options);
          $this->refresh(); // Refresh the model for the new virtual column values
          return $return;
    }
}

class YourModel
{
    use HasVirtualFields;
    protected $virtualFields = ['grand_total'];
}