Conditions

To add a WHERE clause to a dynamic query, use the condition() method:

$query->condition('bundle', 'article', '=');

The above code will instruct the query to filter results to the article bundle. Note that the field name here should be the same as in the database table. Use where() method for the alias created by the addField() or addExpression() methods.

Condition Parameters

The condition() method accepts three parameters:

• $field - The field on which to make the comparison (required).
• $value - The value to compare (optional, defaults to NULL).
• $operator - The comparison operator (optional, defaults to ‘=’)

Supported Operators

Common comparison operators of '=', '<>', '<', '<=', '>', '>=' are supported in all supported database types.

Using IN, NOT IN

The operators IN and NOT IN accept an array in $value and will compare the field value with the values of the array.

$users = [2,5,17,22];
$query->condition('uid', $users, 'IN');
//
$query->condition('uid', $users, 'NOT IN');

In the first example, the query will return records where uid is one of 2, 5, 17, or 22.

In the second example, the query will return records where uid is NOT one of  2, 5, 17, or 22.

Using NOT IN With Multi Value field like roles > user entity:

Lets' say you want to get users who has not role 'content_editor'

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

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

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

$query->condition($Group);
// Required in 9.2 onwards.
$query->accessCheck(FALSE);

In this example suppose site has 3 roles, excluding authenticated & anonymous role:

Then you have to add 'NOT IN' condition 3 times as roles is multi-value field & need to check role 'content_editor' for each role's row (by this roles.0.target_id, so on).

Using BETWEEN and  NOT BETWEEN

The operators BETWEEN and NOT BETWEEN accept an array of two values for the comparison.

$query->condition('count', [5,10], 'BETWEEN');

In this example, the query will return records where count is between 5 and 10.

Using IS NULL, IS NOT NULL, EXISTS,  and NOT EXISTS

By convention, use the following rather using the condition() method:

$query->isNull($field);
$query->isNotNull($field);
$query->exists($field);
$query->notExists($field);

While a condition such as $query->condition($field, NULL, 'IS NOT NULL'); should work, the above convention is recommended.

isNull, etc. for Querying Entities

As of Drupal 9.5.8, methods isNull() and isNotNull() do not exist for queries initiated with entityTypeManager. Use condition() like $query->condition($field, NULL, 'IS NOT NULL'); instead. For example:

// Load term storage for entities of type Taxonomy Term.
$taxonomy_term_storage = \Drupal::entityTypeManager()->getStorage('taxonomy_term');

// Get all Taxonomy Term IDs from vocabulary 'my_vocabulary_name' where
//   'field_my_custom_field' is not empty.
$query = $taxonomy_term_storage->getQuery()
  ->condition('vid', 'my_vocabulary_name')
  ->condition('field_my_custom_field', NULL, 'IS NOT NULL');
// Required in 9.2 onwards.
$query->accessCheck(FALSE);
$term_ids = $query->execute();

Other Operators

Other operators such as BINARY or others specific to the database system in use, may or may not work. Be aware of this when developing modules which may be used on different systems. 

Using Multiple Conditions

Multiple condition() methods may be added to further filter the query.

$query->condition('bundle', 'article', '=');
$query->condition('status', 1, '=');

When multiple condition()s exist, as in the example above, all conditions must be met for a record to appear in the query result (i.e. The conditions are ANDed together).

Using Subqueries in Conditions

Conditions can contain subqueries.

// Create a subquery, which is just a normal query object.
$database = \Drupal::database();
$subquery = $database->select('test', 't2');
$subquery->addExpression('AVG(t2.age)');

// Create another query that adds a clause using the subquery.
$select = $database->select('test', 't');
$select->addField('t', 'name');
$select->condition('t.age', $subquery, '<');

// The resulting query should be equivalent to:
// SELECT t.name
// FROM test t
// WHERE t.age < (SELECT AVG(t2.age) FROM test t2)

Condition Groups

Condition groups can be used to create more complex where clauses (including ORs) in the condition of the query. There are two condition group types:

• orConditionGroup - returns an object of conditions joined with ORs
• andConditionGroup - returns an object of conditions joined with ANDs

Conditions groups return an object, which can then be added as a condition to the query.

// Create the orConditionGroup
$orGroup = $query->orConditionGroup()
  ->condition('promoted', 1)
  ->condition('uid', [2,4,7,22], 'IN');
  
// Add the group to the query.
$query->condition($orGroup);

In this example, the conditions for promoted and uid are added to the query’s WHERE, grouped together with OR. This would result in a WHERE which looks like:

WHERE ( promoted = 1 OR uid IN (2,4,7,22) )

Multiple orConditionGroup and andConditionGroups can be added as needed. Condition groups can be nested for more complex WHERE clause needs.

Given the following example WHERE clause:

WHERE   ( a = 1 OR b = 1) AND ( (c = 1 AND d = 1) OR (e = 1) )

The query conditions could be written with condition groups as:

$orGroup1 = $query->orConditionGroup()
  ->condition('a', 1)
  ->condition('b', 1);
  
$andGroup1 = $query->andConditionGroup()
  ->condition('c', 1)
  ->condition('d', 1);
  
$orGroup2 = $query->orConditionGroup()
  ->condition($andGroup1)
  ->condition('e', 1);
  
$query->condition($orGroup1);
$query->condition($orGroup2);