Doctrine - How to print out the real sql, not just the prepared statement?

Question

We're using Doctrine, a PHP ORM. I am creating a query like this:

$q = Doctrine_Query::create()->select('id')->from('MyTable');

and then in the function I'm adding in various where clauses and things as appropriate, like this

$q->where('normalisedname = ? OR name = ?', array($string, $originalString));

Later on, before execute()-ing that query object, I want to print out the raw SQL in order to examine it, and do this:

$q->getSQLQuery();

However that only prints out the prepared statement, not the full query. I want to see what it is sending to the MySQL, but instead it is printing out a prepared statement, including ?'s. Is there some way to see the 'full' query?

user_138475 · Answer

Doctrine is not sending a "real SQL query" to the database server : it is actually using prepared statements, which means :

Sending the statement, for it to be prepared (this is what is returned by $query->getSql())
And, then, sending the parameters (returned by $query->getParameters())
and executing the prepared statements

This means there is never a "real" SQL query on the PHP side — so, Doctrine cannot display it.

user_189179 · Answer

There is no other real query, this is how prepared statements work. The values are bound in the database server, not in the application layer.

See my answer to this question: In PHP with PDO, how to check the final SQL parametrized query?

(Repeated here for convenience:)

Using prepared statements with parametrised values is not simply another way to dynamically create a string of SQL. You create a prepared statement at the database, and then send the parameter values alone.

So what is probably sent to the database will be a PREPARE ..., then SET ... and finally EXECUTE ....

You won't be able to get some SQL string like SELECT * FROM ..., even if it would produce equivalent results, because no such query was ever actually sent to the database.

user_811120 · Answer

TL;DR

$qb = ... // your query builder
$query = $qb->getQuery();
// temporarily enable logging for your query (will also work in prod env)
$conf = $query->getEntityManager()->getConnection()->getConfiguration();
$backupLogger = $conf->getSQLLogger();
$logger = new \Doctrine\DBAL\Logging\DebugStack();
$conf->setSQLLogger($logger);
// execute query
$res = $query->getResult();
$conf->setSQLLogger($backupLogger); //restore logger for other queries
$params = [
  'query' => array_pop($logger->queries) //extract query log details
  //your other twig params here...
]
return $params; //send this to your twig template...

in your twig files, use Doctrine's twig helpers filters:

// show raw query:
{{ (query.sql ~ ';')|doctrine_replace_query_parameters(query.params)
// highlighted
{{ (query.sql ~ ';')|doctrine_replace_query_parameters(query.params)|doctrine_pretty_query(highlight_only = true) }}
// highlighted and formatted (i.e. with tabs and newlines)
{{ (query.sql ~ ';')|doctrine_replace_query_parameters(query.params)|doctrine_pretty_query }}

Explanation:

The other answers mentioning that Prepared statement are actually "real queries" are right, but they don't answer the obvious asker's expectation... Every developer wants to display a "runnable query" for debugging (or to display it to the user).

So, I looked into Symfony profiler's source to see how they do it. The Doctrine part is Doctrine's responsibility so they made a doctrine-bundle to integrate with Symfony. Having a look at the doctrine-bundle/Resources/views/Collector/db.html.twig file, you will find out how they do it (this might change across versions). Interestingly, they created twig filters that we can reuse (see above).

For everything to work we need to enable Logging for our query. There are multiple ways to do this and here I use DebugStack which allows to log queries without actually printing them. This also ensure that this will work in production mode if this is what you need...

If you need further formatting, you will see that they include some CSS in a style tag, so simply "steal" it ^^:

.highlight pre { margin: 0; white-space: pre-wrap; }
.highlight .keyword   { color: #8959A8; font-weight: bold; }
.highlight .word      { color: #222222; }
.highlight .variable  { color: #916319; }
.highlight .symbol    { color: #222222; }
.highlight .comment   { color: #999999; }
.highlight .backtick  { color: #718C00; }
.highlight .string    { color: #718C00; }
.highlight .number    { color: #F5871F; font-weight: bold; }
.highlight .error     { color: #C82829; }

Hope, this will help ;-)

user_12125244 · Answer

I made some research for this topic, because i wanted to debug a generated SQL query and execute it in the sql editor. As seen in all the answers, it is a highly technical topic.

When i assume that the initial question is base on dev-env, one very simple answer is missing at the moment. You can just use the build in Symfony profiler. Just click on the Doctrine Tab, Scroll to the query you want to inspect. Then click on "view runnable query" and you can paste your query directly in your SQL editor

More UI base approach but very quick and without debugging code overhead.

user_4809264 · Answer

$sql = $query->getSQL();

$parameters = [];
    foreach ($query->getParameters() as $parameter) {
        $parameters[] = $parameter->getValue();
    }

$result = $connection->executeQuery($sql, $parameters)
        ->fetchAll();

user_6737670 · Answer

Modified @dsamblas function to work when parameters are date strings like this '2019-01-01' and when there is array passed using IN like

$qb->expr()->in('ps.code', ':activeCodes'),

. So do everything what dsamblas wrote, but replace startQuery with this one or see the differences and add my code. (in case he modified something in his function and my version does not have modifications).

public function startQuery($sql, array $params = null, array $types = null)

{
    if($this->isLoggable($sql)){
        if(!empty($params)){
            foreach ($params as $key=>$param) {

                try {
                    $type=Type::getType($types[$key]);
                    $value=$type->convertToDatabaseValue($param,$this->dbPlatform);
                } catch (Exception $e) {
                    if (is_array($param)) {
                        // connect arrays like ("A", "R", "C") for SQL IN
                        $value = '"' . implode('","', $param) . '"';
                    } else {
                        $value = $param; // case when there are date strings
                    }
                }

                $sql = join(var_export($value, true), explode('?', $sql, 2));
            }

        }
        echo $sql . " ;".PHP_EOL;
    }
}

Did not test much.

user_5546916 · Answer

$sql = $query->getSQL();
$obj->mapDQLParametersNamesToSQL($query->getDQL(), $sql);
echo $sql;//to see parameters names in sql
$obj->mapDQLParametersValuesToSQL($query->getParameters(), $sql);
echo $sql;//to see parameters values in sql

public function mapDQLParametersNamesToSQL($dql, &$sql)
{
    $matches = [];
    $parameterNamePattern = '/:\w+/';
    /** Found parameter names in DQL */
    preg_match_all($parameterNamePattern, $dql, $matches);
    if (empty($matches[0])) {
        return;
    }
    $needle = '?';
    foreach ($matches[0] as $match) {
        $strPos = strpos($sql, $needle);
        if ($strPos !== false) {
            /** Paste parameter names in SQL */
            $sql = substr_replace($sql, $match, $strPos, strlen($needle));
        }
    }
}

public function mapDQLParametersValuesToSQL($parameters, &$sql)
{
    $matches = [];
    $parameterNamePattern = '/:\w+/';
    /** Found parameter names in SQL */
    preg_match_all($parameterNamePattern, $sql, $matches);
    if (empty($matches[0])) {
        return;
    }
    foreach ($matches[0] as $parameterName) {
        $strPos = strpos($sql, $parameterName);
        if ($strPos !== false) {
            foreach ($parameters as $parameter) {
                /** @var \Doctrine\ORM\Query\Parameter $parameter */
                if ($parameterName !== ':' . $parameter->getName()) {
                    continue;
                }
                $parameterValue = $parameter->getValue();
                if (is_string($parameterValue)) {
                    $parameterValue = "'$parameterValue'";
                }
                if (is_array($parameterValue)) {
                    foreach ($parameterValue as $key => $value) {
                        if (is_string($value)) {
                            $parameterValue[$key] = "'$value'";
                        }
                    }
                    $parameterValue = implode(', ', $parameterValue);
                }
                /** Paste parameter values in SQL */
                $sql = substr_replace($sql, $parameterValue, $strPos, strlen($parameterName));
            }
        }
    }
}

user_6550011 · Answer

You can build an sql string by combining the sql prepared statement with bindings like this way:

$sql = str_replace_array('?', $query->getBindings(), $query->toSql())

str_replace_array(string $search, array $replacement, string $subject): string

PHP's str_replace_array function replaces each instance of $search in $subject with values from $replacement array sequentially.

Doctrine - How to print out the real sql, not just the prepared statement?

20 Answers