Introduction to Dynamic Queries

Dynamic queries refer to queries that are built dynamically by Drupal rather than provided as an explicit query string. All insert, update, delete, and merge queries must be dynamic. Select queries may be either static or dynamic. Therefore, "dynamic query" generally refers to a dynamic select query.

All dynamically built queries are constructed using a query object, requested from the appropriate database connection object. As with static queries, in the vast majority of cases the procedural wrapper may be used to request the object. Subsequent directives to the query, however, take the form of methods invoked on the query object.

Dynamic select queries are started using the select() method like in the following code.

$database = \Drupal::database();
$query = $database->select('mytable', 'mt', $options);
$query->fields('mt', ['field_1', 'field_2']);

In this case, 'mytable' is the base table for the query, the table used from the FROM statement. Note that it should not have brackets around it. The query builder will handle that automatically. The second parameter is the table alias; if not specified, the name of the table will be used as alias.

The value returned by $database->select() is an instance of Select.

Note that field values are required to execute a dynamic query so either the addField() or fields() method is required in all dynamic queries. The query will be syntactically invalid and will fail if one of these methods is ommitted. See Fields. 

Dynamic select queries can be very simple or very complex. The next sections will describe the individual parts that make  simple query; the following pages will describe more advanced techniques.

Options Array

The $options parameter is optional and it's identical to the $options array for static queries. See Static Queries Options.

The Big Picture

This is a relatively simple query for the users table.

Let's say we want to create a dynamic query which is roughly equivalent to the following static query.

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

The dynamic query would use the following code.

// 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_field_data', 'u')
  ->condition('u.uid', 0, '<>')
  ->fields('u', ['uid', 'name', 'status', 'created', 'access'])
  ->range(0, 50);

Executing the query

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

$result = $query->execute();

The execute() method will return a result set / statement object that is identical to that returned by $database->query(); it may be iterated or fetched in exactly the same way.

$result = $query->execute();
foreach ($result as $record) {
  // Do something with each $record.
  // A field named `field_1` in $record is accessible via `$record->field_1`.
}

See the section on result sets for more details.

Debugging

To examine the SQL query the query object produces, print the query object. To examine the arguments, look at the array returned by arguments().

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