Виртуальные колонки БД в миграциях Laravel и MySQL

Небольшой пример из миграции:

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

Существует два типа генерируемых столбцов: виртуальные и хранимые.

Виртуальные столбцы вычисляются во время выполнения. Это означает, что вычисление может занять больше времени, но вы экономите место на диске. Хранимые столбцы сохраняются при сохранении или обновлении записи.

Генерируемые столбцы можно создавать с помощью функций SQL. В этом руководстве рассматриваются примеры с виртуальными столбцами, но то же самое можно сказать и о хранимых столбцах.

Сгенерированные столбцы определяются в миграциях с помощью модификатора столбца. Для виртуального столбца мы используем virtualAs($expression). Для хранимого столбца storedAs($expression).

Один из распространённых случаев использования мутаторов — отображение полного имени пользователя, когда в базе данных есть столбцы first_name и last_name. В модели у нас будет:

//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,
        );
    }
}

То же самое можно сделать на уровне базы данных с помощью миграции.

//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();
    });
}

Когда мы проверим записи в базе данных, мы также увидим поле full_name.

Ещё один хороший пример — пакет Laravel Pulse.

//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...
});

// ...

Виртуальный столбец key_hash создаётся на основе столбца key. Поскольку у нас есть key_hash в базе данных, его можно использовать в запросах к базе данных. Именно это и делается в Laravel Pulse. Они делают большой запрос, в котором key_hash используется для группировки записей.

//src/Storage/DatabaseStorage.php
// ...

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

// ...

Полный текст запроса можно посмотреть здесь.

Таким образом, виртуальные столбцы можно использовать в запросе, поскольку они являются столбцами базы данных.

Другой пример — вычисление денежных сумм. В этом проекте с открытым исходным кодом (к сожалению проект был удалён) чистая сумма вычисляется в виртуальном столбце.

//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();
    });
}

Затем значение отображается пользователю во фронтэнде.

//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'),
            ])
            // ...
    }

    // ...
}

Поэтому используйте генерируемые виртуальные/хранимые столбцы, когда вам нужно извлечь или вычислить данные на основе других столбцов и вы хотите сделать это на уровне базы данных, а не с помощью мутаторов Eloquent.

Статьи о Миграции в Laravel

• Генерация Laravel миграций из существующей базы данных
• Laravel Миграции: Как добавить индекс, если он, возможно, существует
• Laravel: Все секреты миграции