ToString() method to see raw SQL statement generated by Drupal SelectQuery

So yesterday I wrote another Coderwall post about trying to write an EntityFieldQuery where a fieldCondition is actually a field provided by the Domain Access Module. I ended up having to use the standard SelectQuery class instead.

While I was writing my query, I was using the /devel/php page to test my query results but I kept getting "unexpected errors" when I tried to execute it. I was able to determine that the act of adding the ->execute() method was causing the problem but was having a brain fart and couldn't quite figure out was wrong. In the end, I was not specifying which fields I wanted returned in the query but I was only able to figure that out because of the toString() method made available by the SelectQuery class.

$query = db_select('node', 'n');
$query->innerJoin('domain_source', 'd', 'n.nid = d.nid');
$query->condition('n.type', 'my_node_type_name', '=');
$query->condition('d.domain_id', '-5', '!=');

The database abstraction layer of Drupal is really cool but sometimes you just really want to know what the final SQL query is that Drupal is ultimately running so you can see what might be wrong with the query.

Enter the toString() method of the SelectQuery class.

To fire the __toString() method, you need to cast the $query variable to a string before you actually run the execute() method.

print (string) $query;

Doing so, I was able to determine that I was not selecting any fields and my resultant SQL string was actually missing some required components and of course would be syntactically invalid.

I had something along the lines of the following generated:

SELECT
FROM {node} n
INNER JOIN {domain} d ON (n.nid = d.nid)
WHERE n.type = { :dbplaceholder1 } AND d.domainid = { :dbplaceholder_2 }

It became very obvious at this point that I was not selecting any specific fields. I had previously assumed that if i didn't specify a field, it would run a select * but I was incorrect.

Thanks to Mario from Sydney, Australia who wrote this article that helped me realize my error.