Dynamic queries

Last updated June 30, 2011. Created by Xano on September 18, 2008.
Edited by xjm, kiamlaluno, tutumlum, nicholasThompson. Log in to edit this page.

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 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.

Table Of Contents

1. The Big Picture
2. Joins
3. Fields
4. Distinct
5. Expressions
6. Ordering
7. Random Ordering
8. Grouping
9. Ranges and Limits
10. Table Sorting
11. Conditionals
12. Executing The Query
13. Count Queries
14. Debugging

Dynamic select queries are started using the db_select() function as follows:

In this case, "node" 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.

Dynamic select queries can be very simple or very complex. We will cover the basic principles of how they work here, but an exhaustive treatment would be a book unto itself.

The Big Picture

Here is a relatively simple query of the users table. Below we'll look at the individual parts that make up this query, and more advanced techniques like joins.

The above is roughly equivalent to
$result = db_query("SELECT uid, name, status, created, access FROM {users} u WHERE uid <> 0 LIMIT 50 OFFSET 0");

It is a simplified form of the query used by the user administration page, which can be referenced for further study.

Joins

To join against another table, use the join(), innerJoin(), leftJoin(), or rightJoin() methods, like so:

The above directive will add an INNER JOIN (the default join type) against the "user" table, which will get an alias of "u". The join will be ON the condition " n.uid = u.uid AND u.uid = :uid", where :uid has a value of 5. Note the use of a prepared statement fragment. That allows for the addition of variable join statements in a secure fashion. Never put a literal value or variable directly into a query fragment, just as literals and variables should never be placed into a static query directly (they can lead to SQL injection vulnerabilities). The innerJoin(), leftJoin(), and rightJoin() methods operate identically for their respective join types.

The return value of a join method is the alias of the table that was assigned. If an alias is specified it will be used except in the rare case that alias is already in use by a different table. In that case, the system will assign a different alias.

Note that in place of a literal such as 'user' for the table name, all of the join methods will accept a select query as their first argument. Example:

Fields

To add a field to the Select query, use the addField() method:

The above code will instruct the query to select the "title" field of the table with alias "n", and give it an alias of "my_title". If no alias is specified, one will be generated automatically. In the vast majority of cases the generated alias will simply be the field name. In this example, that would be "title". If that alias already exists, the alias will be the table name and field name. In this example, that would be "n_title". If that alias already exists, a counter will be added to the alias until an unused alias is found, such as "n_title_2".

Note that if you are creating and populating the query yourself and do not specify an alias and the default alias is not available, there is almost certainly a bug in your code. If you are writing a hook_query_alter() implementation, however, you cannot know with certainty what aliases are already in use so you should always use the generated alias.

To select multiple fields, simply call addField() multiple times in the order desired. Note that in most cases the order of fields should not matter, and if it does then there is likely a flaw in the business logic of the module.

As an alternate shorthand, you can use the fields() method to add multiple fields at once.

The above method is equivalent to calling addField() four times, once for each field. However, fields() does not support specifying an alias for a field. It also returns the query object itself so that the method may be chained rather than returning any generated aliases. If you need to know the generated alias, either use addField() or use getFields() to access the raw internal fields structure.

Calling fields() with no field list will result in a "SELECT *" query.

That will result in "n.*" being included in the field list of the query. Note that no aliases will be generated. If a table using SELECT * contains a field that is also specified directly from another table, it is possible for a field name collision to occur in the result set. In that case, the result set will only contain one of the fields with the common name. For that reason the SELECT * usage is discouraged.

Distinct

Some SQL queries may produce duplicate results. In such cases, duplicate rows may be filtered out using the "DISTINCT" keyword in a static query. In a dynamic query, use the distinct() method.

Note that DISTINCT can introduce a performance penalty, so do not use it unless there is no other way to restrict the result set to avoid duplicates.

Expressions

The Select query builder supports the use of expressions in the field list. Examples of expressions include "twice the age field", "a count of all name fields", and a substring of the title field. Be aware that many expressions may use SQL functions, and not all SQL functions are standardized across all databases. It is up to the module developer to ensure that only cross-database compatible expressions are used. (Refer to this list: http://drupal.org/node/773090)

To add an expression to a query, use the addExpression() method.

The first line above will add "COUNT(uid) AS uid_count" to the query. The second parameter is the alias for the field. In the rare case that alias is already in use, a new one will be generated and the return value of addExpression() will be the alias used. If no alias is specified, a default of "expression" (or expression_2, expression_3, etc.) will be generated.

The optional third parameter is an associative array of placeholder values to use as part of the expression.

Note that some expressions may not function unless accompanied by a Group By clause. It is up to the developer to ensure that the query that is generated is in fact valid.

Ordering

To add an order by clause to a dynamic query, use the orderBy() method:

The above code will instruct the query to sort by the title field in descending order. The second parameter may be either "ASC" or "DESC" for ascending or descending, respectively, and defaults to "ASC". Note that the field name here should be the alias created by the addField() or addExpression() methods, so in most cases you will want to use the return value from those methods here to ensure the correct alias is used. To order by multiple fields, simply call orderBy() multiple times in the order desired.

Random ordering

Random ordering of queries requires slightly different syntax on different databases. Therefore, that is best handled by a dynamic query.

To indicate that a given query should order randomly, call the orderRandom() method on it.

Note that orderRandom() is chainable, and stackable with orderBy(). That is, it is safe to do something like the following:

The above would order first by the "term" field of the query and then, for records that have the same term, order randomly.

Grouping

To group by a given field, use the groupBy() method.

The above code will instruct the query to group by the uid field. Note that the field name here should be the alias created by the addField() or addExpression() methods, so in most cases you will want to use the return value from those methods here to ensure the correct alias is used. To group by multiple fields, simply call groupBy() multiple times in the order desired.

Ranges and Limits

Queries may also be restricted to a certain subset of the records found. In general this is known as a "range query". In MySQL, this is implemented using the LIMIT clause. To limit the range of a query, use the range() method:

The above code will instruct the result set to start at the 5th record found rather than the first, and to return only 10 records. In most cases one will want "the first n records". To do that, pass 0 as the first argument and n as the second.

Calling the range() method a second time will overwrite previous values. Calling it with no parameters will remove all range restrictions on the query.

Table sorting

To produce a result table which can be sorted by any column, use the TableSort extender and then add the table header. Note that an extender does return a new query object that you need to use from that point on.

Conditionals

Conditionals are a complex subject and are shared by Select, Update, and Delete queries. They are therefore explained separately. Unlike Update and Delete queries, however, 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().

Executing the query

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

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:

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

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

These methods currently require numeric column indicies (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.)

Count queries

Any query may have a corresponding "count query". The count query returns the number of rows in the original query. To obtain a count query, use the countQuery() method.

$count_query is now a new Dynamic Select query with no ordering restrictions that when executed will return a result set with only one value, the number of records that would be matched by the original query. Because PHP supports chaining methods on returned objects, the following idiom is a common approach:

Debugging

To examine the SQL query that the query object will build at a particular point in its lifecycle, call its __toString() method: