如何在 Laravel 中使用 php artisan 为数据库视图创建迁移？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36287364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create the migrations for database views using php artisan in Laravel?
提问by rc.adhikari
Actually, I have managed to create a sql views for Laravel using PHP Artisan using below step.
实际上，我已经设法使用 PHP Artisan 使用以下步骤为 Laravel 创建了一个 sql 视图。
Step 1. Run below command:
步骤 1. 运行以下命令：
php artisan make:migration create_overall_report_views
Step 2.
第2步。
Open the migration file and add the below code:
打开迁移文件并添加以下代码：
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');
}
}
Step 3. To call and run the SQL Views via Laravel query
步骤 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);
Hope that helps. Please let me know if anyone has better solution!!
希望有帮助。如果有人有更好的解决方案，请告诉我！！
回答by Peon
Stumbled on the same issue and found the solution @ http://programmingarehard.com/2013/11/10/eloquent_and_views.html/
偶然发现了同样的问题并找到了解决方案@ 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");
}
}
回答by julian martinez
You can also try this DB::connection()->getPdo()->exec("your sql query"); it works
你也可以试试这个 DB::connection()->getPdo()->exec("your sql query"); 有用
class CreateCompaniesView extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
DB::connection()->getPdo()->exec("CREATE VIEW companie ...");
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
DB::connection()->getPdo()->exec("DROP VIEW companies ...");
}
}
回答by Jonas Staudenmeir
I've created a package for creating, renaming and dropping views:
https://github.com/staudenmeir/laravel-migration-views
我创建了一个用于创建、重命名和删除视图的包：https:
//github.com/staudenmeir/laravel-migration-views
You can provide a query builder instance or an SQL string:
您可以提供查询构建器实例或 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');
}
}
回答by Ayman Elshehawy
Write your migration like this, with 'CREATE OR REPLACE' in up function:
像这样编写您的迁移，在 up 函数中使用“CREATE OR REPLACE”：
public function up()
{
DB::statement('CREATE OR REPLACE VIEW my_view AS SELECT name FROM users');
}
public function down()
{
DB::statement('DROP VIEW my_view');
}
回答by ehsan khodayar
You have to use laravel eloquent an create the relation between in your model. Forexample: If you have 2 table (roles , users) and the relation between them is many to many you have to create to model Role and User after that in Role Model write the code below: public function users() { return $this->belongaToMany('App/User'); }
您必须使用 laravel eloquent 在模型中创建关系。例如：如果您有 2 个表（角色，用户），并且它们之间的关系是多对多的，那么您必须创建以建模角色和用户，然后在角色模型中编写以下代码： public function users() { return $this- >belongaToMany('应用程序/用户'); }
and in User model:
并在用户模型中：
public function roles()
{
return $this->belongsToMany('App/Role')
}
after that create a new migration and put the table name role_user and put the code below into this:
之后创建一个新的迁移并放置表名 role_user 并将以下代码放入：
public function up()
{
Schema::create('category_post', function (Blueprint $table){
$table->increments('id')->unsigned();
$table->integer('post_id')->unsigned()->index();
$table->integer('category_id')->unsigned()->index();
$table->timestamps();
$table->foreign('category_id')
->references('id')->on('categories')
->onUpdate('cascade')
->onDelete('cascade');$table->foreign('post_id')
->references('id')->on('posts')
->onUpdate('cascade')
->onDelete('cascade');
});
}
Save it and run php artisan migrate. Its finished and now you have many to many relation. After all of this for geting query forexample for related role to a user add your User and Role model to your controler an do like this:
保存并运行 php artisan migrate。它完成了，现在你有了多对多的关系。在所有这些用于获取查询例如用户的相关角色之后，将您的用户和角色模型添加到您的控制器中，如下所示：
public function edit($id){
$user=User::whereId($id)->firstOrFail();
$roles=Role::all()
$selectedRole=$user->roles()->lists('id')->toArray();
return view('your page view',compact($user,$roles,$selectedRole));
}
and to show the selected roles into your view write:
并将所选角色显示到您的视图中：
<select id="role" name="role[]" multiple>
@foreach($roles as $role)
<option value="{!! $role->id !!}" @if(in_array($role->id, $selectedRoles)) selected="selected" @endif >
{!! $role->display_name !!}
</option>
@endforeach
</select>
Its finished.
都结束了。
