Laravel Eloquent – How to effectively manage SQL views

Who is this article for?

Anyone looking for how to manage SQL Views effectively in a Laravel App.

In this article, I'm going to walk through, how to effectively manage SQL views with version control using Console Command and Migrations in Laravel.

Before You Begin

If you haven't already know how to create a view from migration, this is how you do it. However, this article isn't about how to create a view from migration. It is about how to manage SQL Views effectively. So let's start.

Step 1 - Create a Console Command

Step 2 - Update the Console Command to Create or Update View

Step 3 - Create a new migration to call the command.

Now every time you need to update the SQL view, you can update the console command

Advantages -

  1. Easy to maintain SQL Views as it can get difficult if you have to copy the full definition of view in a migration everytime you need to update it.
  2. Power of source control.
  3. Helpful in maintaining a view which has many columns.

As you know, Laravel treats tables, view as eloquent, so let's take it another step further and create a model.

Step 4 - Create a Model

Step 5 - Test out the newly created Model

Now, you can add scopes, mutators etc to the model. Ref to Laravel Documentation. Although, you cannot update the view as SQL View are not real tables.

In future articles I will talk about how to setup and manage Materialised SQL Views in Laravel.  You can read more about Materialised view here.

Sources

 

Posted in LARAVEL, MYSQL, PHP, BLOG
Akki K

Akki

Experienced Backend Developer.