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.
7 Answers
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.
-
1I think that a function like
_get_query_string()
should have been part of theSelectQuery
interface. Oct 19, 2015 at 20:27
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.
-
1Note that this requires the Devel module to be installed. If you do use Devel (I love it), this is the easiest way to go. Feb 20, 2014 at 18:14
-
2
-
Doesn't appear to work in a
try
catch
block when the query fails. So not helpful in my case if I can't debug the broken query.– KieeJul 27, 2016 at 10:21
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());
-
2
-
2No bloat / 3rd party modules required. Plus this works on queries that have not been executed, so you can print a query that fails and gives an error,
dpq
doesn't seem to allow this even in a try/catch.– KieeJul 27, 2016 at 10:25 -
2
-
-
It's simple but not totally accurate: it misses the quote on strings and the NULL handling.– TeenageSep 8, 2021 at 9:11
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.
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.
Drupal 8 Version:
$query = \Drupal::entityQuery('node')
->addTag('debug')
->execute();
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());