Mysql View in Laravel 5.2

Question

This might be simple for someone. But for me I am totally lost. Can anyone give me a heads up for using Mysql View in Laravel 5. I've been searching for relevant post for a while but not a clue except:

DB::statement("Create View")

DB::statement("Drop View")

But this doesn't ring a bell. Any help, any clue, any guide is appreciated. Thank in advance

My Scenario

I have an employee table with other tables that holds various attributes of the employee separately such as Appointment, posting,health, family etc etc. Most of these tables has one property Is_current to represent the current record of the employee. So whenever I want to display employee profile with latest record or retrieve some latest record from some of these tables, I don't want to retrieve from each an every table one by one. I just want to compile the latest record in a view and retrieve from it whenever I want.

I hope you understand my requirements and sorry for my bad english

Answer 1

I use views all the time for reporting purposes as I can create a denormalized View and then use the power of Models with Scopes and mutators. I wrote an article on how I manage MySQL Views.

# Create a new migration
php artisan make:migration create_employees_record_view

# Update the migration
<?php

use Illuminate\Database\Migrations\Migration;

class CreateEmployeesRecordView extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        \DB::statement("
            CREATE VIEW employees_records 
            AS
            SELECT
                employees.emp_no,
                employees.first_name,
                employees.last_name,
                employees.gender,
                employees.hire_date,
                employees.birth_date,
                dept_emp.dept_no,
                departments.dept_name,
                mananger.emp_no AS manager_emp_no,
                mananger.first_name AS manager_first_name,
                mananger.last_name AS manager_last_name
            FROM
                employees
                LEFT JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
                LEFT JOIN departments ON dept_emp.dept_no = departments.dept_no
                LEFT JOIN dept_manager ON departments.dept_no = dept_manager.dept_no
                LEFT JOIN employees mananger ON dept_manager.emp_no = mananger.emp_no;
        ");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
    }
}

# Run the migration
php artisan migrate

Managing it via Console Command

php artisan make:command CreateOrReplaceEmployeeRecordsViewCommand

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;

class CreateOrReplaceEmployeeRecordsViewCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'view:CreateOrReplaceEmployeeRecordsView';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Create or Replace SQL View.';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        \DB::statement("
            CREATE OR REPLACE VIEW employees_records 
            AS
            SELECT
                employees.emp_no,
                employees.first_name,
                employees.last_name,
                employees.gender,
                employees.hire_date,
                employees.birth_date,
                dept_emp.dept_no,
                departments.dept_name,
                mananger.emp_no AS manager_emp_no,
                mananger.first_name AS manager_first_name,
                mananger.last_name AS manager_last_name
            FROM
                employees
                LEFT JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
                LEFT JOIN departments ON dept_emp.dept_no = departments.dept_no
                LEFT JOIN dept_manager ON departments.dept_no = dept_manager.dept_no
                LEFT JOIN employees mananger ON dept_manager.emp_no = mananger.emp_no;
        ");
    }
}

Using Model To View # Create a new model php artisan make:model EmployeesRecord

<?php
namespace App;

use Illuminate\Database\Eloquent\Model;

class EmployeesRecord extends Model
{
}

Test out the newly created Model

# For this we will be using tinker
php artisan tinker

>>> $e = \App\EmployeesRecord::first();
=> App\EmployeesRecord {#2885
     emp_no: 10001,
     first_name: "Georgi",
     last_name: "Facello",
     gender: "M",
     hire_date: "1986-06-26",
     birth_date: "1953-09-02",
     dept_no: "d005",
     dept_name: "Development",
     manager_emp_no: 110511,
     manager_first_name: "DeForest",
     manager_last_name: "Hagimont",
   }
>>> $e = \App\EmployeesRecord::where('emp_no', 10003)->first();
=> App\EmployeesRecord {#2896
     emp_no: 10003,
     first_name: "Parto",
     last_name: "Bamford",
     gender: "M",
     hire_date: "1986-08-28",
     birth_date: "1959-12-03",
     dept_no: "d004",
     dept_name: "Production",
     manager_emp_no: 110303,
     manager_first_name: "Krassimir",
     manager_last_name: "Wegerle",
   }

Ref - http://blog.tekz.io/laravel-eloquent-how-to-effectively-manage-sql-views/