Sometimes we need to create (SQL) views of database tables for various reasons. In the documentation on Laravel migrations it does not specify anything regarding SQL views. But it would be nice when running migrations, to be able to create the views also with the migrations, hence the question:
How can we create views in the database with migrations in laravel?
Assume the following basic example taken from the MySQL documentation on views
mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty | price | value |
+------+-------+-------+
| 3 | 50 | 150 |
+------+-------+-------+
The view in question:
CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
Laravel version preferable >= 5.6
DBMS = MySQL >= 5.5
One way would be to use the facade
DB
with the methodstatement
for creating the view from a migration.Create the migration with the name you want, after creating the tables that will be involved, otherwise it will throw an error when running the migration.
And in the migration execute the
query
creation of the view directlyFrom the controller or some other part that wants to get the result of the view, you would also use the facade
DB
I like it better this way, because when a reset is done, it doesn't give an error.