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!

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(),
));

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!

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à!

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.

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?

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);