Introduction in Dynamic Queries

Dynamic queries refer to queries that are dynamically created by Drupal, rather than being supplied as an explicit query string. All insert, update, delete, and merge queries must be dynamic. Select queries can be either static or dynamic. Hence, "dynamic query" usually refers to a dynamic Select query.

Note: In 90% of select query use cases, you'll use a static query. If on a performance-critical path, you should use query() instead of select() for performance reasons. Use dynamic queries only if parts of the query vary (e.g., adding WHERE conditions depending on context) or if they need to be alterable.

All dynamically created queries are built using a query object obtained from the corresponding database connection object. As with static queries, in most cases, a procedural wrapper can be used to request the query object. However, subsequent query directives take the form of methods called on the query object.

Dynamic select queries are initiated using the select() method like this:

$database = \Drupal::database();
$query = $database->select('mytable', 'mt', $options);

In this case, mytable is the base table for the query—the first table after the FROM clause. Note that it should not be wrapped in braces. The query builder will handle that automatically. The second parameter is the table alias. If not specified, the table name is used. The $options array is optional and is identical to the $options array for static queries.

The value returned by $database->select() is an object of type Select. Thus, the type of the value in the $query variable after this call is a Select object. This object has a full set of methods like fields(), joins(), and group() which can be called to further define the query.

Dynamic select queries can be very simple or very complex. Below we will look at individual parts that make up a simple query, and on the following pages, we will look at more complex methods such as joins.

Big Picture

Here's a relatively simple user table query.

Suppose we want to create a dynamic query roughly equivalent to the following static query:

$result = $database->query("SELECT uid, name, status, created, access FROM {users} u WHERE uid <> 0 LIMIT 50 OFFSET 0");

The dynamic equivalent begins as follows:

// Create an object of type Select.
$database = \Drupal::database();
$query = $database->select('users', 'u');

// Add extra detail to this query object: a condition, fields and a range.
$query->condition('u.uid', 0, '<>');
$query->fields('u', ['uid', 'name', 'status', 'created', 'access']);
$query->range(0, 50);

This is often written using shorthand syntax that chains multiple object method calls together. So the above code is often written like this:

// Create an object of type Select.
$query = $database->select('users', 'u');

// Add extra detail to this query object: a condition, fields and a range.
$query->condition('u.uid', 0, '<>')
  ->fields('u', ['uid', 'name', 'status', 'created', 'access'])
  ->range(0, 50);

Indeed, the code can, and often is, simplified one step further by chaining the call to $database->select() directly with the method calls for the resulting object. This gives:

// Create an object of type Select and directly add extra detail
// to this query object: a condition, fields and a range.
$query = $database->select('users', 'u')
  ->condition('u.uid', 0, '<>')
  ->fields('u', ['uid', 'name', 'status', 'created', 'access'])
  ->range(0, 50);

This is the simplified query format used by the user administration page, which you can reference for further study.

Executing the Query

Once the query is built, call the execute() method to compile and run the query.

$result = $query->execute();

The execute() method will return a result set / statement object, identical to the object returned by $database->query(), and it can be iterated or fetched in the same way:

$result = $query->execute();
foreach ($result as $record) {
  // Do something with each $record.
}

Note: Be careful when using the following methods with a multi-column dynamic query:

• fetchField()
• fetchAllKeyed()
• fetchCol()

These methods currently require numeric column indexes (0, 1, 2, etc.), not table aliases. However, the query builder does not currently guarantee any specific order for returned fields, so data columns may not be in the order you expect. In particular, expressions are always added after fields, even if you add them first in your query. (This issue does not apply to static queries, which always return data columns in the order you specify.)

Debugging

To inspect the SQL query that the query object uses at a given point in its lifecycle, print the query object. To inspect the arguments, look at the array returned by the arguments() method:

echo $query;
print_r($query->__toString());
print_r($query->arguments());