Quick tip: getting the SQL built with db_select() in Drupal 7 and Drupal 8

Fri, 02/09/2016 - 15:03

When building more complex queries using db_select() you will often want to see the exact SQL being generated. This is helpful for understanding the query or simply debugging the results.

This article will show you how to see the exact query being generated with db_select() as well as getting how to get all values passed as arguments. The code in this article will be applicable for both Drupal 7 and Drupal 8.

Let's see some examples on how we would build a simple query.

Example #1: using db_select() and creating the query dynamically:

$results = db_select('node', 'n')
  ->fields('n')
  ->condition('n.type', 'page')
  ->execute();

Example #2: you could also use db_query() instead. The above query would translate into the following:

$results = db_query("SELECT * FROM {node} n WHERE n.type = :type", array(
  ':type' => 'page',
));

Both queries above result in the following SQL:

SELECT * FROM node n WHERE n.type = 'page';

The example #1 is preferred way of building queries because you can alter them dynamically and "tag" the queries to provide additional functionality (e.g. node_access). However, the approach #2 has two positives:

  1. Performance. It will be faster than queries built using db_select().
  2. Readability. You are writing the exact SQL code with some Drupal added extras, such as automatic table prefixes (wrapping table names in curly brackets) and placeholders (keywords starting with colons).

To aid you with readability, there are 2 methods you will find useful:

  1. __toString() which translates your query into something that could be plugged in db_query(). Do note that the placeholder replacement values will not be included here.
  2. arguments() which will return the array of arguments with their values.

Finally, here's the code you can use to see the exact SQL:

$query = db_select('node', 'n')
  ->fields('n')
  ->condition('n.type', 'page');

// The following method is manual way to see the query.
//
// Prints the query with placeholders as a string.
dpm($query->__toString());
// Print the provided arguments, so you can see exactly which values are passed.
dpm($query->arguments());

UPDATE: Anton Sidashin made a great point of using dpq() which is a helper function provided by the Devel module. It automatically prints (or returns) the query and replaces all arguments. To use it, just pass the pre-executed query as the first argument. Setting the second argument to TRUE will make the function return the value instead of printing it out.

$query = db_select('node', 'n')
  ->fields('n')
  ->condition('n.type', 'page');

// The following method will get you the compiled end query with all values
// passed as arguments.
dpq($query);

Two notes for the end:

  • You can call dpq(), __toString() and arguments() only before the query has been executed.
  • The db_select() queries are just provided as examples. You will most likely want to tag the query with node_access so the query results can be filtered to include only the nodes current user has access to. Since this is outside of the scope of the article, I did not include it in the above samples.

See also: