Laravel 的 toSql() 方法是否屏蔽了 id？（列值被问号替换）

Does Laravel's toSql() method mask ids? (column value being replaced by question mark)

提问by Zach Smith

I'm trying to debug some SQL queries that I'm doing in a testing suite. Using the following debugging code:

我正在尝试调试我在测试套件中执行的一些 SQL 查询。使用以下调试代码：

\Log::debug(User::first()->jobs()->toSql());

The SQL that prints out is:

打印出来的 SQL 是：

`select * from `jobs` where `jobs`.`deleted_at` is null and `jobs`.`managed_by_id` = ? and `jobs`.`managed_by_id` is not null`

What is that question mark doing there? I've tested the query, and it works as expected. Is it because i'm selecting that first() user that this is happening?

那个问号在那里做什么？我已经测试了查询，它按预期工作。是因为我选择了 first() 用户才发生这种情况吗？

回答by Wader

Laravel uses Prepared Statements. They're a way of writing an SQL statement without dropping variables directly into the SQL string. The ?you see are placeholders or bindings for the information which will later be substituted and automatically sanitised by PDO. See the PHP docs for more information on prepared statements http://php.net/manual/en/pdo.prepared-statements.php

Laravel 使用准备好的语句。它们是一种无需将变量直接放入 SQL 字符串即可编写 SQL 语句的方法。在?你看到的是稍后将取代和自动PDO消毒的信息占位符或绑定。有关准备好的语句的更多信息，请参阅 PHP 文档http://php.net/manual/en/pdo.prepared-statements.php

To view the data that will be substituted into the query string you can call the getBindings()function on the query as below.

要查看将被替换到查询字符串中的数据，您可以调用getBindings()查询上的函数，如下所示。

$query = User::first()->jobs();

dd($query->toSql(), $query->getBindings());

The array of bindings get substituted in the same order the ?appear in the SQL statement.

绑定数组按照?出现在 SQL 语句中的相同顺序进行替换。

回答by Ijas Ameenudeen

In addition to @wader's answer, a 'macroable'way to get the raw SQL query with the bindings.

除了@wader的答案之外，还有一种“可宏”的方式来获取带有绑定的原始 SQL 查询。

1. Add below macro function in AppServiceProviderboot()method.

   \Illuminate\Database\Query\Builder::macro('toRawSql', function(){
       return array_reduce($this->getBindings(), function($sql, $binding){
           return preg_replace('/\?/', is_numeric($binding) ? $binding : "'".$binding."'" , $sql, 1);
       }, $this->toSql());
   });
   

2. Add an alias to the Eloquent Builder.

   \Illuminate\Database\Eloquent\Builder::macro('toRawSql', function(){
       return ($this->getQuery()->toRawSql());
   });
   

3. Then debug as usual.

   \Log::debug(User::first()->jobs()->toRawSql());
   

1. 在方法中添加以下宏函数。AppServiceProviderboot()

   \Illuminate\Database\Query\Builder::macro('toRawSql', function(){
       return array_reduce($this->getBindings(), function($sql, $binding){
           return preg_replace('/\?/', is_numeric($binding) ? $binding : "'".$binding."'" , $sql, 1);
       }, $this->toSql());
   });
   

2. 为 Eloquent Builder 添加别名。

   \Illuminate\Database\Eloquent\Builder::macro('toRawSql', function(){
       return ($this->getQuery()->toRawSql());
   });
   

3. 然后像往常一样调试。

   \Log::debug(User::first()->jobs()->toRawSql());
   

Note: from Laravel 5.1 to 5.3, Since Eloquent Builder doesn't make use of the Macroabletrait, cannot add toRawSqlan alias to the Eloquent Builder on the fly. Follow the below example to achieve the same.

注意：从 Laravel 5.1 到 5.3，由于 Eloquent Builder 没有使用Macroabletrait，所以不能即时给 Eloquent Builder添加toRawSql别名。按照以下示例实现相同的目的。

E.g. Eloquent Builder(Laravel 5.1 - 5.3)

例如Eloquent Builder( Laravel 5.1 - 5.3)

\Log::debug(User::first()->jobs()->getQuery()->toRawSql());

回答by rahim.nagori

Use the below code to print RAW SQL in Laravel:

使用以下代码在 Laravel 中打印RAW SQL：

echo "<pre>";
print_r($query->toSql());
print_r($query->getBindings());

Adding a PRE tag helps you read the result more accurately.

添加 PRE 标签可帮助您更准确地读取结果。

回答by phoenix

Just to reiterate @giovannipds great answer: currently i'm doing like this:

只是重申@giovannipds 很好的答案：目前我正在这样做：

vsprintf(str_replace(['?'], ['\'%s\''], $query->toSql()), $query->getBindings())