Conditional clauses

A "conditional clause" is a portion of a query that restricts the rows matched by certain conditions. In SQL, that means the WHERE or HAVING portions of a SELECT, UPDATE, or DELETE query. In all dynamic queries in Backdrop those are implemented using the same mechanism. Except where noted, everything that follows applies to all three of those query types.

Concepts

Conditional fragment

A conditional fragment is a self-contained portion of a conditional clause.

Conjunction

Every conditional clause consists of one or more conditional fragments joined by a conjunction. A conjunction is a term such as AND or OR that joins the two statements together.

Conditional object

Backdrop represents each conditional fragment as an instance of class QueryConditional. A conditional object is an instance of that class.

As an example, the following query breaks down this way:

Query

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

Conditional clause

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

Conditional fragments

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

(c = 'bar')

Conjunctions

AND, OR

The Select, Update, and Delete query objects implement the QueryConditionalInterface, which provides them all with the same interface. Internally they wrap a QueryConditional object. The QueryConditional class can also be instantiated directly.

Each conditional fragment in a conditional statement is joined by a conjunction. A conditional object consists of one or more conditional fragments that will all be joined by a specified conjunction. By default, that conjunction is AND. Each conditional fragment may optionally be a conditional object with a different conjunction, allowing for a conditional statement to be built up by nesting conditional fragments inside each other. In that way, arbitrarily complex conditional statements may be built.

API

There are two main methods that apply for all conditional objects:

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

The condition() method allows for adding a standard $field $value $operator format of conditional fragment. That includes any case where the condition is a binary comparison such as =, <, >=, LIKE, etc. If no operator is specified, = is assumed. That means the most common case would be condition('myfield', $value), which results in a conditional fragment of myfield = :value, where :value will be replaced with $value when the query is run. Database-specific operators such as REGEXP for MySQL are supported; see the database's documentation for implementation-specific options.

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

The where() method allows for the addition of arbitrary SQL as a conditional fragment. $snippet may contain any legal SQL fragment, and if it has variable content it must be added using a named placeholder. The $args array is an array of placeholders and values that will be substituted into the snippet. It is up to the developer to ensure that the snippet is valid SQL. No database-specific modifications are made to the snippet.

The condition() method is preferred in most cases, unless the $field $value $operator format is not appropriate, such as happens when you have more complex things like expressions, or a condition on two fields instead of a field and a value. Both methods return the corresponding conditional object, so they may be chained indefinitely.

condition() also handles several other special cases.

Unlike Update and Delete queries, Select queries have two types of conditionals: The WHERE clause and the HAVING clause. The Having clause behaves identically to the WHERE clause, except that it uses methods havingCondition() and having() instead of condition() and where().

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

$query->having($snippet, $args = [])

Array operators

Some operators are intended to work on an array for the value parameter. The most common of these are IN and BETWEEN. If the operator is IN, then the $value is assumed to be an array of values that the field may equal. Thus, the following call will evaluate this way:

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

If the operator is BETWEEN, then $value is assumed to be a 2-element array of the values the field must be between. For example:

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

NOT IN is also supported.

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

Using the <> operator will result in an error.

Nested conditionals

The first parameter of condition() can also accept another conditional object. That inner conditional object will be incorporated into the outer conditional, surrounded by parentheses. The inner object may also use a different conjunction than the outer object. That way, one can build complex nested conditional structures by creating and building up conditional objects "bottom up".

The db_condition() helper function will return a new conditional object. It takes a single parameter that is the conjunction that object will use. In general, the helper methods db_and(), db_or(), and db_xor()will cover almost any expected case. 

Null values

To filter a database field on whether the value is or is not NULL, use the following methods.

$query->isNull('myfield');
// Results in (myfield IS NULL)
$query->isNotNull('myfield');
// Results in (myfield IS NOT NULL)

Both methods may be chained and combined with condition() and where() as desired.

Although it is possible in Backdrop to check for NULL values using condition('field', NULL), that usage is deprecated and should not be used. Instead, use the methods above. 

Subselects

condition() also supports subselects as the $value. To use a subselect, first construct a SelectQuery object created by db_select(). Then, instead of executing the Select query pass it into the value parameter of the condition() method of another query. It will automatically get integrated into the main query when it is executed.

Subselects are generally useful only in two cases: Where the subselect results in only a single row and value returned and the operator is =, <, >, <=, or >=; or when the subselect returns a single column of information and the operator is IN. Most other combination would result in a syntax error.

On some databases, particularly MySQL, subselects in a conditional clause are not particularly fast. If possible, use joins, subselects in the FROM clause, or multiple flat conditional fragments instead of a subselect.

Examples

The following examples should hopefully make the use of conditionals clearer. For clarity, the equivalent query string is shown even though in practice placeholders and prepared statements would be used instead.

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

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

// From taxonomy_term_save():
$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))