Eloquent Many to Many select User without certain Roles

Question

So I have User & Role models with many-to-many relationship, I have 3 roles: super, admin and moderator with 4 users let's says: John, Mike, James and Larry.

John is a super, Mike has admin and moderator roles, James is an admin and Larry is a moderator. To displaying users who doesn't have certain roles I created this scope:

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 doesn't have super role, it works and returns:

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

But, when I trying to list users who doesn't have admin role User::doesntHaveRoles([2])->lists('name', 'id'), yes James is not shown there but Mike is appeared while he is actually has admin role:

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

I think it's because Mike is also has moderator role, do you see something wrong in my scope? or do you have other solutions?

Thanks

Edit: Here is my pivot schema

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 a Role stored in the variable $role you can get all users who don't have that role with:

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

The inner query will return all IDs of users who has the given role. Using whereNotIn will return the opposite set of users. The folowing 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 of roles stored in $roles you can get all users who don't have any of that roles with:

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

The inner select will return IDs of all users who has one of the roles in the collection. With whereNotIn you will again get the opposite result. You can also use an array of role-ids instead of $roles->pluck('id').

The builder will create a query like

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