Select users with only one role

Question

How can i select all users wich have only 1 role. For example i got 4 users:

User 1:

Role 1, Role 2

User 2:

Role 1

User 3:

Role 1, Role 2

User 4:

Role 1

How can i retrieve a list of users wich only got Role 1? But not Role 2.

$query = db_select('users', 'u');
$query->leftJoin('users_roles', 'ur', 'u.uid = ur.uid');
$query->leftJoin('field_data_field_full_name', 'n', 'u.uid = n.entity_id');
$query->condition(...);
$query->fields('u', array('uid','name','picture'));
$query->fields('n', array('field_full_name_value'));
$result = $query->execute()->fetchAll()

EDIT:

SELECT 
u.uid AS uid,
u.name AS name, 
u.picture AS picture,
n.field_full_name_value AS field_full_name_value,
f.field_function_value AS field_function_value,
pi.field_phone_intern__value AS field_phone_intern__value,
ur.rid AS rid,
COUNT(*) AS rolecount
FROM `dev_drupal_wurth-intranet`.users u 
LEFT OUTER JOIN `dev_drupal_wurth-intranet`.users_roles ur ON u.uid = ur.uid 
LEFT OUTER JOIN `dev_drupal_wurth-intranet`.field_data_field_full_name n ON u.uid = n.entity_id 
LEFT OUTER JOIN `dev_drupal_wurth-intranet`.field_data_field_function f ON u.uid = f.entity_id 
LEFT OUTER JOIN `dev_drupal_wurth-intranet`.field_data_field_phone_intern_ pi ON u.uid = pi.entity_id 
GROUP BY u.uid;

This give me the result all the users and their number of roles. Now i tried to filter on this role count with 'WHERE rolecount = 1' but this just gives and sql error.

I'm not very advanced in sql :), still learning.

Thx

Answer 1

You can try this one:

  $sub_query1 = db_select('users_roles', 'ur')
    ->fields('ur', array('uid'))
    ->condition('ur.rid', '3'); // Role 1

  $sub_query2 = db_select('users_roles', 'ur')
    ->fields('ur', array('uid'))
    ->condition('ur.rid', '4'); // Role 2

  $query = db_select('users', 'u');
  $query->leftJoin('users_roles', 'ur', 'u.uid = ur.uid');
  $query->leftJoin('field_data_field_full_name', 'n', 'u.uid = n.entity_id');

  $query->condition('ur.uid', $sub_query1, 'IN');
  $query->condition('ur.uid', $sub_query2, 'NOT IN');

  $query->fields('u', array('uid','name','picture'));
  $query->fields('n', array('field_full_name_value'));
  $result = $query->execute()->fetchAll();

Answer 2

If I were you (and with my poor level), I would proceed in 2 steps:

1. Get all the users with the role that interests you. Maybe inspire yourself for the query here: Get all users with specific roles using EntityFieldQuery

2. Browse the query result by loading each user and check if $user->roles has another role than the one you are looking for.

3. Push the final results in another array.

I don't know if it is a good way to proceed or if there is another way to achieve this, but I think it could do the trick.