find all users of a certain role and return that list 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
    -&gt;entityCondition('entity_type', 'user')
    -&gt;addTag('role_filter');
  $results = $query2-&gt;execute();


So getting all users within a specific role id

function mymodule_query_role_filter_alter(QueryAlterableInterface $query) {
  $query-&gt;leftJoin('users_roles', 'r', 'users.uid = r.uid');  
  $and = db_and()
            -&gt;condition('r.rid', array(3,4,5), 'IN');
  $query
    -&gt;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":"admin@example.com" "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 ?
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-&gt;innerJoin('users_roles', 'ur', 'users.uid = ur.uid');  
  $query-&gt;innerJoin('role', 'r', 'r.rid = ur.rid');
  $query-&gt;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.