Drupal 7 - Dynamic Queries

Dynamic Queries

<?php// 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);
//Execute the query
$result = $query->execute();
?> 

Conditional Clauses

There are two main methods that apply for all conditional objects:

$query->condition($field, $value = NULL, $operator = '=')

The condition() method allows for adding a standard $field $value $operator format of conditional fragment. That includes any case where the condition is a binary comparison such as =, <, >=, LIKE, etc. If no operator is specified, = is assumed. That means the most common case would be condition('myfield', $value), which results in a conditional fragment of myfield = :value, where :value will be replaced with $value when the query is run.

<?php
$query->condition('myfield', array(1, 2, 3), 'IN');// Becomes: myfield IN (:db_placeholder_1, :db_placeholder_2, :db_placeholder_3)?>

$query->where($snippet, $args = array())

The where() method allows for the addition of arbitrary SQL as a conditional fragment.$snippet may contain any legal SQL fragment, and if it has variable content it must be added using a named placeholder. The $args array is an array of placeholders and values that will be substituted into the snippet. It is up to the developer to ensure that the snippet is valid SQL. No database-specific modifications are made to the snippet.

 

Joins

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

<?php
$query = db_select('node', 'n');
$table_alias = $query->join('users', 'u', 'n.uid = u.uid AND u.uid = :uid', array(':uid' => 5));?>

 

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.

<?php
$count_query = $query->countQuery();?>

 

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.

<?php// Force filtering of duplicate records in the result set.$query->distinct()?>

 

Grouping

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

<?php
$query->groupBy('uid');?>

Ordering

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

<?php
$query->orderBy('title', 'DESC');?>

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.

In most cases we want "the first n records". To do that, pass 0 as the first argument and n as the second.

<?php// Limit the result to 10 records$query->range(0, 10);?>

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.

<?php
$query = $query
  ->extend('TableSort')
  ->orderByHeader($header);?>