In this blog post, you can explore the concept of MySQL views and explain how to effectively integrate them into Laravel applications.

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" => "Roshan Kumar Jha"

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

    

  ]