Conditions

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

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

The code above instructs the query to filter results in the article bundle. Note that the field name here must be the alias created by the addField() or addExpression() methods.

Condition Parameters

The condition() method takes three parameters:

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

Supported Operators

The common comparison operators '=', '<>', '<', '<=', '>', '>=' are supported in all supported database types.

Using IN, NOT IN

The IN and NOT IN operators take an array in $value and compare the field value to the array values.

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

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

Using BETWEEN and NOT BETWEEN

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

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

In this example, the query will return records with a count between 5 and 10.

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

By convention, use the following instead of 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 convention above is recommended.

Other Operators

Other operators, such as BINARY or those specific to the database system in use, may or may not work. Keep this in mind when developing modules that may be used across different systems.

Using Multiple Conditions

You can add multiple condition() methods to further filter the query.

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

If multiple condition() calls 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 combined with AND).

Tip: To get a query with only OR conditions, use a single orConditionGroup.

Condition Groups

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

• orConditionGroup - returns a condition object combined with OR
• andConditionGroup - returns a condition object combined with AND

Condition groups return an object that 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 WHERE clause of the query, grouped together with OR. This will result in a WHERE clause that looks like:

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

You can add multiple orConditionGroups and andConditionGroups as needed. Condition groups can be nested for more complex WHERE clause requirements.

The following example WHERE clause:

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

The query conditions can 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);