How Does Laravel 9 Use MySQL View?

You may learn about MySQL views in this blog post and how to successfully incorporate them into Laravel apps.

SQL Create View Query

CREATE VIEW view_data AS

SELECT 

    users.id, 

    users.name, 

    users.email,

    (SELECT count(*) FROM posts

                WHERE posts.user_id = users.id

            ) AS total_posts,

    (SELECT count(*) FROM comments

                WHERE comments.user_id = users.id

            ) AS total_comments

FROM users

SQL Drop View Query

DROP VIEW IF EXISTS `view_data`;

 Let’s create migration with views.

php artisan make:migration create_view

Update Migration File:

<?php

 
use Illuminate\Database\Migrations\Migration;

use Illuminate\Database\Schema\Blueprint;

use Illuminate\Support\Facades\Schema;

  

class CreateView 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 <<

            CREATE VIEW view_data AS

                SELECT 

                    users.id, 

                    users.name, 

                    users.email,

                    (SELECT count(*) FROM posts

                                WHERE posts.user_id = users.id

                            ) AS total_posts,

                    (SELECT count(*) FROM comments

                               WHERE comments.user_id = users.id

                            ) AS total_comments

                FROM users

            SQL;

    }

    /**

     * Reverse the migrations.

     *

     * @return void

     */

    private function dropView(): string

    {

        return <<

            DROP VIEW IF EXISTS `view_data`;

            SQL;

    }

}

now we will create model as below:

app/ViewData.php

<?php

 
namespace App;

use Illuminate\Database\Eloquent\Model;

 
class ViewUserData extends Model

{

    public $table = "view_data";

}

Now we can use it as below on the controller file:

<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\ViewData;

  

class UserController extends Controller

{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */

    public function index()
    {
        $users = ViewData::select("*")
                        ->get()
                        ->toArray();   
        dd($users);

    }

}

you can see output:-

array:20 [

  0 => array:5 [

    "id" => 1

    "name" => "abhishek singh"

    "email" => "abhisheksingh.cotocus@gmail.com"

  ]

Abhishek Singh

I’m Abhishek, a DevOps, SRE, DevSecOps, and Cloud expert with a passion for sharing knowledge and real-world experiences. I’ve had the opportunity to work with Cotocus and continue to contribute to multiple platforms where I share insights across different domains:

• DevOps School – Tech blogs and tutorials

• Holiday Landmark – Travel stories and guides

• Stocks Mantra – Stock market strategies and tips

• My Medic Plus – Health and fitness guidance

• TrueReviewNow – Honest product reviews

• Wizbrand – SEO and digital tools for businesses

I’m also exploring the fascinating world of Quantum Computing.