How to Use MySQL View in Laravel?

Hi, in this tutorial, I will share, how to create MySQL view using laravel and how to use it. You can use any laravel version including laravel 6, laravel 7, laravel 8, laravel 9, and laravel 10. So, let's start

Basically, we are using SQL view because we don't need to write a long query on our database repeatedly. If we create the view, we can use it and quickly get data from the view table.

1. To use MySQL views in Laravel, you can follow these steps:

Create the MySQL view in your database using your preferred MySQL client. Here's an example of a MySQL view that retrieves the total number of orders per day:

CREATE VIEW orders_per_day AS
SELECT DATE(created_at) as order_date, COUNT(*) as total_orders
FROM orders
GROUP BY DATE(created_at);

2. Create view using migration: 

php artisan make:migration create_orders_per_day_view

3. Update migration: 

<?php
  
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
  
class CreateOrdersPerDayView extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        \DB::statement($this->createView());
    }
   
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        \DB::statement($this->dropView());
    }
   
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    private function createView(): string
    {
        return <<<SQL
	      CREATE VIEW orders_per_day AS
		SELECT DATE(created_at) as order_date, COUNT(*) as total_orders
		FROM orders
		GROUP BY DATE(created_at)
            SQL;
    }
   
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    private function dropView(): string
    {
        return <<

            DROP VIEW IF EXISTS `orders_per_day`;
            SQL;
    }
}

4. In Laravel, create a new model for the MySQL view. This model should extend the Illuminate\Database\Eloquent\Model class and specify the name of the MySQL view as the $table property. Here's an example of a OrdersPerDay model for the orders_per_day MySQL view:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class OrdersPerDay extends Model
{
    protected $table = 'orders_per_day';
}

5. Now you can use the OrdersPerDay model in your Laravel application to retrieve data from the MySQL view. Here's an example of how to retrieve the total number of orders for a specific date:

$ordersPerDay = OrdersPerDay::where('order_date', '2022-03-22')->first();

echo "Total orders on 2022-03-22: " . $ordersPerDay->total_orders;

6. Using Controller

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\OrdersPerDay;
  
class OrderController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $ordersPerDay = OrdersPerDay::where('order_date', '2022-03-22')->first();

        echo "Total orders on 2022-03-22: " . $ordersPerDay->total_orders;
    }
}

Hope, it will help you.