Get All User That Doesn't Have Any Role or User That Don't Have Relationship To This Role

Sorry if my question is a bit confusing because I'm not a native speaker.

So the question is I have a User and a Role model, I'm trying to display a list of users that doesn't have any roles assigned to it, or the user that don't have this specific role.

The relation is many to many and using a pivot table

I tried to write this method on my model file


public function getUsersNotInRole($id)
{
	return User::with('roles')->where('roles.id, '!=', $id)->orWhere('roles.id', '')->get();
}

but didn't get the result I expected, any insight please?

ftiersch

5 years ago

Level 28

return User::where(function($query) use ($roleId) {
	$query->whereDoesntHave('roles', function ($query) use ($roleId) {
		$query->where('id', $roleId);
	})->orWhereDoesntHave('roles');
})->get();

I think that should work but haven't tested it :)

1 like

Joeseph Chen

5 years ago

Level 20


SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select * from `users` where (not exists (select * from `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` where `users`.`id` = `role_user`.`user_id` and `id` = 6) or not exists (select * from `roles` inner join `role_user` on `roles`.`id` = `role_user`.`role_id` where `users`.`id` = `role_user`.`user_id`)))

I'm getting this error from your code..

right now my solution is I tried find every user id that related to this role


foreach($this->role->users as $user) {
	$this->userIds[] = $user->id;
}

User::whereNotIn('id', $userIds)->get()

but I keep thinking there is a better way to write this..

Please or to participate in this conversation.