Use a view instead of a complex Eloquent query in your Laravel application

What is a database view, and how can we use it to make our code cleaner? This is not to be mixed up with the more common view file in Laravel, or any MVC framework for that matter. A database view or more commonly known as a view, is a way to push a more complex query into the database and then make a simpler query against it, kinda like a sub-query, but inside the database. You could also see it as a readonly table, or as a table full of generated columns. I have talked about views in several of my earlier posts, but I think it's time to put that into practice. I would love to show you a really complex query, and the difference between the Eloquent query against several tables, and how it would look when using a view in the database, but I haven't managed to get that query to work in Eloquent.

We will create another simpler, but still very real life query, and improve it by using a view instead of a more complex Eloquent query. So with not much further a do we start.

We have these tables that we need to join, and create queries for.

Artists
Records
Genres
Formats

I believe that you already know how to create migrations for your database, so I will just show the table structures for the tables mentioned above. Some tables are a bit slimmed down since the full version won't add any more meaning for this post.

The artists table.

1+------------+---------------------+------+-----+---------+----------------+
2| Field      | Type                | Null | Key | Default | Extra          |
3+------------+---------------------+------+-----+---------+----------------+
4| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
5| name       | varchar(255)        | NO   |     | NULL    |                |
6| created_at | timestamp           | YES  |     | NULL    |                |
7| updated_at | timestamp           | YES  |     | NULL    |                |
8+------------+---------------------+------+-----+---------+----------------+

The records table.

 1+------------+---------------------+------+-----+---------+----------------+
 2| Field      | Type                | Null | Key | Default | Extra          |
 3+------------+---------------------+------+-----+---------+----------------+
 4| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
 5| title      | varchar(255)        | NO   |     | NULL    |                |
 6| released   | int(11)             | NO   |     | NULL    |                |
 7| artist_id  | bigint(20) unsigned | NO   |     | NULL    |                |
 8| genre_id   | bigint(20) unsigned | NO   |     | NULL    |                |
 9| format_id  | bigint(20) unsigned | NO   |     | NULL    |                |
10| created_at | timestamp           | YES  |     | NULL    |                |
11| updated_at | timestamp           | YES  |     | NULL    |                |
12+------------+---------------------+------+-----+---------+----------------+

The genres table.

1+------------+---------------------+------+-----+---------+----------------+
2| Field      | Type                | Null | Key | Default | Extra          |
3+------------+---------------------+------+-----+---------+----------------+
4| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
5| name       | varchar(255)        | NO   |     | NULL    |                |
6| created_at | timestamp           | YES  |     | NULL    |                |
7| updated_at | timestamp           | YES  |     | NULL    |                |
8+------------+---------------------+------+-----+---------+----------------+

Lastly the formats table.

1+------------+---------------------+------+-----+---------+----------------+
2| Field      | Type                | Null | Key | Default | Extra          |
3+------------+---------------------+------+-----+---------+----------------+
4| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
5| name       | varchar(255)        | NO   |     | NULL    |                |
6| created_at | timestamp           | YES  |     | NULL    |                |
7| updated_at | timestamp           | YES  |     | NULL    |                |
8+------------+---------------------+------+-----+---------+----------------+

Now that we have introduced the four players, it's time to put some use-cases into play.

The records should be ordered by artist in the index view.
Records by the same artist should be ordered by the year they were released.

To make that happen in our controller we need would normally have to do something like this.

 1Record::query()
 2  ->join('artists','records.artist_id', '=', 'artists.id')
 3  ->join('genres', 'records.genre_id', '=', 'genres.id')
 4  ->join('formats', 'records.format_id', '=', 'formats.id')
 5  ->select('records.*',
 6           'artists.name AS artist',
 7           'genres.name AS genre',
 8           'formats.name AS format')
 9  ->orderBy('artists.name')
10  ->orderBy('records.released')
11  ->get();

It's not hideous, but we can make it so much prettier with by using a view. Now Laravel doesn't have any nice way of creating views like it does with creating tables, but we can still use a migration to create the view.

So we create a new migration, and I like to follow the Laravel naming convention for tables even for my views, it makes them a bit odd-ish, but we get the normal support as we would for a table.

 1public function up(): void
 2{
 3    DB::statement("
 4        CREATE OR REPLACE VIEW record_index_views AS
 5        SELECT `records`.*,
 6               `artists`.`name` AS `artist`,
 7               `genres`.`name` AS `genre`,
 8               `formats`.`name` AS `format`
 9        FROM `records`
10        INNER JOIN `artists`
11          ON `records`.`artist_id` = `artists`.`id`
12       INNER JOIN `genres` ON `records`.`genre_id` = `genres`.`id`
13       INNER JOIN `formats` ON `records`.`format_id` = `formats`.`id`;
14    ");
15}
16 
17public function down(): void
18{
19    DB::statement('DROP VIEW IF EXISTS record_index_views;');
20}

Nex step is to create a model for our view so that we can use Eloquent on it, and it should be named RecordIndexView, that way it by default knows the name of our view. Also notice that I didn't use any ORDER BYs in the view, any ordering should be handled in the query.

So let's update the query in our controller.

1RecordIndexView::query()
2  ->orderBy('artist')
3  ->orderBy('released')
4  ->get();

As you can see way cleaner, and if you want to filter it, paginate it, or whatever else you would to with your table, go ahead. The only things you can't do is, update and delete records in the view since it read-only.

Notice also that we told the view exactly which columns to pull out of each table, and as you know, you shouldn't pull more columns than you will need, and even if you include them in the view, you can still tell Eloquent which columns it should get.

So when can you use a view instead of a table? You can use it for all the read queries, that means that you can have an index view like I've shown you, but you could also have one for show and even edit (as long as you don't use it in your store, update or delete queries). So any time you want to pull data from the database, and it includes more than one table, using a view is an option. So if the route method is a GET you could use a view. The view is completely dynamic so no data is stored in it. That means that you don't need to worry about storing information in more than one place, the base tables.

Another example would be if you have a blog, and you have three kinds of posts.

Draft
Unpublished
Published

Then a view might be an option to using a query scope.

 1//View
 2PostDraftView::all();
 3//Scope
 4Post::draft()->get();
 5 
 6//View
 7PostUnpublishedView::all();
 8//Scope
 9Post::unpublished()->get();
10 
11//View
12PostPublishedView::all();
13//Scope
14Post::published()->get();

I hope this post has been an interesting read, and that you have learned how to use a view instead of a complex query in you controller, or where ever you choose to place the logic. Sometimes there is no need for a view, but on occasion there is.

//Tray2