How to make Eloquent play nice with generated columns

Generated columns are a great feature of MySQL. They let you create columns that are computed at the database level. In this article, I will explain how to better integrate them with Eloquent.

To begin, here is a basic generated column that stores a users full name. It is a small amendment to the users table migration that ships with Laravel. There are two types of generated columns, stored and virtual. Virtual columns are calculated at query time and therefore don’t take up any disk space. On the other hand, stored columns are calculated when the data is inserted or updated, meaning they behave more like a normal column and do require space. Bear this in mind when making your decision. The following example uses a stored generated column.

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('first_name', 100);
    $table->string('last_name', 100);
    $table->string('full_name', 201)->nullable(false)->storedAs("CONCAT(first_name,' ',last_name)")->index();
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

This generated column means that each user has a full name that is always up-to-date, whether updated through the application or directly via the database. This way, we don’t have to rely on Laravel model events to ensure that this column is always accurate. As useful as model events are, developers can save models without triggering the events using methods such as ->createQuietly(), which could cause data inconsistency. In addition, direct database edits would also be missed using that approach.

On the flip side, generated columns also have their own caveats. For example, a developer unknowingly trying to update this column would be met with an Illuminate\Database\QueryException.

SQLSTATE[HY000]: General error: 3105 The value specified for generated column 'full_name' in table 'users' is not allowed.

So, let’s discuss some ways to mitigate this.

Property assignment

You could add a property-read docblock definition to the model in question.

/**
 * App\Models\User
 *
 * @property int $id
 * @property string $first_name
 * @property string $last_name
 * @property-read string $full_name
 * @property string $email
 */

When attempting to overwrite this property using a modern IDE, it will show up like this.

This is a great way to warn other developers on your team that this column is not to be overwritten.

Bonus Tip

If you are using the fantastic Laravel IDE Helper package by barryvdh, you can automate this. Just simply add a Hook class as described in the documentation and register this in the config file. From then on, when you run the ide-helper:models artisan command, it will generate the docblock as above and you can retain the automation for the rest of the columns that you add to this model over time.

<?php

namespace App\Support\IdeHelper;

use App\Models\User;
use Barryvdh\LaravelIdeHelper\Console\ModelsCommand;
use Barryvdh\LaravelIdeHelper\Contracts\ModelHookInterface;
use Illuminate\Database\Eloquent\Model;

class UserHook implements ModelHookInterface
{
    public function run(ModelsCommand $command, Model $model): void
    {
        if (! $model instanceof User) {
            return;
        }

        // Named properties are not required, but included to make the example clear
        $command->setProperty(name: 'full_name', type: 'string', read: true, write: false);
    }
}

Mass assignment

Preventing the property from being amended during mass assignment is much easier. You can just use the powerful mass assignment protection already built-in to Laravel. This can be achieved by using either fillable or guarded.

class User extends Authenticatable
{
    protected $guarded = [
        'id',
        'full_name',
    ];
}

Ensuring the model has the attribute loaded

There is one other issue that I’ve come across when using generated columns, and that’s making sure that the model has the generated columns each time you go to access them. Take the following example store method for a UserController. Create a user from a validated UserRequest payload and then flash a message to the session for display on the front-end.

public function store(UserRequest $request): RedirectResponse
{
    $user = User::create($request->userPayload());

    return to_route('admin.users.index')->message("Successfully added $user->full_name as a user.");
}

Running this will result in the flash message displaying like so.

Successfully added as a user.

This is due to the fact that the full_name property isn’t generated until written to the database. It will only show when fetched from the database using a subsequent query. We can account for this in a couple of ways, this time using model events to our advantage.

protected static function booted()
{
    static::saved(function (self $user) {
        if ($user->full_name !== "$user->first_name $user->last_name") {
            // Set the full_name property to match what is in the database
            $user->setAttribute('full_name', "$user->first_name $user->last_name");
            // or
            $user->refresh();
        }
    });
}

In this example, I am listening to the model saved event, which fires when a model is created or updated. The ->setAttribute() approach limits the need for a follow-up database query and sets the attribute directly to be what is done in the generated column definition. The alternative is to call ->refresh(), fetching this newly created / updated model from the database and therefore populating the model with any generated columns.

The condition is there to check if the full_name property already matches the combinedfirst_name and last_name columns. This check is mainly for models being updated, in instances where only other attributes have been altered. It prevents an unnecessary query / call to the setAttribute method.

Summary

As you can see, model events and generated columns are both great ways to keep data in sync, each with their own pros and cons. These are just some of the ways I’ve personally mitigated the drawbacks of using generated columns with Eloquent. Are there any ways I’ve missed? Anything that simplifies the approaches I’ve set out here? Please let me know.

For more information on generated columns, I recommend this fantastic video by Aaron Francis.