In Drupal 7 you can query a database table in two ways; using a static query or a dynamic query. A static query is the simplest of the two, all you need to do is pass a query and arguments using the db_query function. Most of your queries should be a static query because they are the fastest and the simplest.
Dynamic queries on the other hand allow other modules to modify a query by implementing the hook_query_alteror hook_query_TAG_alter hook. But beware dynamic queries are slower than static queries.
Here is what a static query looks like:
$result = db_query("SELECT nid, title FROM {node} WHERE type = :type", array(
':type' => 'article',
));
Here is the same query written as a dynamic query:
$query = db_select('node', 'n');
$query
->fields('n', array('nid', 'title'))
->condition('n.type', 'article', '=');
$result = $query->execute();
How to Use dpq()
Writing and debugging a dynamic query can be tough because you can’t easily see the generated SQL string. Using the Devel module you could turn on the “Display query log” settings, but then you’ll be bombarded with a lot of queries to sort through.
Note:
dpq()function only works for dynamic queries that use thedb_selectfunction (SelectQuery object).
The best way to view a generated SQL string is to use the dpq function. Simply pass the $query object through to the dpq function and the SQL string will be displayed in the message region.
$query = db_select('node', 'n');
$query
->fields('n', array('nid', 'title'))
->condition('n.type', 'article', '=');
$result = $query->execute();
dpq($query);
In the image above, you can see the printed SQL string.
Print SQL String without Devel
If you don’t want to use Devel to print the SQL string, run print_r($query->__toString()) to print the SQL string.
Here is an example:
$query = db_select('node', 'n');
$query
->fields('n', array('nid', 'title'))
->condition('n.type', 'article', '=');
$result = $query->execute();
print_r($query->__toString());
I still prefer to use the dpq function because the SQL string is displayed and formatted much better.
Here is an image of the same query being printed using dpq and print_r($query->__toString()).
As you can see the print_r version is just below the toolbar. If the query was larger it’ll be hard to read. It is really a matter of choice if you use Devel or not.
If you have any questions, please leave a comment.
Thanks for the writeup! Actually your last screenshot shows what’s, in my opinion, the biggest difference: placeholder variables!
dpq() automatically replaces them so it leaves you with a query you can almost copy and paste into a mysql tool (just gotta remove those curly brackets), whereas print_r() won’t do that and will let you have to figure out placeholders yourself…
That’s a good observation.
To log EntityFieldQuery’s, use this:
// Add this to your entityfieldquery
->addTag(‘debug’)
Like this:
$query->entityCondition(‘entity_type’, ‘node’)
->entityCondition(‘bundle’, ‘article’)
->propertyCondition(‘status’, 1)
->addTag(‘debug’);
// Add this function to the module
function MODULE_query_alter($query) {
if ($query->hasTag(‘debug’)) {
dpq($query);
}
}
This is because when it reaches the query_alters, it is a query object.
Wow, thanks I’ll try it out.
In some cases you can’t access $query object but you still can debug it:
Database::startLog('node_load_debug');
node_load(1);
dpm(Database::getLog('node_load_debug'));
Database::getLog returns log of all queries which were executed after logging with key ‘node_load_debug’ was started.
Thanks, I never knew about
Database::startLogorDatabase::getLog.You can also just run-
print $query
__toString() is a magic method that (in this instance) turns the object into a string representation of the query.