How to create a migration to view the database using php artisan in Laravel?

Question

In fact, I was able to create sql views for Laravel using PHP Artisan using the next step.

Step 1. Run the command below:

php artisan make:migration create_overall_report_views 

Step 2

Open the migration file and add the following 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. Calling and starting SQL views through a Laravel query

 $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 this helps. Please let me know if anyone has a better solution!

Answer 1

I came across the same problem and found a solution @ 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");
     }
 } 

Answer 2

You can also try this DB :: connection () → getPdo () → exec ("your SQL query"); it works

 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 ..."); } } 

Answer 3

I created a package to create, rename and delete views:
https://github.com/staudenmeir/laravel-migration-views

You can provide an instance of the query builder or an SQL string:

 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'); } } 

Answer 4

You must use laravel eloquent to create a connection between your model. For example: If you have 2 tables (roles, users), and the relationship between them is many for many, you need to create a Role and User for the model, and then write the code below in the Role model: users of public functions () {return $ this- > ownaToMany ('App / User'); }

and in the user model:

 public function roles() { return $this->belongsToMany('App/Role') } 

after that create a new migration and put the name of the table role_user and put the code below:

 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 over, and now you have a lot of relationships. After all this, to get the forexample request for the associated role to the user, add your User and Role model to your controller, do like this:

  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 in 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> 

Done.