How do I view the parameters in a query?

Question

In order to debug my code I would like to see the explicit sql query that is executed.

I create the query with createQueryBuilder, and the most explicit thing I achieved is having the raw query using:

$qb->getQuery()->getSQL();

The problem is that instead of parameters I see the holders (?). I found some solutions on the web but they are for 1.3 and 1.4, nothing for Symfony-2.

Ideas? Thanks!

Rancho

Actually, your question is a duplicate of this one https://mcmap.net/q/126209/-doctrine-how-to-print-out-the-real-sql-not-just-the-prepared-statement/795876. Doctrine is using prepared statement, thus there is never a "real" SQL query on the PHP side and Doctrine cannot display it. However you can read this solution https://mcmap.net/q/126209/-doctrine-how-to-print-out-the-real-sql-not-just-the-prepared-statement for your debugging purposes. – Edgar

Also, doctrine 2 have the sqlLogger class: doctrine-orm.readthedocs.org/en/2.0.x/reference/… – Labium

fsehat - looks like this solution is for Symfony 1.4, I couldn't find in the whole project neither getSqlQuery function nor getFlattenedParams (which is the function that I am missing). manix - do you have some reference for more documentation how to use the logger in Symfony 2.x? It looks like a good solution but I don't find any good documentation. Thanks for both! – Rancho

Answer 1

You can access the parameters used by the placeholders using $query->getParameters(), so you could debug your query using:

$query = $qb->getQuery();
print_r(array(
    'sql'        => $query->getSQL(),
    'parameters' => $query->getParameters(),
));

Lynellelynett

Basically it is a nice idea. The problem is that the parameters bag might contain objects, and then trying to print it give huge string. What I would like to do is to convert the parameters bag which is returned by $query->getParameters() to an array of strings which can replace the placeholders ?. – Rancho

I think that this array is already an array of scalar values, if you look at the setParameter method github.com/doctrine/doctrine2/blob/2.2/lib/Doctrine/ORM/… , it call processParameterValue that transforms array and objects to scalar values github.com/doctrine/doctrine2/blob/2.2/lib/Doctrine/ORM/… – Lynellelynett

Funny, I got (as far as I understand...) the last version of all vendors, and my AbstractQuery.php and Query.php are a little bit different. Probably what I have is Doctrine 2.1 while this code is 2.2. Anyway, I added a method to my Query.php file which returns the raw sql + parameters, based on your answers. Thanks! – Rancho

From what I see the data you can get back is a lot less useful than what you put in. the getSQL() shows ? placeholders, even if you used :namedPlaceholders. The getParamaters() does not tell how it relates to those placeholders. Take a statement with 3 placeholders, and two key/pairs (reusing one param in the statement) how can you tell what value to plug where? – Mace

^ The getDQLParts() kinda completes the picture if you're performing a hack similar to what I was needing. – Mace

Answer 2

You can easily access the SQL parameters using the following approach.

   $result = $qb->getQuery()->getSQL();

   $param_values = '';  
   $col_names = '';   

   foreach ($result->getParameters() as $index => $param){              
            $param_values .= $param->getValue().',';
            $col_names .= $param->getName().',';
   } 

   //echo rtrim($param_values,',');
   //echo rtrim($col_names,',');    

So if you printed out the $param_values and $col_names , you can get the parameter values passing through the sql and respective column names.

Note : If $param returns an array, you need to re iterate, as parameters inside IN (:?) usually comes is as a nested array.

Meantime if you found another approach, please be kind enough to share with us :)

Thank you!

Sides

Answer 3

I had to build a requete union (impossible with DQL or QueryBuilder) with 5 query already built with the QueryBuilder. So I reuse these queries but I had a problem using getParameters() function because it give the parameter in same order you have given it. One of the advantages when you use the query builder is you can create a query in order yhou want but when you retrieve parameters, you may retrieve it in messy. to avoid this i have built the following function:

    $getSqlWithParams = \Closure::bind(function(){
        return [$this->getSql(), $this->processParameterMappings($this->_parserResult->getParameterMappings())];
    }, null, Query::class);

now when you want retrieve sql and the sorted parameters you do :

$getSqlWithParams()->call($query)

Don't forget use \Doctrine\ORM\Query statement. And voilà!

Smack

Answer 4

I've also been looking for a way to get parameter-injected SQL from a DQL query to aid in debugging by allowing me to output an SQL string that I can directly paste into phpmyadmin, for instance, and add explain to it, etc.

Anyway I based my answer on Néo's answer which, since I wasn't able to get it to work due to private method calls, I adapted by creating a function inside Doctrine\ORM\Query, as per below:

/**
 * Execute query and return the SQL with params injected.
 *
 * @return string
 * @throws QueryException
 */
public function executeAndGetSqlWithParams(): string
{
    // Execute the query to get the parser result.
    $this->execute();

    // Declare the SQL for use in the vsprintf function.
    $sql = str_replace('?', '%s', $this->getSQL());

    // Declare the SQL parameter mappings.
    $parameterMappings = $this->processParameterMappings($this->_parserResult->getParameterMappings());

    /**
     * TODO: Possibly replace each question mark by the correct vsprintf argument using $parameterMappings[1].
     *
     * Right now all parameters are treated as strings.
     */

    // Declare and define the SQL parameters.
    $sqlParameters = [];

    foreach ($parameterMappings[0] as $parameter)
    {
        if (is_array($parameter))
        {
            $sqlParameters[] = implode(',', $parameter);
        }
        else
        {
            $sqlParameters[] = $parameter;
        }
    }

    // Return the SQL with its parameters injected.
    return vsprintf($sql, $sqlParameters);
}

As its name implies, it executes the query in order to get the parameter mappings from the parser result, and then uses that along with vsprintf to replace the parameters with their values.

This is of course a hack of the core code, and as I'm not familiar with contributing to public projects, if anyone who does wants to try and get it included there, feel free to copy it.

Lal

Answer 5

I would use Profiler in SQL Server to get what is being sent to the database. Apparently there are some similar tools for mySQL. Is there a Profiler equivalent for MySql?

Wileywilfong

Answer 6

I based my answer on Néo's answer for symfony 5.4.

    $getSqlParams = \Closure::bind(function(){
        $this->getSql();
        return $this->processParameterMappings($this->parserResult->getParameterMappings());
    }, $qb->getQuery(), Query::class);

    [$paramValueList, $paramTypeList] = $getSqlParams();

    $sql = $qb->getQuery()->getSQL();

    $em->getConnection()->executeStatement($sql, $paramValueList, $paramTypeList);

Carotid