How to convert SelectQuery object to SQL string?

Question

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.

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
– Scuzzy, Commented Dec 5, 2012 at 1:29
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
– Karol, Commented Dec 5, 2012 at 1:30
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
– Scuzzy, Commented Dec 5, 2012 at 2:27

Karol · Accepted Answer · 2012-12-05 04:33:35Z

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.

Nikita Makarov · Accepted Answer · 2014-04-22 11:18:01Z

0

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

Add tag to query

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

Implement hook "query_TAG_alter"

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

The solution based on Carlos comment

answered Apr 22, 2014 at 11:18

Nikita Makarov

991 silver badge3 bronze badges

Collectives™ on Stack Overflow

How to convert SelectQuery object to SQL string?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related