How to create the migrations for database views using php artisan in Laravel?

Question

Actually, I have managed to create a sql views for Laravel using PHP Artisan using below step.

Step 1. Run below command:

php artisan make:migration create_overall_report_views 

Step 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

$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!!

Answer 1

Stumbled on the same issue and found the 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");     } }