query with pivot table

Hi

I need a bit of help querying some data as I am fairly new to eloquent. I am using the Spatie Permissions module which comes with all sorts of fantastic methods to build/query permissions and roles and I have all of that working like a charm. I need to display in my "Manage Administrator", all the admins in the system and I want to have a role column in the datatable. Each user has only one role.

My current query is very simple and only fetches data from the admins table

$data = DB::table('admins')->select(['id', 'name', 'email', 'created_at']);

The admin table

CREATE TABLE `admins` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `email` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `password` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `remember_token` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `admins_email_unique` (`email`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=52
;

The Spatie role table is

CREATE TABLE `roles` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `guard_name` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `description` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4
;

and the Spatie pivot table is like this

CREATE TABLE `model_has_roles` (
    `role_id` INT(10) UNSIGNED NOT NULL,
    `model_type` VARCHAR(191) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `model_id` BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY (`role_id`, `model_id`, `model_type`),
    INDEX `model_has_roles_model_id_model_type_index` (`model_id`, `model_type`),
    CONSTRAINT `model_has_roles_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

The data in the pivot table is

"1",    "App\Models\Admin", "1"

I am using yajra datatables and I is think it better to query all the data before returning the Datatable. The roles could be queried in the datatable declaration but it would generate an extra query per admin which is not great

     public function index(Request $request)
    {

       //checks policy
        $this->authorize('view', Admin::class);
      
        if ($request->ajax()) {

            /****  QUERY ALL THE DATA HERE ****/
            $data = DB::table('admins')->select(['id', 'name', 'email', 'created_at']);
            
            return Datatables::of($data)
                    ->addColumn('action', function($row){
  
                        $actions = "";

                        //checks permission
                        if (Auth::guard('admin')->user()->can('update-admin'))
                        {
                            $actions = '<a href="/admin/admins/'.$row->id.'/edit" class="edit btn btn-primary btn-sm">Edit</a>';
                        }

                        if (Auth::guard('admin')->user()->can('delete-admin'))
                        {
                            $actions .= \Form::open(['route' => ['admin.admins.destroy', $row->id], 'method' => 'DELETE', 'class' => 'form-inline form-delete']);
                            $actions .= \Form::button('Delete', ['type' => 'button', 'class' => 'btn btn-danger btn-xs', 'data-title' => "Delete User", 'data-message' => "Are you sure you want to delete this user ?", 'data-toggle' => "modal", 'data-target' => "#confirmDelete"]);
                            $actions .= \Form::close();
                        }

                        return $actions;
                    })
                    ->rawColumns(['action'])
                    ->make(true);
        }

        return view('admin.admins.index');
  
    }

So I just need a bit of help with eloquent to get this data queried.

In essence I think I need something like Select T1.*, T3.name as role_name from admins as T1 inner join model_has_roles as T2 ON T1.id = T2.model_id inner join roles as T3 ON T3.id = T2.role_id

Cheers