I am trying to turn this query to drupal EntityCondition query

select u.name, ',', u.mail, ',', r.name , ','
from users as u, users_roles as ur, role as r 
where u.uid = ur.uid 
and ur.rid = r.rid

This should return a list of all users with their email username and user role

I have this

  $query = new EntityFieldQuery;
    ->entityCondition('entity_type', 'user')
  $results = $query2->execute();

So getting all users within a specific role id

function mymodule_query_role_filter_alter(QueryAlterableInterface $query) {
  $query->leftJoin('users_roles', 'r', 'users.uid = r.uid');  
  $and = db_and()
            ->condition('r.rid', array(3,4,5), 'IN');

I am not sure how to get the role value based on what I have is it another entity condition?

The above entityFiledQuery does return the following

{"1":{"uid":"1","name":"admin","pass":"HASHPASSWORD!","mail":"[email protected]" "roles":{"2":"authenticated user","3":"administrator"},

but a) I would rather have roles as the value administrator and not this funky array with the "rid" key b) the hashed password is there, can data be sanitized ?

  • 1
    Isn't naming your module "users" rather confusing given that the core module is named "user"?
    – Clive
    Commented Feb 10, 2017 at 17:56
  • 1
    Yeah, I would change that for clarity.
    – Kevin
    Commented Feb 10, 2017 at 18:02
  • It has another name, I just cannot use it publicly so I changed it to users we can call it XYZ_query_role as well
    – Quantico
    Commented Feb 10, 2017 at 18:02
  • 1
    Oh in that case, a general practice is using mymodule_ as a prefix (or mytheme_ for themes).
    – Kevin
    Commented Feb 10, 2017 at 18:04

1 Answer 1


The name of the role is in the role table so just join that in as well:

function mymodule_query_role_filter_alter(QueryAlterableInterface $query) {
  $query->innerJoin('users_roles', 'ur', 'users.uid = ur.uid');  
  $query->innerJoin('role', 'r', 'r.rid = ur.rid');
  $query->condition('r.name', 'administrator');

Note that you want inner joins here, not left, otherwise you'll just get whether they have the admin role or not. You don't need an and as you only have one condition.

The hashed password is added by user_load, not your query, so you'd have to address that separately. I'm not sure there's an easy way to exclude a property from an entity load, it would make for a good follow-up question.

  • $query->condition('r.name', 'administrator'); there are 3 types of roles I don't want to limit this to administrator only
    – Quantico
    Commented Feb 10, 2017 at 18:49
  • So apply what you already know ;) $query->condition('r.name', ['administrator', 'foo', 'bar'], 'IN');
    – Clive
    Commented Feb 10, 2017 at 18:50
  • Thank you for the answer but the entity_load still returns role as "roles":{"2":"authenticated user","3":"administrator"} so when I parse the data I have to do something like roles[1] which is not super great programmatically
    – Quantico
    Commented Feb 10, 2017 at 18:53
  • You already have the roles up front, you're limiting the query results by those roles...why would you need to get them from the user entity? You already know they're going to be correct right? If you desperately need to check again with php just use array_search if you need to find the role ID, or in_array to test that the user has a certain role
    – Clive
    Commented Feb 10, 2017 at 18:55
  • Or reset to get the first, end to get the last, array_slice to get a certain other one, array_filter if you're feeling fruity, the list goes on. It feels like there's probably some context missing from your question that would clear this up. In plain English what are you trying to do? Get all users with any of these roles and do x with them? Get all users with all of these roles and do y with them? Something else?
    – Clive
    Commented Feb 10, 2017 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.