Create MySQL view by migration script in Laravel 4

Question

I'm trying to create view in MySQL in Laravel by migration script. How can we create MySQL view by migration script in Laravel 4?

Answer 1

How about this? Haven't tested it, but I think it should work.

class CreateMyView extends Migration {

    public function up()
    {
        DB::statement( 'CREATE VIEW myview AS SELECT [your select statement here]' );
    }

    public function down()
    {
        DB::statement( 'DROP VIEW myview' );
    }

}

And then you can create a model to access it:

class MyView extends Eloquent {

    protected $table = 'myview';

}

And then to access the view from elsewhere in your app you can query it like you would any other model, e.g.

MyView::all();  // returns all rows from your view
MyView::where( 'price', '>', '100.00' )->get();  // gets rows from your view matching criteria

Props go to the following which provided info on how to do this:

http://laravel.io/forum/05-29-2014-model-with-calculated-sql-field-doesnt-paginate http://forumsarchive.laravel.io/viewtopic.php?pid=51692#p51692

CAVEAT

Be careful if later migrations modify the tables underlying your view. The reason is that per the documentation:

The view definition is “frozen” at creation time, so changes to the underlying tables afterward do not affect the view definition. For example, if a view is defined as SELECT * on a table, new columns added to the table later do not become part of the view.

Really, I guess you'd have to be careful of stuff like that for any migration, so maybe this is not such a big deal.