如何用 migrate 创建 view table 视图?如何用原生 sql 语句创建表?

mysql里面有一种表叫做视图是一张通过查询语句生成的虚拟表。项目中有个排行榜的需求,需要通过mysql统计用户积分的排行,视图表的建表语句如下:

CREATE VIEW `rankings` AS select `t1`.`customer_id` AS `customer_id`,
`t1`.`total_score` AS `total_score`,
count(0) AS `ranking_num` from (`score_accounts` `t1` left join `score_accounts` `t2` 
    on(((`t2`.`total_score` > `t1`.`total_score`) or ((`t2`.`total_score` = `t1`.`total_score`) 
        and (`t2`.`customer_id` >= `t1`.`customer_id`))))) 
group by `t1`.`total_score`,`t1`.`customer_id`;

但是如何同使用migration,因为按照常规建的migrate建表方式,不好写?我们能不能直接用sql语句建表呢?
答案是可以通过查询构造器来达到。
引入:use Illuminate\Support\Facades\DB;
执行:DB::statement( );
代码如下:

<?php

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

class CreateRankingsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {

        //查询构造器建表:
        DB::statement( 'CREATE VIEW `rankings` AS select `t1`.`customer_id` AS `customer_id`,
`t1`.`total_score` AS `total_score`,
count(0) AS `ranking_num` from (`score_accounts` `t1` left join `score_accounts` `t2` 
    on(((`t2`.`total_score` > `t1`.`total_score`) or ((`t2`.`total_score` = `t1`.`total_score`) 
        and (`t2`.`customer_id` >= `t1`.`customer_id`))))) 
group by `t1`.`total_score`,`t1`.`customer_id`;
     ' );
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('rankings');
    }
}
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!