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/

Answer 2

To achieve this there is a good article here
I will show some code from the article .
Alter a base class like following:

public function save(array $options = [])
{
    $this->toWriteMode();

    try {
        $saved = parent::save($options);
    } catch (\Exception $e) {
        $this->toReadMode();
        throw $e;
    }

    $this->toReadMode();

    return $saved;
}


protected $readOnly = [];

    protected $readOnlyCache = [];

    public function save(array $options = [])
    {
        $this->toWriteMode();
        $this->cacheReadOnly();

        try {
            $saved = parent::save($options);
        } catch (\Exception $e) {
            $this->toReadMode();
            throw $e;
        }

        $this->toReadMode();
        $this->restoreReadOnly();

        return $saved;
    }

    protected function cacheReadOnly()
    {
        $this->readOnlyCache = [];

        foreach ($this->readOnly as $key) {
            $value = $this->getAttributeValue($key);
            $this->readOnlyCache[$key] = $value;
            $this->__unset($key);
        }
    }

    protected function restoreReadOnly()
    {
        foreach ($this->readOnlyCache as $key => $value) {
            $this->setAttribute($key, $value);
        }
    }

Create Employee model as follows:

class Employee extends BaseModel
{
    protected $table = 'employees';

    protected $fillable = ['name'];

    protected $guarded = ['id'];

    public function people()
    {
        return $this->hasMany('Person');
    }
} 

Create EagerEmployee class as follows:

class EagerEmployee extends Employee
{
    protected $readFrom = 'employeeView'; //Use your view name

    protected $readOnly = ['person_ids'];

    public function getPersonIdsAttribute($ids)
    {
        return $this->intArrayAttribute($ids);
    }
} 

This class will read its data from the view and we can save and retrieve it as normal. It will fetch read only attributes and they will be handled appropriately when saving.

That new intArrayAttribute() method just converts the comma delimited id string returned from the view into an array of integers.

We can use the Employee internally but if we need those extra read only attributes, say in an api response, we can use the EagerEmployee class.

P.S. The above code is copied from the given article and changed according to your needs.

Update: Since old link to article is broken I'm adding a link to cached page of the site.
New Link to Article
Old Link to Article

Answer 3

'options'   => [
            \PDO::ATTR_EMULATE_PREPARES => true
]

Add Code to "config/database.php" see picture below Show Example