entityQuery condition for users without a role

Question

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')
      ->execute();

This query ^ yeilds results

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

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

Answer 1

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();
$or
  ->condition('roles.target_id', ['testing_user'], 'NOT IN')
  ->condition('roles', NULL, 'IS NULL');
$query->condition($or);

Answer 2

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