The relationship between three columns in Laravel: Roles, Teams, and Projects.

Question

<p>In Laravel, I have a relationship between teams, projects, users, roles and permissions. <br /><br />Users can have multiple teams, a team can have multiple projects, and users can have user roles, team roles, and project roles. I want to know how to get a user's permissions per project, per team, and only for the user. </p><p><br /></p> <p><strong>User table</strong></p> <ul> <li>Id</li> <li>name</li> <li>current_project_id</li> <li>current_team_id</li> <li>role_id</li> <li>etc</li> </ul> <p><strong>Teams table</strong></p> <ul> <li>id</li> <li>name</li> </ul> <p><strong>Projects table</strong></p> <ul> <li>id</li> <li>name</li> <li>description</li> </ul> <p><strong>Roles table</strong></p> <ul> <li>id</li> <li>name</li> <li>type(user,project,team)</li> </ul> <p><strong>Permissions table</strong></p> <ul> <li>id</li> <li>name</li> </ul> <p><strong>Role_permission table</strong></p> <ul> <li>permission_id</li> <li>role_id</li> </ul> <p><strong>project_user table</strong></p> <ul> <li>project_id</li> <li>user_id</li> <li>role_id</li> </ul> <p><strong>team_user table</strong></p> <ul> <li>team_id</li> <li>user_id</li> <li>role_id</li> </ul> <p>A user can only have one role in a project, only one role in a team, and a user himself can only have one role, which can be an administrator or an ordinary user.

</p><p><br /></p> <pre class="brush:php;toolbar:false;">class User extends Authenticatable { public function projectRole() { //I can't find the way to insert project_id like in where, because current_project_id is null in boot return $this->belongsToMany(Role::class, 'project_user')->where('project_id', '=', $this->current_project_id)->first(); } public function projectPermissions() { return $this->projectRole()->permissions; } public function permissions() { //I can't find the way to get all team permission, project permissions and user permissions } public function role() : BelongsTo { //Only can be User role, Admin or Support return $this->belongsTo(Role::class); } public function userPermissions() { return $this->role->permissions; } } class Role extends Model { public function permissions() { return $this->belongsToMany(Permission::class); } }</pre> <p>I want to use permissions as a threshold to pass to the Inertia frontend, I'm trying something like this. </p> <pre class="brush:php;toolbar:false;">Gate::before(function ($user, $permission) { return $user->projectPermissions($user->currentProject)->contains($permission) || $user->teamPermissions($user->currentTeam)->contains($permission) || $ user->teamPermissions($user->currentTeam)->contains($permission) || $user->userPermissions()->contains($permission); });</pre> <p><br /></p>

Answer 1

Based on your explanation, I did it as follows:

Table******************

project_user table:

Schema::create('project_user', function (Blueprint $table) {
    $table->unsignedBigInteger('project_id');
    $table->unsignedBigInteger('user_id');
});

permission_role table:

Schema::create('permission_role', function (Blueprint $table) {
    $table->unsignedBigInteger('permission_id');
    $table->unsignedBigInteger('role_id');
});

role_user table:

Schema::create('role_user', function (Blueprint $table) {
    $table->unsignedBigInteger('role_id');
    $table->unsignedBigInteger('user_id');
});

Models *************

User model:

/**
 * Team Relationship
 *
 * @return BelongsToMany
 */
public function teams(): BelongsToMany
{
    return $this->belongsToMany(Team::class);
}

/**
 * Project Relationship
 *
 * @return BelongsToMany
 */
public function projects(): BelongsToMany
{
    return $this->belongsToMany(Project::class);
}

/**
 * Role Relationship
 *
 * @return BelongsToMany
 */
public function roles(): BelongsToMany
{
    return $this->belongsToMany(Role::class, 'role_user');
}

Team Model:

/**
 * User Relationship
 *
 * @return BelongsToMany
 */
public function users(): BelongsToMany
{
    return $this->belongsToMany(User::class);
}

/**
 * Project Relationship
 *
 * @return HasMany
 */
public function projects(): HasMany
{
    return $this->hasMany(Project::class);
}

Project Model:

/**
 * Team Relation
 *
 * @return BelongsTo
 */
public function team(): BelongsTo
{
    return $this->belongsTo(Team::class);
}

/**
 * User Relation
 *
 * @return BelongsToMany
 */
public function users(): BelongsToMany
{
    return $this->belongsToMany(User::class);
}

Role Model:

/**
 * Permission Relation
 *
 * @return BelongsToMany
 */
public function permissions(): BelongsToMany
{
    return $this->belongsToMany(Permission::class, 'permission_role');
}

/**
 * User Relation
 * 
 * @return BelongsToMany
 */
public function users(): BelongsToMany
{
    return $this->belongsToMany(User::class,'role_user');
}

Permission Model:

/**
 * Role Relation
 *
 * @return BelongsToMany
 */
public function roles(): BelongsToMany
{
    return $this->belongsToMany(Role::class, 'permission_role');
}

First, we create the roles we expect. For example:

Role 1: Name = Super Administrator, Type = User

Role 2: Name = Team Director, Type = Team

Role 3: Name = Project Development user, type=project

Now we assign the desired role to our user. These roles are stored in the role_user table.

Then, based on these roles, you can determine each user's responsibilities within each project and team.