如何用 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');
}
}
推荐文章: