1

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.

Karol
  • 7,803
  • 9
  • 49
  • 67
  • 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](http://stackoverflow.com/questions/210564/pdo-prepared-statements/21069) – Scuzzy Dec 05 '12 at 01: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 Dec 05 '12 at 01:30
  • 2
    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](http://php.net/manual/en/pdostatement.execute.php#98804) but not going to be exactly what you need for a drupal solution, just the concept. – Scuzzy Dec 05 '12 at 02:27

2 Answers2

2

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.

Karol
  • 7,803
  • 9
  • 49
  • 67
0

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

Nikita Makarov
  • 99
  • 1
  • 3