Print the query which is built using db_select()

Question

I want to print the query which is built using db_select() in a programmatical way. Is there any API function provided by Drupal Abstraction Layer?
It is similar to query output in Views, but I want to print it from my custom module for debugging purpose.

Answer 1

SelectQuery implements SelectQuery::__toString(), which is called in the contexts where a string is required.

Consider the following code.

global $theme_key;

$query = db_select('block')
  ->condition('theme', $theme_key)
  ->condition('status', 1)
  ->fields('block');

print $query;

Its output is the following one.

SELECT block.*
FROM 
{block} block
WHERE  (theme = :db_condition_placeholder_0) AND (status = :db_condition_placeholder_1)

To get the array of arguments used for the query, you can call SelectQuery::arguments().

The following code prints the query and its arguments using the functions made available from the Devel module.

global $theme_key;

$query = db_select('block')
  ->condition('theme', $theme_key)
  ->condition('status', 1)
  ->fields('block');

dpm((string) $query);
dpm($query->arguments());

The Devel module is not necessary, though, and you could drupal_set_message() to show the output. For example, you could use the following function to get a string with the placeholders replaced by their actual values.

function _get_query_string(SelectQueryInterface $query) {
  $string = (string) $query;
  $arguments = $query->arguments();

  if (!empty($arguments) && is_array($arguments)) {
    foreach ($arguments as $placeholder => &$value) {
      if (is_string($value)) {
        $value = "'$value'";
      }
    }

    $string = strtr($string, $arguments);
  }

  return $string;
}

The previous example code I shown would become the following one.

global $theme_key;

$query = db_select('block')
  ->condition('theme', $theme_key)
  ->condition('status', 1)
  ->fields('block');

drupal_set_message(format_string('Query: %query', array('%query' => _get_query_string($query))));

function _get_query_string(SelectQueryInterface $query) {
  $string = (string) $query;
  $arguments = $query->arguments();

  if (!empty($arguments) && is_array($arguments)) {
    foreach ($arguments as $placeholder => &$value) {
      if (is_string($value)) {
        $value = "'$value'";
      }
    }

    $string = strtr($string, $arguments);
  }

  return $string;
}

Notice that SelectQuery::arguments() returns the array of query arguments only when it is called after SelectQuery::__toString(), SelectQuery::compile(), or SelectQuery::execute(); otherwise, SelectQuery::arguments() returns NULL.

You could use a function similar to the following one to get the string query, with the placeholders replaced with the arguments.

Answer 2

You can use dpq() to display the query, and dpr() to display the result.

  $query = db_select('users','u');
  $query->fields('u');
  $query->condition('u.uid', 1042);
  $result = $query->execute()->fetchAll();

  dpq($query); // Display the query. 
  dpr($result); // Display the query result.

Answer 3

Another option is:

global $theme_key;

$query = db_select('block')
  ->condition('theme', $theme_key)
  ->condition('status', 1)
  ->fields('block');

print strtr((string) $query, $query->arguments());

Answer 4

The above answers are good when you have Devel installed and configured.

Best way to print the query without Devel is as below.

$query = db_select('block')
->condition('theme', $theme_key)
->condition('status', 1)
->fields('block');
//One way
echo $query->__toString();
// Second way
echo (string)$query;

We can use one of the above ways to print the query.

Answer 5

I have a good solution that you can copy/paste your query string directly in "SQL" section in Phpmyadmin and debug your query ( I often use this method when I struggle with query)

$querystring=$query->__toString();
$querystring=str_replace("{",'',$querystring);
$querystring=str_replace("}",'',$querystring);
foreach($query->getArguments() as $key=> $item){

    if(!$item) {
        $item = 'NULL';
    }
    $querystring=str_replace($key.')',$item.')',$querystring);
}
dpm($querystring);

I hope this be usful for other guys.

Answer 6

Drupal 8 Version:

$query = \Drupal::entityQuery('node')
    ->addTag('debug')
    ->execute();

Answer 7

We can print SQL String without Devel by print_r($query->__toString);

  $query = db_select('node', 'n');
  $query
      ->fields('n', array('nid', 'title'))
      ->condition('n.type', 'article', '=');
  $result = $query->execute();
  
  print_r($query->__toString());