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.

Note: in 90% of select query use cases you will have a static query. If in a critical performance path, you should use db_query() and friends instead of db_select() for performance reasons. Only use dynamic queries if the query parts vary (example: adding WHERE conditions depending on the context) or if they should be alterable (example: node access). Note: Varied opinions exist on this ... see the discussion at #1881146: [policy] Decide when to use db_select() and when db_query() (or the appropriate connection commands) and #835068: Document usage of static vs dynamic queries.

Drupal 7 no longer has db_rewrite_sql and you must use a dynamic query to accomplish the same thing. For example, whenever querying the node table you should make use of the "node_access" tag, as such:

$query = db_select('node', 'n')
  ->addTag('node_access');

All dynamically built queries are constructed using a query object, requested from the appropriate 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 db_select() function as follows:

$query = db_select('users', 'u', $options);

In this case, "users" is the base table for the query; that is, the first table after the FROM statement. Note that it should not have brackets around it. The query builder will handle that automatically. The second parameter is the alias for the table. If not specified, the name of the table is used. The $options array is optional, and is identical to the $options array for static queries.

The value returned by the db_select() call is an object of type SelectQuery. Therefore, the type of value in the $query variable after this call is an object of type SelectQuery. This object has a whole list of methods such as 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'll look at the individual parts that make up a simple query, and on the following pages we will look at more advanced techniques like joins.

The Big Picture

Here is a relatively simple query of the users table.

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

$result = db_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 SelectQuery
$query = db_select('users', 'u');
 
// Add extra detail to this query object: a condition, fields and a range
$query->condition('u.uid', 0, '<>');
$query->fields('u', array('uid', 'name', 'status', 'created', 'access'));
$query->range(0, 50);

This is often written using the shorthand syntax used to call multiple methods at the same time on the $query object. The code above would therefore often be written as follows:

// Create an object of type SelectQuery
$query = db_select('users', 'u');
 
// Add extra detail to this query object: a condition, fields and a range
$query->condition('u.uid', 0, '<>')
  ->fields('u', array('uid', 'name', 'status', 'created', 'access'))
  ->range(0, 50);

Indeed, the code can be and often is simplified one step further, by chaining the call to db_select() directly with the method calls on the resulting object. This gives:

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

It is a simplified form of the query used by the user administration page, which can be referenced 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 that is identical to that returned by db_query(), and it may be iterated or fetched in the exact 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:

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

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

Debugging

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

print $query;
print_r($query->__toString());
print_r($query->arguments());
print strtr($query->__toString(), $query->arguments());