Virtual DB Columns in Laravel Migrations and MySQL

In Laravel, if we want to have a DB value calculated on the fly, we often use Mutators. But we can also perform calculations on the database level with generated virtual/stored columns.

A quick example from migration:

$table->string('full_name')->virtualAs("CONCAT(first_name, ' ', last_name)");

There are two types of generated columns: virtual and stored.

Virtual columns are calculated at a runtime. This means it can take longer to calculate, but you save disk space. Stored columns are stored when a record is saved or updated.

Generated columns can be generated using SQL functions. This tutorial will look at examples with virtual columns, but the same would be with the stored column.

Generated columns are defined in the Migrations using the column modifier. We would use virtualAs($expression) for the virtual column. For the stored column, we use storedAs($expression).


One common use case of Mutators is to show the full name of the User when we have first_name and last_name columns in the database. In the Model we would have:

app/Models/User.php:

use Illuminate\Database\Eloquent\Casts\Attribute;
 
class User extends Authenticatable
{
// ...
 
protected function fullName(): Attribute
{
return Attribute::make(
get: fn() => $this->first_name . ' ' . $this->last_name,
);
}
}

The same can be done on the database level with the Migrations.

database/migrations/xxx_create_users_table.php:

public function up(): void
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('first_name');
$table->string('last_name');
$table->string('full_name')->virtualAs("CONCAT(first_name, ' ', last_name)");
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->rememberToken();
$table->timestamps();
});
}

When we check database records, we also see the full_name.


Another good example comes from the Laravel Pulse package.

2023_06_07_000001_create_pulse_tables.php:

// ...
 
Schema::create('pulse_values', function (Blueprint $table) {
$table->id();
$table->unsignedInteger('timestamp');
$table->string('type');
$table->mediumText('key');
match ($this->driver()) {
'mysql' => $table->char('key_hash', 16)->charset('binary')->virtualAs('unhex(md5(`key`))'),
'pgsql' => $table->uuid('key_hash')->storedAs('md5("key")::uuid'),
'sqlite' => $table->string('key_hash'),
};
$table->mediumText('value');
 
$table->index('timestamp'); // For trimming...
$table->index('type'); // For fast lookups and purging...
$table->unique(['type', 'key_hash']); // For data integrity and upserts...
});
 
// ...

The virtual column key_hash is created from the key column. Because we have key_hash in the database, it can be used in the database queries. Precisely, that is being done in the Laravel Pulse. They make a big query in which key_hash is used to group records.

src/Storage/DatabaseStorage.php:

// ...
 
$query
->from('pulse_entries')
->where('type', $type)
->where('timestamp', '>=', $windowStart)
->where('timestamp', '<=', $oldestBucket - 1)
->groupBy('key_hash');
 
// ...

You can check the full query here.

So, virtual columns can be used in the query because they are database columns.


Another example is calculating money values. The net amount is calculated in the virtual column in this open-source project.

database/migrations/xxx_create_loans_table.php:

public function up(): void
{
Schema::create('loans', function (Blueprint $table) {
$table->id();
$table->foreignId('member_id')->constrained();
$table->foreignId('loan_application_id')->constrained();
$table->foreignId('loan_type_id')->constrained();
$table->string('reference_number');
$table->string('check_number')->nullable();
$table->string('priority_number');
$table->decimal('gross_amount', 18, 4);
$table->decimal('net_amount', 18, 4)->virtualAs('gross_amount - deductions_amount');
$table->json('deductions')->default(DB::raw('(JSON_ARRAY())'));
$table->integer('number_of_terms');
$table->decimal('interest_rate', 7, 4);
$table->decimal('interest', 18, 4);
$table->decimal('service_fee', 18, 4)->default(0);
$table->decimal('cbu_amount', 18, 4)->default(0);
$table->decimal('imprest_amount', 18, 4)->default(0);
$table->decimal('insurance_amount', 18, 4)->default(0);
$table->decimal('loan_buyout_interest', 18, 4)->default(0);
$table->decimal('loan_buyout_principal', 18, 4)->default(0);
$table->decimal('deductions_amount', 18, 4);
$table->decimal('monthly_payment', 16, 4);
$table->date('release_date');
$table->date('transaction_date')->default(DB::raw('CURDATE()'));
$table->boolean('posted')->default(false);
$table->timestamps();
});
}

Then, the value is shown to the user on the front end.

app/Livewire/App/LoansTable.php:

class LoansTable extends Component implements HasForms, HasTable
{
// ...
 
public function table(Table $table): Table
{
return $table
->query(Loan::whereMemberId($this->member->id))
->columns([
TextColumn::make('reference_number'),
TextColumn::make('loan_type.code'),
TextColumn::make('number_of_terms'),
TextColumn::make('gross_amount')->money('PHP'),
TextColumn::make('interest')->money('PHP'),
TextColumn::make('deductions_amount')->money('PHP'),
TextColumn::make('net_amount')->money('PHP'),
TextColumn::make('monthly_payment')->money('PHP'),
TextColumn::make('outstanding_balance')->money('PHP'),
TextColumn::make('transaction_date')->date('F d, Y'),
])
// ...
}
 
// ...
}

So, use generated virtual/stored columns when you need to extract or calculate data based on other columns and you want to do it on the database level instead of Eloquent Mutators.

avatar

Generated columns are defined in the Migrations using the column modifier. We would use virtualAs($expression) for the virtual column. For the stored column, we use generatedAs($expression).

I'm a littile comfused according to Laravel docs , generatedAs($expression) is used only for PostgreSQL. I think for MySQL we used storedAs($expression).

Think you and have a nice day.

avatar

You are right. Missed this part somehow. Updated

avatar

nice use of the match and drivers in pulse migration.

avatar

The only problem I see here is:

Let's say we have a $user with first_name = 'Martin' and last_name = 'Bojmaliev'. Our virtual '$user->name' is equal to: Martin Bojmaliev

Once I do:

$user->first_name = 'Foo';
$user->save();

My $user->name remains 'Martin Bojmaliev'

I should do $user->refresh() or $user->fresh() in order to get the new calculated value of $user->name

avatar

This makes sense, because of how Laravel updates the model. And with virtual columns you need to re-fetch any changed data.

ps. This is also an issue in many other cases, where observers trigger and change data. So this is expected

Like our articles?

Become a Premium Member for $129/year or $29/month
What else you will get:
  • 56 courses (1011 lessons, total 46 h 42 min)
  • 77 long-form tutorials (one new every week)
  • access to project repositories
  • access to private Discord

Recent Premium Tutorials