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.