Get all users with specific roles using EntityFieldQuery

Question

I thought this was an easy task, yet there does not seem to be a Drupal-method for this. I came as far as knowing I have to use EntityFieldQuery for this - because the API said conditions for user_load_multiple() are deprecated.

So I tried this:

  $query = new EntityFieldQuery;
  $query
    ->entityCondition('entity_type', 'user')
    ->propertyCondition('rid',array(1,2,3);

  $result = $query->execute();

Yet I got this:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.rid' in 'where clause': SELECT users.uid AS entity_id, :entity_type AS entity_type, NULL AS revision_id, :bundle AS bundle FROM {users} users WHERE (users.rid = :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] => 3 [:entity_type] => user [:bundle] => user ) in EntityFieldQuery->execute()

So my first thought was, that I'd have to join with users_roles-Table and so on, but that will lead to duplicates.

Does anyone have an idea how to do it?

Answer 1

To be honest, I have no idea on how to achieve this. Good examples of how to use the EntityFieldQuery are hard to find. Since noone has answered this question yet and I'm also interested in the solution I'll try to help you out. Described below is my best guess.

A thing to keep in mind: roles are stored in a different table than users. They are added using UserController::attachLoad.

There seem to be three different conditions that you can use with an EntityFieldQuery:

1. entityCondition (Entity specific conditions:'entity_type', 'bundle', 'revision_id' or 'entity_id')
2. fieldCondition (Conditions on fields maintained by the Field API)
3. propertyCondition (Conditions on entity properties)

The most logical option (if there is one) would be to use a propertyCondition, but as roles are added to the user in UserController::attachLoad I don't think the EntityFieldQuery has access to it. I think the EntityFieldQuery just uses the schema defined in hook_schema().

This leads me to believe that what you're trying to achieve is impossible. A workaround would be to get all the uids with a normal query:

I don't have access to Drupal right now, so the code below might be off. I'm sure somebody will edit the mistakes.

// Use $query for readability
$query = 'SELECT DISTINCT(ur.uid) 
  FROM {users_roles} AS ur
  WHERE ur.rid IN (:rids)';
$result = db_query($query, array(':rids' => array(1,2,3)));

$uids = $result->fetchCol();

$users = user_load_multiple($uids);

If it is possible to achieve what you want with EntityFieldQuery, I'll be enlightened.

Answer 2

It should do the trick

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

/**
* Implement hook_query_TAG_alter
* 
* @param QueryAlterableInterface $query
*/
function MY_MODULE_query_role_filter_alter(QueryAlterableInterface $query) {
  $query->leftJoin('users_roles', 'r', 'users.uid = r.uid');  
  $and = db_and()
            ->condition('r.rid', MY_ROLE_INT, '=');
  $query
    ->condition($and);
}

Answer 3

There is in fact a way to do this. In its heart, the EntityFieldQuery (EFQ) is just a database query which can be altered with query alter hooks.

Simplest possible example:

function mymodule_get_users_by_rolename($rolename){
  $query = new EntityFieldQuery;
  $query->entityCondition('entity_type', 'user');
  $query->addTag('rolequery');
  $query->addMetaData('rolename', $rolename);

  return $query->execute();
}

function mymodule_query_rolequery_alter(QueryAlterableInterface $query) {
  $rolename = $query->getMetaData('rolename');

  $role_subquery = db_select("role", "role");
  $role_subquery->condition('role.name', $rolename, '=');
  $role_subquery->join('users_roles', "users_to_include", "role.rid = users_to_include.rid");
  $role_subquery->fields('users_to_include', array('uid' => 'uid'));
  $role_subquery->where('users_to_include.uid = users.uid');
  $query->exists($role_subquery);
}

There is however a few small caveats with this that would require some more coding. For example in the care of the only condition present in the EFQ being a fieldCondition, the users-basetable won't be present, so when you're fetching users by role and a single fieldCondition, you'd also need to make sure the users table is joined and if not, manually join it, which is a bit of a tedious process.

But for your need, this will do the trick. Realizing you can manually alter EFQ queries opens a huge world of opportunities for creating very powerful queries while still keeping the interface clean and Drupal.

Let me know if you have any questions or issues.

Edit: I actually found a little more performant way of doing this and changed my code accordingly.

Answer 4

$user_list = array();
$roles = array('role_1', 'role_2');
$query = db_select('users_roles', 'ur');
$query->join('users', 'u', 'u.uid = ur.uid');
$query->join('role', 'r', 'r.rid = ur.rid');
$query->fields('u',array('uid'));
$query->fields('u',array('mail'));
$query->fields('u',array('name'));

$query->condition('r.name', $roles, 'IN');
$result = $query->execute();

if($result){
    foreach($result as $row ) {
        $uid = $row->uid;
        $user_list[$uid]['mail'] = $row->mail;
        $user_list[$uid]['name'] = $row->name;
        $user_list[$uid]['uid'] = $uid;
    }
}

return $user_list;

Answer 5

You can still use EntityFieldQuery() once you get the user ids for your role, if you want to, for example because you want to user fieldCondition() or another method.

Using the $uids array in the above example:

$role = user_role_load_by_name('my_role_name');
$result = db_select('users_roles', 'ur')
->fields('ur', array('uid'))
->condition('rid', $role->rid)
->execute();

foreach($result as $record) {
  $uids[] = $record->uid;
}

$query = new EntityFieldQuery();
$query->entityCondition('entity_type', 'user')
->entityCondition('entity_id', $uids, 'IN')
->execute();

It would be nicer if EntityFieldQuery had a join method.

Answer 6

/**
 * Get users by specific role
 */
function mymodule_get_users_by_role() {
    $role = user_role_load_by_name('rolename');
    $uids = db_select('users_roles', 'ur')
        ->fields('ur', array('uid'))
        ->condition('ur.rid', $role->rid, '=')
        ->execute()
        ->fetchCol();
    $users = user_load_multiple($uids);
}

Answer 7

OK This is an old one, but you could do something like this:

$my_role_id = [3,4,5];

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

$roles_subquery = db_select('users_roles', 'ur');
$roles_subquery->fields('ur', array('uid'));
$roles_subquery->condition('rid', $my_role_id);

$query->propertyCondition('uid', $roles_subquery, 'IN');

$result = $query->execute();

Answer 8

This is a little late to the game, but I think it's what the OP was asking for. e.g. no sql, all drupal. Hopefully others will find it useful. The search for this led me here and this is what I came up with.

    /**
     * Users with role
     *
     * @param $role mixed The name or rid of the role we're wanting users to have
     * @param $active_user boolean Only return active accounts?
     *
     * @return array An array of user objects with the role
     */
    function users_with_role($role, $active_user = TRUE) {
      $uids = array();
      $users = array();
      if (is_int($role)) {
        $my_rid = $role;
      }
      else {
        $role_obj = user_role_load_by_name($role);
      }
      $result = db_select('users_roles', 'ur')
        ->fields('ur')
        ->condition('ur.rid', $role_obj->rid, '=')
        ->execute();
      foreach ($result as $record) {
        $uids[] = $record->uid;
      };
      $query = new EntityFieldQuery();
      $query->entityCondition('entity_type', 'user')
        ->propertyCondition('uid', $uids, 'IN');
      if ($active_user) {
        $query->propertyCondition('status', 1);
      }
      $entities = $query->execute();
      if (!empty($entities)) {
        $users = entity_load('user', array_keys($entities['user']));
      }
      return $users;
    }

Answer 9

In drupal 8 you can't use entityFieldQuery, instead use entityQuery. something like the following will return a list of user with the required role ('editor' in the example).

$uids = \Drupal::entityQuery('user')
  ->condition('status', 1)
  ->condition('roles', 'editor')
  ->execute();

$users = User::loadMultiple($uids);
foreach($users as $user){
  $username = $user->get('name')->value;
  $uid = $user->get('uid')->value;
  $userslist[$uid] = $username;
}

Answer 10

This is indeed an older topic and I found lots of good approaches.

I would improve a bit on the solution provided by @alf, because I think one query with join is the best approach. However I also like my functions to have parameters and not depend on a constant. So here it goes:

function MY_MODULE_load_users_by_role($rid) {
  $query = new EntityFieldQuery;
  $query
    ->entityCondition('entity_type', 'user')
    ->addMetaData('account_role', user_role_load($rid))
    ->addTag('role_filter');
  $results = $query->execute();

  if (isset($results['user'])) {
    $uids = array_keys($results['user']);
    $users = entity_load('user', $uids);
  }
  else {
    $users = array();
  }

  return $users;
}

/**
 * Implement hook_query_TAG_alter
 *
 * @param QueryAlterableInterface $query
 */
function MY_MODULE_query_role_filter_alter(QueryAlterableInterface $query) {
  $rid = $query->alterMetaData['account_role']->rid;
  $query->leftJoin('users_roles', 'r', 'users.uid = r.uid');
  $and = db_and()
    ->condition('r.rid', $rid, '=');
  $query
    ->condition($and);
}

Answer 11

You can find a good explanations examples about how to use the EntityFieldQuery here:

• http://drupal.org/node/1343708 (in this article you will find more useful resources, tutorials and examples)

but just as Berdir said as for now "EFQ only supports basic properties of an entity, which are part of the entity table. The roles of a user are not exposed in any way to the entity system."

Answer 12

I have no doubt this can be simplified. This can be done though for drupal 8:

//retrieve all "VIP" members
$query = \Drupal::entityQuery('user');
$nids = $query->execute();
foreach ($nids as $nid){
    $user = \Drupal\user\Entity\User::load($nid);
    if ($user->hasRole('VIP'))
        $emails_VIP_members[]=$user->getEmail();
}
 $send_to=implode(',',$emails_VIP_members);