3 way relationship

Hi all, I'm working on a small inhouse app to manage an authorization matrix we need for ISO requirements. We need to manage documentation on all apps, and which users/roles have access to what application and with what level. I have the following tables:

Users
Roles (A user has many Roles) (Things like CEO, Developer, Operations, etc)
Applications
Permissions (things like "admin", "user", "none" to keep it simple)

So there is a user-roles relationship, and then an application-permission-role 3 way relationship.

What I'd like is on the Application Resource, be able to edit which Roles have what permissions. I did kind of solve it. What I did was create a relationship on Role only, and added the Permission as an additional pivot column. It works, but it feels a bit clunky. I also tried using a Repeater Form, which also does somewhat work, but I liked it even less. I'll add my current RolesRelationManager on the ApplicationResource:

        return $table
            ->recordTitleAttribute('slug')
            ->columns([
                TextColumn::make('slug')
                    ->searchable(),
                TextColumn::make('pivot.permission.name')
            ])
            ->filters([
                //
            ])
            ->headerActions([
                AttachAction::make()
                    ->label('Authorize')
                    ->schema(fn (AttachAction $action): array => [
                        $action->getRecordSelect(),
                        Select::make('permission_id')
                            ->label('Permission')
                            ->searchable()
                            ->options(fn () => Permission::orderBy('name')->pluck('name', 'id'))
                            ->required(),
                    ])
                    ->preloadRecordSelect()
                    ->modalSubmitActionLabel('Authorize')
            ])
            ->recordActions([
                DetachAction::make()
                    ->label('Deauthorize')
            ]);

How would you solve this? Is there a better way?

And as a second question. I need to generate a page where we basically see the full Matrix. So Y column would be apps, and X column is Roles, with each cell being the permission. Can this be done as a page?

Thanks

LaryAI

5 months ago

Level 58

Certainly! This is a classic use-case for a pivot table with extra columns, often referred to as a "many-to-many-to-many" relationship, which Laravel models with a many-to-many pivot table and additional columns. Let's address your core questions:

1. Modeling the 3-way Relationship

You want to assign a Role a specific Permission on an Application. The setup you're using (a pivot table on application_role with a permission_id column) is the clean, idiomatic way to solve this in Laravel.

Table Structure

Your pivot table should look like:

application_role
----------------
id
application_id
role_id
permission_id

Other ways (like "repeaters") would be more cumbersome and less native to relational databases.

Example Laravel Morphology

Application Model:

public function roles()
{
    return $this->belongsToMany(Role::class)
        ->withPivot('permission_id')
        ->withTimestamps();
}

Role Model:

public function applications()
{
    return $this->belongsToMany(Application::class)
        ->withPivot('permission_id')
        ->withTimestamps();
}

When you want to attach/edit the permission a role has to an application, you update this pivot table.

Attaching a role with permission to application:

$application->roles()->attach($roleId, ['permission_id' => $permissionId]);

$application->roles()->sync([$roleId => ['permission_id' => $permissionId]]);

Your current approach — using Filament's AttachAction to allow picking a role and a permission for the pivot — is solid, and widely used for this situation.

2. Displaying The Full Matrix of Applications × Roles

Yes, you can absolutely generate a page to show a matrix of Applications vs. Roles, with each cell showing the Permission.

How To Query The Data

You want to fetch all applications, all roles, and the pivot info for each combination. A simple way is to eager-load the data:

$apps = Application::with(['roles' => function($q) {
    $q->withPivot('permission_id');
    $q->with('permissions'); // If you want the permission details
}])->get();

$roles = Role::all();
$permissions = Permission::pluck('name', 'id');

Example Blade Table

<table>
    <thead>
        <tr>
            <th>Application</th>
            @foreach($roles as $role)
                <th>{{ $role->name }}</th>
            @endforeach
        </tr>
    </thead>
    <tbody>
    @foreach($apps as $app)
        <tr>
            <td>{{ $app->name }}</td>
            @foreach ($roles as $role)
                <td>
                    @php
                        $pivot = $app->roles->firstWhere('id', $role->id)?->pivot;
                    @endphp
                    {{ $pivot ? $permissions[$pivot->permission_id] : 'none' }}
                </td>
            @endforeach
        </tr>
    @endforeach
    </tbody>
</table>

This will give you a matrix where each cell is the permission that a role has for a given application, or none if not assigned.

Summary

Your current solution using a pivot on application_role with permission_id is the best & recommended method for the scenario.
Displaying the matrix is just fetching all apps and roles, then checking the pivot for each cell.

If you want to make the matrix editable (change permissions inline), consider using Livewire or Filament's Table/Repeater component for a UX upgrade!

Let me know if you want more concrete code for a specific admin panel/CRUD system (Filament, Livewire, etc)!