Last updated July 13, 2014. Created on September 18, 2008.
Edited by vegantriathlete, batigolix, klausi, drupalshrek. 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.

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:

<?php
$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:

<?php
$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:

<?php
// 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:

<?php
// 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:

<?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);
?>

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.

<?php
$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:

<?php
$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:

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

Debugging

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

<?php
print_r
($query->__toString());
print_r($query->arguments());
?>

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

Comments

espressochino’s picture

String comparitors and others can be found at: http://drupal.org/node/310086 and http://drupal.org/node/773090

drunken monkey’s picture

Even though UNION isn't listed here, it is supported. For documentation see the SelectQueryInterface::union() and SelectQueryInterface::getUnion() methods.
Basically, you just do $query->union($otherQuery, 'UNION ALL');. The return value is just $query, no new object is created.

ndmaque’s picture

adding the range after an addfield gives an error

this works

$query = db_select('users', 'u');
$query
->fields('u', array('uid', 'name', 'mail'))
->range(0, 3);

$result = $query->execute();

this works too using fields and field alias

$query = db_select('users', 'u');
$query
->fields('u', array('uid', 'name', 'mail'))
->addField('u', 'pass', 'userpass');

$result = $query->execute();

but gives the error 'Call to a member function range() on a non-object' when the range is added

$query = db_select('users', 'u');
$query
->fields('u', array('uid', 'name', 'mail'))
->addField('u', 'pass', 'userpass')
->range(0, 3);

$result = $query->execute();

not sure why yet.

Xano’s picture

addField() does not return the query object, but the field alias. See http://api.drupal.org/api/drupal/includes--database--select.inc/function....

rajatgusain’s picture

@ndmaque

addField() is not the chain-able function that's why it do not return $query object and we can't add more function after if but we can add more before it.

please refer below link for more clarification :
Chaining

Regards
Rajat Gusain

pcoloma’s picture

Strangely pager extender fails to work when doing some database joins.
Nothing happens I just get a non paginated list from the database.

Solved: the pager statements have to come among the firsts in the definition of the query.

rajatgusain’s picture

@pcoloma
Please refer given below link to create pager :
Pager for drupal 7

Regards
Rajat Gusain

hannanxp’s picture

i hope this clear code example is helpful for others,
mixing db_select() join and paging together:

<?php
$query
= db_select(TABLE_DATA_SIDANG, 'ds')
  ->
condition('tanggal', $tanggal . " 00:00:00")
  ->
extend('PagerDefault')
    ->
limit(10)               
  ->
extend('TableSort')   
    ->
orderByHeader($header)
  ->
fields ('ds', array (
                     
'no_perk',
                     
'tingkat',
                     
'tanggal',
                     
'ruang',
                     
'tim',
                     
'no_majelis',
  ));
   
 
$query->leftJoin(TABLE_DATA_REG_AC, 'ra', 'ra.no_perk = ds.no_perk');
 
$query->fields ('ra', array (
                       
'nama_pe',
                       
'nama_ter',
  ));
 
 
$res = $query->execute();
?>
vlkff’s picture

An important note, that you can use join() only at once so if you need fetch data from a several tables, use addJoin() instead.

For example:

$query = db_select('node', 'n')
->condition('n.type', 'dialysis_center')
->leftJoin('content_field_phone', 'phone', 'n.nid = phone.nid')
->leftJoin('content_field_website', 'website', 'n.nid = phone.nid')
->leftJoin('location_instance', 'lin', 'n.nid = lin.nid')
->leftJoin('location', 'ln', 'lin.lid = lm.lid')
->leftJoin('location_phone', 'lphone', 'lin.lid = lphone.lid')
->fields('n', array('nid', 'title', 'status', 'sticky'))
->fields('lphone', array('phone'))
->fields('website', array('field_website_url'))
->fields('ln', array('name', 'street', 'additional', 'city',
'province', 'postal_code', 'country', 'latitude', 'longitude'));

hayskelly’s picture

It might not be the best code, but if I found this a couple of hours ago, I would have saved myself a lot of time. This is how you get usernames to go with the uids you might get on a query:

<?php
$query
= db_select('node', 'n'); //Select the node table and give it the alias of n
$query->join('users', 'u', 'u.uid = n.uid'); //join users, give it alias u, where u.uid = n.uid
$query
 
->fields('n', array('nid', 'title', 'created', 'uid')) //get these fields from the node table
 
->fields('u', array('name')) //get these fields from the users table
 
->condition('n.status', 1); //only published nodes, the n in n.status is needed
return $query->execute();
?>

Thanks to Willie for getting me on the right track.

saitanay’s picture

if we are sure that the result was a single record, which is the best way to get the record from the result with a loop like this:

<?php
$result
= $query->execute();
foreach (
$result as $record) {
 
// Do something with each $record
}
?>
freebug’s picture

How to write a query with an "OR", something like:
SELECT uid, name, status, created, access FROM {users} u WHERE uid = 10 OR (name = 'xyz' AND uid = 3)

rafinskipg’s picture

<?php
        $db_or
= db_or();
   
$db_or->condition('n.type', 'event', '=');
   
$db_or->condition('n.type', 'article', '=');
   
$query->condition($db_or);
?>

Enjoy!

freebug’s picture

Thanks a lot rafinskipg.

yurtboy’s picture

Thanks for this!

jfarhat’s picture

How can I assign a value to a field if it is NULL. Example

select IFNULL(A,'nothing') A from mytable;

Thanks

J. Farhat

madmanmax’s picture

<?php
$query
= db_select('mytable', 'mt');
$query->addExpression("IFNULL(mt.column, 'nothing')", 'an_alias');
?>
madmanmax’s picture

OK, I have a few questions so in order to save some space I'll just link them to stackoverflow:
- first one is how to preserve the order of the fields in select when using addExpression()
- the second one, could be a bug for all I can tell, but the placeholders values are replaced when using union

Muyiwa’s picture

I am new with Drupal and i wish to limit the number of records returned by this query to one record only(the first from the top). The range() method did not make a difference . So i decided to use the condition method(it has been commented out on line 7). But it did not work either. Pls I need help on this. Thanks!

1.    $query = db_select('student_class_details', 'scd');
2.   $query->join('class'           , 'c' , 'c.class_id = scd.class_id');
3.    $query->join('term_duration'   , 'td', 'td.term_duration_id = scd.term_duration_id');
4.   $query->join('session_term'    , 'st', 'st.term_id = td.term_id');
5.    $query->join('academic_session', 'ac', 'ac.session_id = td.session_id');
6.    $query->condition('scd.uid'    , $uid, '=');
7.    //$query->condition('td.term_end_date'    , max(td.term_end_date), '=');
8.
9.    $query->fields('c' , array('class_name', 'class_suffix', 'class_id'));
10.    $query->fields('td', array('term_duration_id'));
11.    $query->fields('ac', array('session_name'));
12.    $query->fields('st', array('term_name'));
13.    $query->orderBy('td.term_end_date','DESC');
14.    $query->range(0,1);
b8x’s picture

i need

<code><?php
$q1
= "(SELECT IF(uid1<>3,uid1,uid2) FROM table1 AS c WHERE c.id = 28)";
$q_obj->addExpression($q1, 'to');
?>

it gives error. but db_query process this query well.

drupalshrek’s picture

The Lullabot website has a useful article on the pros and cons of Drupal 7 changing the database APIs to be more object-oriented:
http://www.lullabot.com/articles/simplify-your-code-with-drupal-7s-datab...

drupalshrek
Please fill in my Learning a foreign language questionnaire if you have a moment.