How to convert SelectQuery object to SQL string?

1
votes

I managed to print a string using __toString() magic method, but in this string I see placeholders (for conditions params), and it doesn't work as SQL query.

I checked documentation of this object, and also looked in google, but couldn't find a working answer.

2
phpsqldrupaldrupal-7
If it's a prepared SQL statement, then there is no "Compiled" version of the query, it's all handled internally by the database Server. Related question on prepared statements – Scuzzy
Is there any way to execute it manually in let's say phpmyadmin? Or it is only achievable using prepare, set and execute commands? – Karol
You're basically going to need to find a way to emulate how stored procedures work, by working with your query parameters and replacing the placeholders in the statement accordingly. This looked useful but not going to be exactly what you need for a drupal solution, just the concept. – Scuzzy

2 Answers

2
votes

Basing on question's comments (thanks @Scuzzy for inspiration) I wrote some simple piece of code to convert SelectQuery object:

class ExportableSelectQuery {

    public static function toSql(SelectQuery $obj) {

        $_string = $obj->__toString();
        $_conditions = $obj->conditions();
        $_tables = $obj->getTables();
        $_fields = $obj->getFields();

        foreach($_tables as $k => $t) {
            if(!empty($t['alias'])) {
                $_string = str_replace('{' . $t['table'] . '}', $t['table'] . ' as', $_string);
            }
            else {
                $_string = str_replace('{' . $t['table'] . '}', $t['table'], $_string);
            }
        }

        foreach($_conditions as $k => $c) {
            if(is_int($c['value'])) {
                $_string = str_replace(':db_condition_placeholder_' . $k, $c['value'], $_string);
            }
            else {
                $_string = str_replace(':db_condition_placeholder_' . $k, "'" . $c['value'] . "'", $_string);
            }
        }

        //echo('<pre>');
        //var_dump($_fields);
        //var_dump($_conditions);
        //var_dump($_tables);
        //var_dump($_string);
        //echo('</pre>');
        //die();

        return $_string;
    }
}

Usage of this code is now simple (if you only have SelectQuery object somewhere):

die(ExportableSelectQuery::toSql($query));

I was thinking about extending original SelectQuery object, and provide method to get SQL code, but Drupal's db_select function returns SelectQuery, so I will have to either change db_select function or cast returned object to ExportableSelectQuery.

Also this is not probably best solution I could write, but assuming limit of time and purpose it solved my problem just fine.

0
votes

If you wish to get SQL from for example "EntityFieldQyery", you may use something like this

  1. Add tag to query

    $query->entityCondition('entity_type', 'node')
      ->entityCondition('bundle', 'page')
      ->addTag('EFQDumper'); //<=== TAG

  2. Implement hook "query_TAG_alter"

    function YOURMODULE_query_EFQDumper_alter(QueryAlterableInterface $query)
{
 //echo ExportableSelectQuery::toSql($query);
 //die();
}

The solution based on Carlos comment