Using MySQL Views with Laravel

Normally, when building an application with MySQL you’ll have lots of interrelated data in interrelated tables. You’ll define foreign keys to define those relationships, and use JOIN queries to query related data.

Alternatively, if you are using Laravel, it’s common not to use JOIN queries to get related data in a single query, but rather to define relationships in your Model classes, and then (hopefully!) use the Query Builder’s ->with() method to eager load those relationships when needed.

However, there is a third option: Creating a MySQL View. Views in MySQL look from the outside just like a table. They are essentially stored as a query that returns a result set, and that you can then run a query against.

This can be more convenient - and sometimes offer better performance - than eager-loading lots of relationships.

Setting the scene

For our example, we’ll imagine having an e-commerce store. For our purposes we have two tables: A table of brands, and a table of products, with a brand_id foreign key.

Your Brand model will then look something like this (use and namespace declarations are omitted throughout this post for brevity):

class Brand extends Model
{
    protected $fillable = ['name'];

    public function products(): HasMany
    {
        return $this->hasMany(Product::class);
    }
}

And here is your Product model:

class Product extends Model
{
    protected $fillable = ['name', 'brand_id'];

    public function brand(): BelongsTo
    {
        return $this->belongsTo(Brand::class);
    }
}

Pretty standard stuff. But what if you want to list your products with brand names? Well, most Laravel developers will probably just eager-load the brand relation:

$products = Product::query()
    ->with('brand')
    ->get();

@foreach($products as $product)
    <p>{{ $product->name }} by {{ $product->brand->name }}</p>
@endforeach

And that works. But if you need a lot of relationships to display your data, it can get quite memory intensive.

Another option is to reach for a JOIN instead:

$products = Product::query()
    ->join('brands', 'brands.id', '=', 'products.brand_id')
    ->select(['products.*', 'brands.name as brand_name'])
    ->get();

@foreach($products as $product)
    <p>{{ $product->name }} by {{ $product->brand_name }}</p>
@endforeach

But that’s a lot of code. Additionally, you need to list all the columns individually for each query (some might consider this an advantage). And the complexity only increases as you add more JOINs. Finally, you get no casts or autocomplete on these joined columns. (On the contrary my editor actively complains about the use of a non-existing property.)

This is where MySQL views come in handy.

Create a MySQL View with Laravel

Our view will act as a virtual table containing both brand and product info. We create the view using a Laravel Migration:

php artisan make:migration create_product_view

Let’s open the migration file we’ve just created, and put our view creation in there. While Laravel’s migrations don’t have in-built support for MySQL Views directly, we can just execute the statement that creates the view using the DB facade:

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        DB::statement(
            <<<'SQL'
            CREATE VIEW product_views AS 
            SELECT  products.id,
                    products.name,
                    products.brand_id,
                    brands.name as brand_name
            FROM    products
              JOIN  brands on brands.id = products.brand_id;
            SQL
        );
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::statement('DROP VIEW product_views;');
    }
};

Note that the SQL convention for a view name would be products_view, but we are sticking with Laravel’s naming convention over SQL’s here. If you prefer the SQL way, you can simply add protected $table = 'products_view'; to your model class later on.

(NB: You might wonder about my use of PHP Nowdoc syntax here: These aren’t very popular, especially because they were a bit tricky before PHP 7.3. But since PHP 7.3, they have some really great advantages for multi-line strings, as the indentation will be stripped automatically, and I do like using them for SQL statements, as it’s less escaping and fudging with quotation marks and indentation than the alternatives.)

Create a Model class

Creating the view is only the first step though. The real beauty is that we can now create a Model class for this view, making this very versatile:

class ProductView extends Model
{
    public function brand(): BelongsTo
    {
        return $this->belongsTo(Brand::class);
    }
}

Notice how I can define relations on this, just as though it was a ’normal’ model? I can also use casts, scopes, Mutators, and all of the other Laravel Model magic. So if we have a status column and a ProductStatusEnum we can just extend this class:

class ProductView extends Model
{
    protected function casts(): array
    {
        return [
            'status' => ProductStatusEnum::class,
        ];
    }

    public function scopeActive(Builder $query): Builder
    {
        return $query->where('status', ProductStatusEnum::active);
    }

    public function brand(): BelongsTo
    {
        return $this->belongsTo(Brand::class);
    }
}

This is really neat and encapsulate all our logic nicely in the one Model class. (Even better, in the real would you’d maybe want to refactor those sort of things into a trait that is being used by both the Product and ProductView classes.)

I can also use my ProductView as a relation in other models in the usual way. For example I might have a category_id column in my products table, which I can then also include in my view. This would allow me to define a relation on the Category model which can be used this to fetch all of a category’s products:

class Category extends Model
{
    public function product_views(): BelongsTo
    {
        return $this->belongsTo(ProductView::class);
    }
}

For most purposes a view behaves just like a table, and therefore a Model backed by a view can be used mostly in the same way as a Model backed by a ’normal’ table. (See below on exceptions to this.)

Why use a MySQL view?

Now you might wonder, why use a MySQL view, rather than define a join? And in this particular case you could almost certainly achieve much the same with a Scope that defines a join on the Product class.

But not all cases are that trivial. And if I have to join many tables, maybe do casts on some columns, etc, then being able to define all of these in a class is hugely advantageous.

What disadvantages does a MySQL view have in Laravel?

Well, primarily that views are read-only. Trying to insert or update a record in the view will throw an error:

ProductView::create([
    // ...
])

// Illuminate\Database\QueryException 
// SQLSTATE[HY000]: General error:   1471 The target table product_views of the INSERT is not insertable-into

Unfortunately Laravel doesn’t, to my knowledge, have a mechanism to indicate this on the class, so you won’t notice you’ve made this mistake until run-time.

Can I have autocomplete in ‘Model’ that’s backed by a MySQL view in Laravel?

Auto completion of Model properties in Laravel requires either either the laravel-ide-helper package, or the PhpStorm Laravel Idea Plugin. I highly recommend the latter! Sadly, neither are able to detect properties from views, so you’ll need to type-hint your columns manually on the Model class for autocomplete to be possible, like this:

/**
 * @property int               $id
 * @property string            $name
 * @property string            $brand_name
 * @property int               $brand_id
 * @property ProductStatusEnum $status
 */
class ProductView extends Model
{
    // Model stuff
}

On the plus side, relations and scopes are properly recognised by Laravel Idea and autocompletion does work for these, which is amazing.

How about performance of MySQL Views?

A MySQL view may offer improved performance over eager-loading relationships, depending on a number of factors, so this is hard to predict, and will likely depend primarily on how complex your models are, and how well you have defined your foreign keys and indices.

Relative to a manual JOIN, performance shouldn’t be impacted either positively or negatively by a view: It will perform the same as if you were executing the query manually every time you query it.

In summary: make sure you got your keys and indices set up correctly, check your execution plans, and measure, to see which option is better for which use case.

Summary

Due to Laravel’s emphasis on relations, eager-loading etc, we might sometimes forget that most databases also offer views. But especially if you have more complex joins (maybe even unions) that you re-use across multiple parts of your code base, these can be an ideal choice to simplify code re-use.