The Eloquent Many Many choose the User without specific Roles

Question

So, I have User and Role models with many-to-many relationships, I have 3 roles: super , admin and moderator with 4 users who say: John , Mike , James and Larry .

John is super , Mike has admin and moderator roles, James is admin , and Larry is moderator . To display users who do not have specific roles, I created this area:

 public function scopeDoesntHaveRoles($query, $roles = [], $column = 'id') { return $query->whereDoesntHave('roles')->orWhereHas('roles', function ($q) use ($roles, $column) { $q->whereNotIn($column, $roles); }); } 

When I call User::doesntHaveRoles([1])->lists('name', 'id') to get users who don't have the super role, it works and returns:

 {"2":"Mike","3":"James","4":"Larry"} 

But when I try to list users who do not have admin role User::doesntHaveRoles([2])->lists('name', 'id') , yes James does not appear there, but Mike appears when he actually has a role admin :

 {"1":"John","2":"Mike","4":"Larry"} 

I think because Mike also has a moderator role, do you see something wrong in my area? or do you have other solutions?

thanks

Edit: Here is my summary diagram

 Schema::create('user_roles', function (Blueprint $table) { $table->integer('user_id')->unsigned(); $table->integer('role_id')->unsigned(); $table->primary([ 'user_id', 'role_id' ]); }); 

User model

 public function roles() { return $this->belongsToMany(Role::class, 'user_roles'); } 

Role Model

 public function users() { return $this->belongsToMany(User::class, 'user_roles'); } 

Answer 1

I would use whereNotIn instead of whereDoesntHave .

Given the Role stored in the $role variable, you can get all users who do not have this role:

 /* @var Role $role */ User::whereNotIn(function('id', $query) use ($role) { $query->select('user_id') ->from('user_roles') ->where('role_id', $role->id); }); 

The internal query will return all user IDs that have the specified role. Using whereNotIn will return the opposite set of users. The following query will be created:

 select * from users where user_id not in ( select user_id from user_roles where role_id = ? ); 

Now, having a Collection roles stored in $roles , you can get all users who do not have these roles:

 /* @var Collection|Role[] $roles */ User::whereNotIn(function('id', $query) use ($roles) { $query->select('user_id') ->from('user_roles') ->whereIn('role_id', $roles->pluck('id'); }); 

Internal selection returns the identifiers of all users who have one of the roles in the collection. With whereNotIn you get the opposite result again. You can also use an array of role identifiers instead of $roles->pluck('id') .

The creator will create a query such as

 select * from users where user_id not in ( select user_id from user_roles where role_id in (?, ?, ..) );