gpt4 book ai didi

php - 如何在 Laravel 中使用 php artisan 创建数据库 View 的迁移?

转载 作者:IT王子 更新时间:2023-10-28 23:55:28 24 4
gpt4 key购买 nike

实际上,我已经通过以下步骤使用 PHP Artisan 为 Laravel 创建了一个 sql View 。

第 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 View

$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);

希望对您有所帮助。如果有人有更好的解决方案,请告诉我!!

最佳答案

偶然发现了同样的问题并找到了解决方案@ 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");    }}

关于php - 如何在 Laravel 中使用 php artisan 创建数据库 View 的迁移?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36287364/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com