[Laravel8] MySQL migration으로 VIEW Table 생성

 

 

마이그레이션 생성

php artisan make:migration create_sales_view

 

create, drop문 작성

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class CreateSalesView extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement("DROP VIEW IF EXISTS sales_summary");
        DB::statement("CREATE VIEW sales_summary AS
                        SELECT 
                            ord_email AS ord_email,
                            (CASE
                                WHEN (COUNT(ord_no) = 1) THEN 'F1'
                                WHEN (COUNT(ord_no) = 2) THEN 'F2'
                                ELSE 'F3'
                            END) AS F,
                            CONCAT(YEAR(ord_date), '-', MONTH(ord_date)) AS sales_month,
                            SUM(ord_total_price) AS total_sales_price
                        FROM orders
                        GROUP BY ord_email, ord_date");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement("DROP VIEW IF EXISTS sales_summary");
    }
}

 

참고자료

https://cakephper.hatenadiary.org/entry/20150731/1438347861

LaravelのマイグレーションでView Tableを作成する - cakephperの日記(CakePHP, Laravel, PHP)