如何查看查询中的参数？

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

为了调试我的代码，我希望看到执行的显式sql查询。

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

我使用createQueryBuilder创建查询，我实现的最明确的事情是使用以下原始查询：

$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.

问题是，我看到持有人（？）而不是参数。我在网上找到了一些解决方案，但它们分别用于1.3和1.4，没有用于Symfony-2。

Ideas? Thanks!

想法？谢谢！

5 个解决方案

#1

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

您可以使用$ query-> getParameters（）访问占位符使用的参数，以便您可以使用以下命令调试查询：

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

#2

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

您可以使用以下方法轻松访问SQL参数。

   $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.

因此，如果打印出$ param_values和$ col_names，则可以获取通过sql和各个列名称的参数值。

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

注意：如果$ param返回一个数组，则需要重新迭代，因为IN（：？）中的参数通常是嵌套数组。

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

同时，如果你找到另一种方法，请善意与我们分享:)

Thank you!

谢谢！

#3

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.

我也一直在寻找一种方法来从DQL查询中获取参数注入的SQL，以帮助调试，允许我输出一个我可以直接粘贴到phpmyadmin的SQL字符串，并为其添加解释等。

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:

无论如何，我的答案基于Néo的答案，因为我无法通过私有方法调用使其工作，我通过在Doctrine \ ORM \ Query中创建一个函数进行调整，如下所示：

/**
 * 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.

顾名思义，它执行查询以从解析器结果中获取参数映射，然后将其与vsprintf一起使用以将值替换为参数。

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.

这当然是核心代码的破解，因为我不熟悉为公共项目做贡献，如果有人想尝试将其包含在那里，请随意复制它。

#4

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:

我必须使用QueryBuilder构建一个带有5个查询的requete union（不可能使用DQL或QueryBuilder）。所以我重用了这些查询但是使用getParameters（）函数时遇到了问题，因为它给出的参数顺序与你给出的顺序相同。使用查询构建器时的一个优点是，您可以按照yhou想要的顺序创建查询，但是当您检索参数时，您可能会在凌乱中检索它。为了避免这种情况，我建立了以下功能：

    $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 :

现在当你想要检索sql和你做的排序参数时：

$getSqlWithParams()->call($query)

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

不要忘记使用\ Doctrine \ ORM \ Query语句。瞧！

#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?

我会在SQL Server中使用Profiler来获取发送到数据库的内容。显然，mySQL有一些类似的工具。是否有MySql的Profiler等效文件？

#1