Posted by bartl on Fri, Nov 22, 2024 - 5:10am

In a view of users, I'm trying to restrict them to users that do not have a particuklar rol. However, the options for filtering users, based on roles seem very confusing:
 

Operator

  •  Is one of
  •  Is all of
  •  Is none of
  •  Only has the 'Authenticated' role
  •  Has roles in addition to 'Authenticated'

plus a list select of existing roles.

I don't think I can achieve my goal using any of these options.

What  I want to do is, in SQL:

select U.* from {users} U left join {users_roles} UR on U.uid = UR.uid and UR.role = ? where UR.uid is null
 

Am I missing something here, or is it indeed not possible with just the core modules? 

What are the alternative options? Use of yet another module that I don't know of, or can I make a module myself that adds this functionaliy?

Accepted answer

Afaik when you use "is none of" [selected roles] it will show only those who don't have the selected role(s).

Comments

Fri, Nov 22, 2024 - 5:36am by theflightrs

Afaik when you use "is none of" [selected roles] it will show only those who don't have the selected role(s).

Fri, Nov 22, 2024 - 6:20am by bartl

Oh, yes, you're right. The way the option was described it sounded like "the user has a role that is not one of this list", which is not the same thing at all.

But the SQL it generated (I found the SQL view in the views settings) is this:

... LEFT JOIN {users_roles} users_roles_value_0 ON users.uid = users_roles_value_0.uid AND (users_roles_value_0.role = '15') WHERE (( (`users`.`status` <> '0') AND (`users_roles_value_0`.`role` IS NULL ) ))

which is exactly what I want.

Thank you.