Working with a Database in Drupal 7 - Lesson 4 - Dynamic Queries (SELECT)

We’ve now reached perhaps the most exciting part of Drupal’s Database API: dynamic queries. These are called "dynamic" because Drupal appends the query string on the fly. All INSERT, UPDATE, DELETE, or MERGE queries can be dynamic. SELECT queries can be either static or dynamic. However, it is recommended to use dynamic queries even for SELECT operations.

All dynamic queries are created as a query object that you build when needed. Like static queries, most dynamic queries use a procedural wrapper. Subsequent query parameters are added to the query object using method chaining.

db_select() SELECT Queries

Dynamic SELECT queries begin with the db_select() function, such as:

Here, 'node' is the table being queried. This corresponds to the FROM clause in SQL. Note that the table name is not wrapped in curly braces – the query builder handles this automatically. The second parameter is the alias for the table. If not provided, the table name is used as the alias. The optional $options array is the same as in static queries.

Another db_select() Example

condition('u.uid', 0, '<>')
  ->fields('u', array('uid', 'name', 'status', 'created', 'access'))
  ->range(0, 50);
$result = $query->execute();
?>

This is equivalent to:

SELECT uid, name, status, created, access FROM {users} u WHERE uid > 0 LIMIT 50 OFFSET 0;

Joins

join('user', 'u', 'n.uid = u.uid AND u.uid = :uid', array(':uid' => 5));
?>

In this INNER JOIN, we're joining the "user" table using alias "u" and the ON condition includes a placeholder value. Never directly insert variables into SQL strings to avoid injection risks.

Fields: addField()

addField('n', 'title', 'my_title');
?>

Use fields() to select multiple fields:

fields('n', array('nid', 'title', 'created', 'uid'));
?>

Calling fields('n') with no second argument is equivalent to SELECT *.

DISTINCT

distinct();
?>

Removes duplicate rows from the result set.

Expressions: addExpression()

addExpression('COUNT(uid)', 'uid_count');
$count_alias = $query->addExpression('created - :offset', 'uid_count', array(':offset' => 3600));
?>

ORDER BY

orderBy('title', 'DESC');
?>

Random Order

orderRandom();
?>

You can combine this with orderBy:

orderBy('term')->orderRandom()->execute();
?>

GROUP BY

groupBy('uid');
?>

Range and Limit

range(5, 10); // Returns 10 records starting from the 6th row
?>

TableSort Extension

extend('TableSort')
  ->orderByHeader($header);
?>

Conditions

Use condition() or where() for WHERE clauses. For HAVING, use havingCondition() or having().

Execute the Query

execute();
foreach ($result as $record) {
  // process each $record
}
?>

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

Count Query

countQuery();
$num_rows = $count_query->execute()->fetchField();
?>

Debugging

__toString());
?>