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)

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('users.name',  'users.email',  DB::raw('JSON_ARRAYAGG(roles.name) as roles_list'))
    ->join('model_has_roles',  'users.id',  '=',  'model_has_roles.model_id')
    ->join('roles',  'roles.id',  '=',  'model_has_roles.role_id')
    ->groupBy('users.name',  'users.email')
    ->get();

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 as $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>
	@endforeach
@endforeach