Teams: Get all roles with count of users.

[jackthedev]

Hello,

I am currently building a SaaS application where a team can have multiple roles, this package seemed like a perfect fit for my use case.

I can get out all the roles for a specific team with the following eloquent query:

$roles = Role::where('team_id', auth()->user()->team_id)->get();

As soon as I try to eager load the users with the roles like this:

$roles = Role::where('team_id', auth()->user()->team_id)->with('users')->get();

I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'team_id' in where clause is ambiguous (SQL: select users.*, model_has_roles.role_id as pivot_role_id, model_has_roles.model_id as pivot_model_id, model_has_roles.model_type as pivot_model_type from users inner join model_has_roles on users.id = model_has_roles.model_id where model_has_roles.role_id in (1, 2) and model_has_roles.model_type = App\Models\User and team_id = 1)

I am not sure if this is an issue with the package or If I have misconfigured something when I set up the team's part on installation.

Any help is greatly appreciated.

[erikn69]

I tried $roles = Role::where('team_id', MY_TEAM_ID)->with('users')->get();
and everything works as expected, maybe you broke something on your implementation

Looks at this

laravel-permission/src/Models/Role.php

Lines 72 to 81 in 6a3ed62

	public function users(): BelongsToMany
	{
	return $this->morphedByMany(
	getModelForGuard($this->attributes['guard_name']),
	'model',
	config('permission.table_names.model_has_roles'),
	PermissionRegistrar::$pivotRole,
	config('permission.column_names.model_morph_key')
	);
	}

there is no team_id = 1) on relationship, you did add that on some place and them it is ambiguous because you did add team_id on user model, add table name for that where for fix that

Also why put team_id on user? auth()->user()->team_id
It must be a relationship many to many, same user can be on many teams #1935

public function teams()
{
    return $this->belongsToMany(Teams::class, 'team_has_users');
}

[jackthedev]

I am using a multitenancy global scope on my users model causing the team_id to be part of the eloquent query when calling with('users'); so that's what is causing the problem. Your answer helped me solve that, thank you.

team_id is on the user because a user can only belong to one team in this system.