find all users of a certain role and return that list

Question

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;
  $query
    ->entityCondition('entity_type', 'user')
    ->addTag('role_filter');
  $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');
  $query
    ->condition($and);
}

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 ?

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.