laraval mysql view

create_field_managers_view.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $this->down();
        DB::statement("
          CREATE VIEW view_field_managers AS
          (
            select
                u.id,
                u.username,
                r.name as role,
                replace(
                    concat_ws(
                        ' ',
                        i.first_name,
                        i.middle_name,
                        i.last_name
                    ),
                    '  ',
                    ' '
                ) as full_name,
                i.first_name,
                i.middle_name,
                i.last_name,
                l.created_at as last_log_in,
                u.status
            from
                users u,
                user_info i,
                roles r,
                role_user ru,
                (select *
                from user_logs l
                where created_at =
                (select max(created_at)
                from user_logs
                where l.user_id = user_id and activity = 'Logged In')) l
            where
                u.id = i.user_id
                and u.id = ru.user_id
                and ru.role_id = r.id
                and u.id = l.user_id
                and r.name = 'Field Manager'
            order by id
          )
        ");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement('DROP VIEW IF EXISTS view_field_managers');
    }
};