Working with a Database in Drupal 7 - Lesson 12 - Query Conditions (WHERE, HAVING, LIKE)

The WHERE clause in a query allows you to select only those records that meet certain conditions—for example, nodes created no earlier than two weeks ago, or taxonomy terms containing the word “Drupal”. In SQL, we use WHERE and HAVING to specify conditions in SELECT, UPDATE, and DELETE queries. In Drupal’s dynamic queries, there's a built-in mechanism for handling query conditions, and it works the same across SELECT, UPDATE, and DELETE operations.

Conditional Expressions Concept

Conditions are represented as special expressions that define constraints.

Combining Conditions

Each condition can consist of multiple conditional expressions combined together using logical operators: AND and OR.

Condition Object

Drupal represents each condition fragment as an instance of the QueryCondition class. Every condition object is a QueryCondition instance.

Example SQL query:

SELECT FROM {mytable} WHERE (a = 1 AND b = 'foo' OR (c = 'bar'))

Condition expression:

WHERE (a = 1 AND b = 'foo' OR (c = 'bar'))

Parts of the condition:

(a = 1 AND b = 'foo' OR (c = 'bar'))
(c = 'bar')

Operators:

AND, OR

SELECT, UPDATE, and DELETE queries implement QueryConditionalInterface, which provides condition-related methods. These are wrapped in a QueryCondition object. The QueryCondition class can also be used directly.

Each expression is joined with AND by default. Multiple fragments can be joined with OR using grouping constructs.

API

There are two main methods for setting conditions on a query object:

$query->condition($field, $value = NULL, $operator = '=');

This method defines a basic comparison condition. Use it with comparison operators like =, <, >=, LIKE, etc. If no operator is specified, it defaults to =. For example:

$query->condition('myfield', $value);
// Becomes: myfield = :value

Another method is:

$query->where($snippet, $args = array());

The where() method allows raw SQL snippets with placeholders. It is less preferable than condition() but useful when writing complex or custom SQL.

Conditions with Arrays

To filter by multiple values, you can pass an array of values. Common SQL operators used are IN and BETWEEN.

$query->condition('myfield', array(1, 2, 3), 'IN');
// Becomes: myfield IN (:db_placeholder_1, :db_placeholder_2, :db_placeholder_3)

For value ranges, use BETWEEN:

$query->condition('myfield', array(5, 10), 'BETWEEN');
// Becomes: myfield BETWEEN :db_placeholder_1 AND :db_placeholder_2

Nested Conditions

The first parameter of condition() can itself be another condition object. This enables complex, nested queries combining AND and OR operators.

Use db_condition() to create new condition objects. You can also use helpers like db_or(), db_and(), db_xor(). For example:

$query
  ->condition('field1', array(1, 2), 'IN')
  ->condition(db_or()->condition('field2', 5)->condition('field3', 6));
// Becomes:
// (field1 IN (:db_placeholder_1, :db_placeholder_2) AND (field2 = :db_placeholder3 OR field3 = :db_placeholder_4))

NULL Conditions

To check for NULL values, use:

$query->isNull('myfield');
// Becomes: myfield IS NULL

$query->isNotNull('myfield');
// Becomes: myfield IS NOT NULL

Note: condition('myfield', NULL) is deprecated. Use isNull() or isNotNull() instead.

Subqueries

The condition() method supports subqueries as values using the SelectQuery class returned by db_select(). Instead of calling select() directly, you pass the object as a parameter in condition().

Subqueries are ideal for small, one-field conditions. For more complex cases, avoid using subqueries and stick to condition chains.

Examples

db_delete()

db_delete('sessions')
  ->condition('timestamp', REQUEST_TIME - $lifetime, '')
  ->execute();
// DELETE FROM {sessions} WHERE (timestamp  1228713473)

db_update()

db_update('sessions')
  ->fields(array('sid' => session_id()))
  ->condition('sid', $old_session_id)
  ->execute();
// UPDATE {sessions} SET sid = 'abcde' WHERE (sid = 'fghij');

db_delete() with OR conditions

$or = db_or()->condition('tid1', 5)->condition('tid2', 6);
db_delete('term_relation')->condition($or)->execute();
// DELETE FROM {term_relation} WHERE ((tid1 = 5 OR tid2 = 6))