accessing Spatie User roles in the table

[carbtest]

I like many others use spatie/laravel-permission library and am struggling being abel to access the roles in the UserTable.
what is the best approach to retrieve them.

Column::make('Roles','roles.name')->eagerLoadRelations()->collapseOnTablet()->deselected(), does not work but the $user->roles will return using tinker

anyone have suggestions on how to access the roles in the user table table?

[carbtest]

Here is how I accomplished it, not sure if there is a better way

Column::make('Roles', 'id')
    ->format(fn ($value) => implode(", ", DB::table('model_has_roles')
        ->where('model_type', 'App\Models\User')
        ->where('model_id', $value)->join('roles', 'roles.id', '=', 'model_has_roles.role_id')
        ->get()
        ->pluck('name')->toArray())
    ) 
    ->sortable(),

[lrljoe]

Okay, with your approach, what you're doing is actually an additional DB query per-row. and it's really not best practice to be using DB in a column, as it will definitely introduce some pain when you get to larger sized tables/pagination.

At current you have:

Column::make('Roles', 'id')
    ->format(fn ($value) => implode(", ", DB::table('model_has_roles')
        ->where('model_type', 'App\Models\User')
        ->where('model_id', $value)->join('roles', 'roles.id', '=', 'model_has_roles.role_id')
        ->get()
        ->pluck('name')->toArray())
    ) 
    ->sortable(),

Are you using the automated approach, or do you have a builder() function in your Table Component? If you're using the builder() component (I'd usually recommend this for anything beyond a basic table), then you an use the standard Eloquent builder functions to include relations etc.

Sharing the full Table Component will be useful, or if you're on the Rappasoft Discord, then feel free to DM it to me if you have privacy concerns.

[carbtest]

I knew it was causing extra queries, but because Spatie uses morphMany i don't think it is supported. here is my Component

<?php

namespace App\Http\Livewire;

use App\Models\User;
use App\Models\Status;
use App\Exports\UsersExport;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Database\Eloquent\Builder;
use Rappasoft\LaravelLivewireTables\Views\Column;
use Rappasoft\LaravelLivewireTables\DataTableComponent;
use Rappasoft\LaravelLivewireTables\Views\Columns\LinkColumn;
use Rappasoft\LaravelLivewireTables\Views\Columns\BooleanColumn;
use Rappasoft\LaravelLivewireTables\Views\Columns\ButtonGroupColumn;

class UsersTable extends DataTableComponent
{
    protected $model = User::class;
    public $tablename;
    public $status;

    public function configure(): void
    {
        // $this->tablename = (new $this->model)->getTable();
        $this->tablename = 'users';
        
        $this->setPrimaryKey('id');
        $this->setTableWrapperAttributes([
            'class' => 'dark:overflow-auto ',
        ]);
        $this->setConfigurableAreas([
            'toolbar-right-start' => [
                'components.tables.add-model-button', [
                    'model' => $this->tablename
                ]
            ],
        ]);
        $this->setSingleSortingDisabled();
    }

    public function columns(): array
    {
        return [
            Column::make('Status', 'status.name')
            ->format(
                fn ($value, $row, Column $column)  => view('components.tables.column-user-status')->withValue($value)
                // function($value, $row , Column $column){
                //     if($value == 'Active')
                //     {
                //         return "<x-ct-icon name='home'/>";
                //     }
                // }
            )->eagerLoadRelations()->html(),
            Column::make('ID', 'id')
                ->sortable()
                ->format(
                    function($value, $row , Column $column){
                        if($value )
                        {
                            $src = $row->avatarUrl();
                            return "<img class='w-20 h-20 rounded-full'  alt='profile-photo' src='{$src}'/>";
                        }
                    }
                )->eagerLoadRelations()->html()->deselected() ,
            Column::make('Display Name', 'name')
                ->sortable()->searchable()->setSortingPillTitle('Full Name'),
            // Column::make('First Name','info.f_name')->collapseOnMobile()  
            //     ->sortable(),
            // Column::make('Last Name','info.l_name')->collapseOnMobile() 
            //     ->sortable(),
            Column::make('Company','info.company')->collapseOnMobile() 
                ->sortable(),
            Column::make('Email')->sortable()->searchable(),
            // BooleanColumn::make("hasRole('Admin')")->yesNo(),//doesn't work
            Column::make('Invited By','invited_by') ,
            // Column::make('Phones','phones')->eagerLoadRelations() ,
            // Column::make('Mobile Phone','mobilePhone.number')->collapseOnTablet(),
            // Column::make('Work Phone','workPhone.number')->collapseOnTablet(),
            
            Column::make('Roles', 'id')
                ->format(fn ($value) => implode(", ", DB::table('model_has_roles')
                    ->where('model_type', 'App\Models\User')
                    ->where('model_id', $value)->join('roles', 'roles.id', '=', 'model_has_roles.role_id')
                    ->get()
                    ->pluck('name')->toArray())
                ) 
                ->sortable(),

            Column::make('Last login', 'last_login')
                ->sortable()
                ->format(fn ($value, $row, Column $column) => !empty($value) ? \Carbon\Carbon::parse($value)->diffForHumans() : ''),
            Column::make('Updated', 'updated_at')
                ->sortable()
                ->format(fn ($value, $row, Column $column) => $value->diffForHumans())
                ->deselected(),
            Column::make('Created', 'created_at')
                ->sortable()
                ->format(fn ($value, $row, Column $column) => $value->diffForHumans())->deselected(),

            Column::make('Actions')
            ->label(
                fn ( $row, Column $column) => view('components.tables.actions-button-group')->withValue($row, $this->tablename)
            ),  
        ];
    }



    public function bulkActions(): array
    {
        return [
            'export'                   => 'Export Selected',
            'updateStatus("Active")'   => 'Activate users',
            'updateStatus("Inactive")' => 'Disable users',
            'updateStatus("Banned")'   => 'Ban users',
            // 'disable'                  => 'Disable User',
        ];
    }
   
    public function updateStatus($newStatus)
    {
       ...

    }

    public function export()
    {
        $users = $this->getSelected();

        $this->clearSelected();

        return Excel::download(new UsersExport($users), 'users.xlsx');
    }

    public function delete(User $user)
    {
        if($user->id != 1){
            $deleted = $user->delete();
        }else {
            return "Cannot delete me";
        }
    }
}

[lrljoe]

So morphMany isn't supported in terms of automatically detecting and joining the relation properly.

It is however supported if you use the builder() approach.

I'll try to give an example in the next day or two, but the docs do explain using builder() as far as I can recall

You can then use format or label with the related data loaded in

I use that approach for loading in related teams/roles/permissions (albeit not with Spatie but with another package, but still morphMany)

[carbtest]

so i was able to write the builder

public function builder() :Builder
    {
        $query =  User::query()
            ->select('users.*', DB::raw( 'group_concat(roles.`name`) as userroles'))
            ->join('model_has_roles','model_has_roles.model_id', '=', 'users.id')
            ->join('roles' ,'roles.id', '=','model_has_roles.role_id')
            ->groupBy('users.id') ;
        info($query->toSql())    ;

        return $query;
    }

which logs this sql

select
`users`.*,
group_concat(roles.`name`) as userroles
from `users`
inner join `model_has_roles` on `model_has_roles`.`model_id` = `users`.`id`
inner join `roles` on `roles`.`id` = `model_has_roles`.`role_id`
group by `users`.`id`

and in my columns() i have this

...
 Column::make('Roles', 'userroles'), 
...

but i receive the error message

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.userroles' in 'field list'
SELECT
count() AS aggregate
FROM
(
SELECT
`users`.,
group_concat(roles.`name`) AS userroles,
`statuses`.`name` AS `status.name`,
`users`.`id` AS `id`,
`users`.`name` AS `name`,
`user_info`.`company` AS `info.company`,
`users`.`email` AS `email`,
`users`.`invited_by` AS `invited_by`,
`users`.`last_login` AS `last_login`,
`users`.`updated_at` AS `updated_at`,
`users`.`created_at` AS `created_at`,
`users`.`userroles` AS `userroles`
FROM
`users`
INNER JOIN `model_has_roles` ON `model_has_roles`.`model_id` = `users`.`id`
INNER JOIN `roles` ON `roles`.`id` = `model_has_roles`.`role_id`
LEFT JOIN `statuses` ON `statuses`.`id` = `users`.`status_id`
LEFT JOIN `user_info` ON `user_info`.`user_id` = `users`.`id`
GROUP BY
`users`.`id`
) AS `aggregate_table`

not sure what is wrong with my builder because it is applying the alias incorrectly and adding extra columns?

[carbtest]

I also have tried to add a function to the model User::class

public function userroles()
    {
        return User::where('users.id', $this->id)
            ->selectRaw('group_concat(roles.`name`) as userroles')
            ->join('model_has_roles', 'model_has_roles.model_id', '=', 'users.id')
            ->join('roles', 'roles.id', '=', 'model_has_roles.role_id')
            ->groupBy('users.id')->first();
    }

but am unsure how to return the function as a column in component and couldn't find it in the docs
Column::make('Roles', 'userroles()'), does not work