Retrieve Only Specified Roles for Users Within Each Team in Laravel

I'm implementing a roles and permissions system that includes both team-specific and user-specific roles. I need to list all teams a user belongs to, along with the roles assigned to the user within each team. The data structure includes:

Table model_has_roles:







Table roles:

For example, if a user with ID 1 should only have roles with IDs 4 and 5 in specific teams, I'd like to ensure only those roles appear in the results.

Additionally, if this user belongs to multiple teams, any roles assigned to the user within each team should be included in the results.

Code Structure

I attempted the following relationships:

Team model:

public function userRoles(): BelongsToMany
{
return $this->belongsToMany(Role::class, 'model_has_roles', 'team_id', 'role_id')->distinct();
}


User model:

public function teams(): BelongsToMany
{
return $this->belongsToMany(Team::class, 'model_has_roles', 'model_id', 'team_id')->distinct();
}

public function teamsWithRoles(): Collection
{
$this->loadMissing(['teams', 'teams.userRoles']);

return $this->teams;
}


Current Check Running the following check:

$user->teamsWithRoles()->pluck('userRoles', 'id')->map(fn($collection) => $collection->pluck('name', 'id'))->dd()


Produces:

{"1":{"4":"user","8":"user"},"2":{"5":"system"}}


Here, the result includes an unexpected role (ID 8). I only want role IDs 4 and 5 to be associated with this user for the specified teams.

Continue reading...