0

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'))
6
  • You probably need to put the conditions in andConditionGroup(). See drupal.stackexchange.com/questions/226396/…
    – 4uk4
    Oct 1, 2023 at 9:19
  • I shouldn't need to, they are all 'AND' condtions
    – jg999
    Oct 1, 2023 at 18:45
  • Have you cast the query object to a string to see what the actual SQL would be?
    – cilefen
    Oct 1, 2023 at 18:47
  • Have you ensured the machine name you're using is the same as the system?
    – Jaypan
    Oct 1, 2023 at 19:48
  • yes, $user->getRoles() returns "authenticated, individual, organization_member "
    – jg999
    Oct 1, 2023 at 19:52

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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