Query to fetch users with specific roles

Question

I'm using the following code.

$query = \Drupal::entityQuery('user');        
$query->condition('uid', [302,330], 'IN') 
  ->condition('status', 1, '=')
  ->condition('field_membership_status.value', 'paid', '!=')  
  ->condition('roles', ['individual','organization'], 'IN')
  ->condition('roles', ['organization_member'], 'NOT IN'); 

$uids = $query->execute();
  
$users = \Drupal::entityTypeManager()->getStorage('user')->loadMultiple(array_values($uids));

foreach ($users as $user) {
  $type= "Final membership reminder";
  \Drupal::logger('my_module')->notice("email to ".$user->getAccountName());
  \Drupal::service('vc_mailer.email.service')->send_email($type, $user);
}

One of the user accounts has the following roles: authenticated, individual, and organization_member. This user account is still returned by the code; it seems like the code ignores the last condition. What is the problem?

condition('uid', [302,330], 'IN') is just to constrain the query to those members for testing purposes.

The SQL query is the following.

SELECT "base_table"."uid" AS "uid", "base_table"."uid" AS "base_table_uid" FROM "users" "base_table" INNER JOIN "users_field_data" "users_field_data" ON "users_field_data"."uid" = "base_table"."uid" INNER JOIN "user__field_membership_status" "user__field_membership_status" ON "user__field_membership_status"."entity_id" = "base_table"."uid" INNER JOIN "user__roles" "user__roles" ON "user__roles"."entity_id" = "base_table"."uid" WHERE ("users_field_data"."uid" IN ('302', '330')) AND ("users_field_data"."status" = '1') AND ("user__field_membership_status"."field_membership_status_value" != 'paid') AND ("user__roles"."roles_target_id" IN ('individual', 'organization')) AND ("user__roles"."roles_target_id" NOT IN ('organization_member'))

Answer 1

Snippet code to get users with some role but not some other role: Drupal 9/10:

$query = \Drupal::entityQuery('user');
$Group1 = $query->orConditionGroup()
->condition('roles.0.target_id', 'organization_member', 'NOT IN')
->condition('roles.0.target_id', NULL, 'IS NULL');
  
$Group2 = $query->orConditionGroup()
->condition('roles.1.target_id', 'organization_member', 'NOT IN')
->condition('roles.1.target_id', NULL, 'IS NULL');

$Group3 = $query->orConditionGroup()
->condition('roles.2.target_id', 'organization_member', 'NOT IN')
->condition('roles.2.target_id', NULL, 'IS NULL');

$Group = $query->andConditionGroup()
  ->condition($Group1)
  ->condition($Group2)
  ->condition($Group3);

$query->condition('uid', 0, '<>');
$query->condition('roles', ['individual','organization'], 'IN');

$query->condition($Group);
$result  = $query->execute();

Additional Info: let's say site has 3 roles, excluding authenticated & anonymous role if you want to get user who has not role 'organization_member' then you have to check that role 'organization_member' for each role's row for individual user as role is multiple value field.

Analysis: #1: I also found some of articles where used query like this:

$query->condition('roles.target_id', 'organization_member', 'NOT IN');

Which interpret like this when query execute:

'roles.target_id' = 'roles.0.target_id'

So It 'll not scan through all the roles rows for each user entity, then unexpected result will be returned, it seems that 'NOT IN' operator not work with role field of user. But in actual role is multivalue field. require to use 'NOT IN' operator for per role row.

#2: With uid 'NOT IN' conditon works fine , as uid is single value field similarly goes with the type field of node.