34

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?

  • user_load_multiple() is also a no-go. Hopefully we'll have a way to do this in D8. – Dalin May 22 '13 at 19:48
  • You can use my EntityFieldQueryExtra sandbox as it allows for ->propertyCondition('rid', array(1, 2, 3)); – mikeytown2 Jul 3 '14 at 0:50

11 Answers 11

32

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.

  • 1
    Yup, this is the way I went. Yet I'm very curious how to achieve this with EFQ and will leave the question open. It's a pitty that EFQs aren't that well documented yet as they might entirely replace Views for me in the long run. – Nils Riedemann Sep 14 '11 at 9:30
  • 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. – Berdir Oct 4 '11 at 1:39
  • 1
    Also, optimization hint: You can replace the $uids initalization and the foreach with $uids = $result->fetchColumn(). – Berdir Oct 4 '11 at 1:42
  • Feel free to edit the code Berdir :) – Bart Oct 17 '11 at 8:51
16

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.

  • One way to "hack" the basetable requirement is to always add a bogus propertycondition like $query->propertyCondition('uid', 0, '!='); to the query. That forces the basetable to be linked, but does hinder performance by just a bit as the reason EFQ leaves out the basetable in the case of a single fieldCondition is that it's way faster to just query the field data table. – Tommi Forsström Jun 6 '13 at 18:49
  • 2
    I don't think this is quite the simplest possible example, as claimed. Can't you ditch the subquery in favour of two joins and one condition directly on $query? And I might also suggest changing mymodule_get_users_by_rolename to not return the result of the query itself, but to dereference the key 'user'. – Peter Taylor Sep 9 '13 at 13:31
16

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);
}
  • I wonder why this answer has so few up-votes. It is the best solution. Though I should note that $query->leftJoin('users_roles', 'r', 'users.uid = r.uid'); could cause unknown column 'uid'. In that case we need to add $query->propertyCondition('uid', 0, '!='), so the users table will be in the query. – Elaman May 18 '15 at 6:14
  • 1
    Definitely this should be the accepted answer. – Frank Robert Anderson Jun 10 '15 at 22:14
6
$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;
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.

  • 1
    If you have more than a few dozen users with this role, this EFQ will have terrible performance. – Dalin May 22 '13 at 19:47
5

/**
 * 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);
}
  • I think this is most probably the most efficient answer. As a slight improvement I would add a parameter $rolename that would be used in the call to user_role_load_by_name and a check to make sure user_role_load_by_name does not return false. – stefgosselin Sep 27 '15 at 6:43
2

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;
    }
  • If you have more than a few dozen users with this role, this EFQ will have terrible performance. – Dalin May 22 '13 at 19:47
  • @Dalin, given the initial qualification, (i.e. no sql, all drupal), what would you suggest as a method that would get better performance? – Gold Apr 9 '14 at 22:18
  • 1
    Hey Gold! Looks like the db_select() condition expects $role to have been a string, and if you pass in an integer it'll get caught on $role_obj not being set when it references $role_obj->rid. EDIT: I can edit answers, woo. – Chris Burgess May 9 '14 at 10:48
2

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

$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');
  • I changed my mind this is the best answer. Except, you're missing a semi-colon and $my_role_id is undefined. – Frank Robert Anderson Jun 10 '15 at 22:18
0

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);
}
-1

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

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."

-1

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);

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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