Using multiple Roles generated by Spatie's Laravel-permission package to display in a Livewire table

If you have got spatie/laravel-permission package installed, and are looking for displaying the list of roles that are assigned to a user, in your Livewire table, read on… (if you are looking for a simple guide to set up a Livewire table, read this)

The table cell showing the multiple roles stylized as pills

Preparing the database query

Below is the Query that you would list the Roles that are assigned to a user. The roles_list column will contain a list of Roles in an array. The MariaDB function JSON_ARRAYAGG() would require version 10.5.x.

$users  =  DB::table('users')
    ->select('',  '',  DB::raw('JSON_ARRAYAGG( as roles_list'))
    ->join('model_has_roles',  '',  '=',  'model_has_roles.model_id')
    ->join('roles',  '',  '=',  'model_has_roles.role_id')
    ->groupBy('',  '')

Note: JSON_ARRAYAGG() function requires 10.5.x. You may want to have stable MariaDB version 10.5.21-GA installed. Refer to this article if you need to upgrade MariaDB.

Display the array elements in a blade

One you have got the roles list, displaying them in the table is straight forward.

@foreach ($users and $user)
	@foreach (json_decode($user->roles_list)  as  $roles)
		<span class="bg-green-200 text-green-600 py-1 px-2 mx-1 rounded-full text-xs">{{  $roles  }}</span>