Laravel Create Virtual Database Column

In this article we're going to learn how to create a virtual database column in Laravel.

Why Use Virtual Columns?

Virtual columns are useful when you want to add a column to a model that doesn't exist in the database. This can be useful for things like computed columns, or for columns that are derived from other columns.

I recently had a situation where I needed to add a virtual column to a model that was derived from other columns in the model. I didn't want to store this column in the database, as it would be redundant and would require additional maintenance.

This was from a JSON blob column that I needed to search for a value in. I didn't want to extract this and store this separately in another column as I need this to stay in sync with the JSON blob. But I wanted to search for this column and be able to index the column for performance improvements rather than searching the JSON blob.

Creating a Virtual Column

To create a new virtual column in MySQL from a JSON blob column, you can use the JSON_EXTRACT function. This function allows you to extract a value from a JSON blob column and use it as a virtual column.

Here's an example of how you can create a virtual column in MySQL:

ALTER TABLE `users` ADD COLUMN `email` VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.email'))) VIRTUAL;

In this example, we're adding a new virtual column called email to the users table. This column is derived from the data column, which is a JSON blob column. We're using the JSON_EXTRACT function to extract the email field from the JSON blob and store it in the email column.

Using Virtual Columns in Laravel

First we need to create the migration to add the virtual column to the table. Here's an example of how you can create a migration to add a virtual column to a table in Laravel:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddVirtualColumnToUsersTable extends Migration
{
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('email')->virtualAs("JSON_UNQUOTE(JSON_EXTRACT(`data`, '$.email'))");
        });
    }

    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('email');
        });
    }
}

In this example, we're creating a new migration called AddVirtualColumnToUsersTable that adds a virtual column called email to the users table. We're using the virtualAs method to specify the expression that should be used to generate the virtual column.

Once you've created the migration, you can run it using the php artisan migrate command. This will add the virtual column to the table.

Use In Laravel

Now that we have the virtual column added to the table, we can use it in our Laravel application like any other column.

$user = User::where('email', 'test@email.com')->first();

In this example, we're using the virtual column email to search for a user with the email address.

You can also access this column like any other column in your model:

$user = User::find(1);
echo $user->email;

In this example, we're accessing the email virtual column on the User model.

Conclusion

In this article, we've learned how to create a virtual database column in Laravel. Virtual columns are useful when you want to add a column to a model that doesn't exist in the database. This can be useful for things like computed columns, or for columns that are derived from other columns.