In Drupal 8 - Is there a way to do a 'NOT IN' in a user based entityQuery?

$uids = \Drupal::entityQuery('user')
      ->condition('uid', 72)
      ->condition('status', 1)
      ->sort('created', 'ASC')

This query ^ yeilds results

$uids = \Drupal::entityQuery('user')
  ->condition('uid', 72)
  ->condition('roles', ['testing_user'], 'NOT IN')
  ->condition('status', 1)
  ->sort('created', 'ASC')

This query ^ does not. User 72 doesn't have the role 'testing_user'

  • i didn't get it, you want to retrieve the users ids with roles not in testing_user and in the same time you add condition condition('uid', 72)?
    – berramou
    Jan 25, 2019 at 23:30
  • So I'm just using that as a test condition. I will eventually be removing the uid 72. Sorry should have been more specific about this. Jan 26, 2019 at 0:49
  • All I'm looking to do here is remove all results for testings_users. This code is being used in a bulk creation module to create content for all users. I'm trying to not create said content for users tagged with a testing_user role. Jan 26, 2019 at 0:55
  • Why was this downvoted? Jan 29, 2019 at 22:59

2 Answers 2


Is there a way to do a 'NOT IN' in a user based entityQuery?

Yes, QueryInterface::condition $operator parameter documentation explicitly mentions NOT IN among other operators.

Operators are supported in several different ways based on the storage for an entity. For instance, "NOT IN" is supported via array_search in configuration entities and via the same manner as SelectInterface::condition for SQL-based entities.

Regardless of the manner the key is knowing the "field" to search on. You're working with an entity_reference field, which is more complex than a condition on a single column.

Perform a query with an entity field condition with multiple values shows this a little, but I hesitated to mark this one as a duplicate because it's not in the accepted answer.

Anyway the documentation for Entity QueryInterface mentions that the $field parameter

Name of the field being queried. It must contain a field name, optionally followed by a column name. The column can be the reference property, usually "entity", for reference fields and that can be followed similarly by a field name and so on. Additionally, the target entity type can be specified by appending the ":target_entity_type_id" to "entity".

So I think roles.target_id should work. If user_role entity was a content entity then the second part of the above statement would apply. Otherwise an exception will be thrown.

However @0-nur is correct: the roles field data stores information users who have roles so any query for a user that has the default roles will not be included in the query. You would need to add an additional condition. This is similar behavior to Drupal 7. In this case, since we're testing nullable we can use "roles" as the field.

$query = \Drupal::entityQuery('user');
$or = $query->orConditionGroup();
  ->condition('roles.target_id', ['testing_user'], 'NOT IN')
  ->condition('roles', NULL, 'IS NULL');

Your original code is correct. The problem is condition of 'not in'. It doesn't give result of users who has no roles. If you give roles to each user you are good to go.

More reading

You can get deeper understanding from class file https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Database%21Query%21Condition.php/class/Condition/8.2.x

Also there is a Drupal 7 API documentation about conditional-clauses https://www.drupal.org/docs/7/api/database-api/dynamic-queries/conditional-clauses


Your Answer

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

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