如何在Laravel中使用php artisan创建数据库视图的迁移？

php laravel laravel-5.2

2022-08-30 12:26:57

实际上，我已经设法使用PHP Artisan使用PHP Artisan为Laravel创建了一个sql视图。

步骤 1.运行以下命令：

php artisan make:migration create_overall_report_views

步骤2.

打开迁移文件并添加以下代码：

class CreateOverallReportView extends Migration 
{
  /**
  * Run the migrations.
  *
  * @return void
  */
  public function up()
  {
    //
    DB::statement("
      CREATE VIEW views_overall_report AS
      (
        SELECT er.user_id as user_id, e.id AS entities_id, 
            c.status_id AS status_id, s.name AS status_name

        FROM `user_roles` er
          LEFT JOIN elists e ON e.id=er.entities_id
          LEFT JOIN `clists` c ON c.id=e.checklists_id
          LEFT JOIN `status` s ON s.id = c.overall_status_id

        WHERE s.slug = 'completed'
          AND c.deleted_at IS NULL
      )
    ");
  }

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

}

第3步.通过 Laravel 查询调用和运行 SQL 视图

$items = $DB::table('views_overall_report')
            ->select('status_id', 'status_name',
                $DB::raw('count(entities_id) as counts')
            )
            ->groupBy('status_id')
            ->orderBy('counts' , 'desc')
            ->whereIn('user_id', Auth::user()->id())
            ->get();
print_r($items);

希望有所帮助。如果有人有更好的解决方案，请告诉我！

答案 1

偶然发现了同样的问题，并找到了解决方案@http://programmingarehard.com/2013/11/10/eloquent_and_views.html/

class CreateCompaniesView extends Migration 
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement("CREATE VIEW companiesView AS
                        SELECT *,
                        (
                            SELECT GROUP_CONCAT(DISTINCT id SEPARATOR ',')
                            FROM people AS p
                            WHERE p.company_id = c.id
                        ) AS person_ids
                        FROM companies AS c");
    }

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

答案 2

我创建了一个用于创建，重命名和删除视图的包：
https://github.com/staudenmeir/laravel-migration-views

您可以提供查询生成器实例或 SQL 字符串：

use Staudenmeir\LaravelMigrationViews\Facades\Schema;

class CreateOverallReportView extends Migration 
{
    public function up()
    {
        $query = DB::table('user_roles as er')->[...];
        $query = 'SELECT [...] FROM `user_roles` er [...]';

        Schema::createView('views_overall_report', $query);
    }

    public function down()
    {
        Schema::dropView('views_overall_report');
    }  
}