php 如何让查询生成器将其原始 SQL 查询输出为字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18236294/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How Do I Get the Query Builder to Output Its Raw SQL Query as a String?
提问by meiryo
Given the following code:
鉴于以下代码：
DB::table('users')->get();
I want to get the raw SQL query string that the database query builder above will generate. In this example, it would be SELECT * FROM users.
我想获取上面的数据库查询生成器将生成的原始 SQL 查询字符串。在这个例子中，它将是SELECT * FROM users.
How do I do this?
我该怎么做呢？
回答by Steven Mercatante
Use the toSql()method on a QueryBuilderinstance.
toSql()在QueryBuilder实例上使用该方法。
DB::table('users')->toSql()would return:
DB::table('users')->toSql()会返回：
select * from `users`
从`用户`中选择*
This is easier than wiring up an event listener, and also lets you check what the query will actually look like at any point while you're building it.
这比连接事件侦听器更容易，并且还可以让您在构建查询时随时检查查询的实际情况。
回答by jfortunato
To output to the screen the last queries ran you can use this:
要将上次运行的查询输出到屏幕，您可以使用以下命令：
DB::enableQueryLog(); // Enable query log
// Your Eloquent query executed by using get()
dd(DB::getQueryLog()); // Show results of log
I believe the most recent queries will be at the bottom of the array.
我相信最近的查询将位于数组的底部。
You will have something like that:
你会有这样的事情：
array(1) {
[0]=>
array(3) {
["query"]=>
string(21) "select * from "users""
["bindings"]=>
array(0) {
}
["time"]=>
string(4) "0.92"
}
}
(Thanks to Joshua'scomment below.)
回答by Kakashi
DB::QueryLog()only work after you execute the query $builder->get(). if you want to get the query before execute the query you can use $builder->toSql()method. this is the example how to get the sql and bind it:
DB::QueryLog()仅在您执行查询后工作$builder->get()。如果您想在执行查询之前获取查询，您可以使用$builder->toSql()方法。这是如何获取 sql 并绑定它的示例：
$query = str_replace(array('?'), array('\'%s\''), $builder->toSql());
$query = vsprintf($query, $builder->getBindings());
dump($query);
$result = $builder->get();
OR just make your query error like calling for unexist table or column, u will see the generated query in exception XD
或者只是让你的查询错误，比如调用不存在的表或列，你会在异常 XD 中看到生成的查询
回答by Rubens Mariuzzo
You can listen to the 'illuminate.query' event. Before the query add the following event listener:
您可以收听 'illuminate.query' 事件。在查询之前添加以下事件侦听器：
Event::listen('illuminate.query', function($query, $params, $time, $conn)
{
dd(array($query, $params, $time, $conn));
});
DB::table('users')->get();
This will print out something like:
这将打印出如下内容：
array(4) {
[0]=>
string(21) "select * from "users""
[1]=>
array(0) {
}
[2]=>
string(4) "0.94"
[3]=>
string(6) "sqlite"
}
回答by Luke Snowden
If you are trying to get the Log using Illuminate without Laravel use:
如果您尝试在没有 Laravel 的情况下使用 Illuminate 获取日志，请使用：
\Illuminate\Database\Capsule\Manager::getQueryLog();
You could also nock up a quick function like so:
你也可以像这样启动一个快速功能：
function logger() {
$queries = \Illuminate\Database\Capsule\Manager::getQueryLog();
$formattedQueries = [];
foreach( $queries as $query ) :
$prep = $query['query'];
foreach( $query['bindings'] as $binding ) :
$prep = preg_replace("#\?#", is_numeric($binding) ? $binding : "'" . $binding . "'", $prep, 1);
endforeach;
$formattedQueries[] = $prep;
endforeach;
return $formattedQueries;
}
EDIT
编辑
updated versions seem to have query logging disabled by default (the above returns an empty array). To turn back on, when initialising the Capsule Manager, grab an instance of the connection and call the enableQueryLogmethod
更新版本似乎默认禁用查询日志记录（上面返回一个空数组）。要重新打开，在初始化 Capsule Manager 时，获取连接的实例并调用该enableQueryLog方法
$capsule::connection()->enableQueryLog();
EDIT AGAIN
再次编辑
Taking the actual question into consideration, you could actually do the following to convert the current single query instead of all previous queries:
考虑到实际问题，您实际上可以执行以下操作来转换当前的单个查询而不是所有以前的查询：
$sql = $query->toSql();
$bindings = $query->getBindings();
回答by CelinVeronicca
There is a method in eloquent for getting query string.
eloquent 有一种获取查询字符串的方法。
toSql()
toSql()
in our case,
在我们的例子中，
DB::table('users')->toSql();
return
返回
select * from users
is the exact solution that return the SQL query string..Hope this helpful...
是返回 SQL 查询字符串的确切解决方案..希望这有帮助......
回答by Kuldeep Mishra
$data = User::toSql();
echo $data; //this will retrun select * from users. //here User is model
回答by Yevgeniy Afanasyev
If you use laravel 5.1 and MySQL you can use this function made by me:
如果你使用 laravel 5.1 和 MySQL 你可以使用我做的这个函数：
/*
* returns SQL with values in it
*/
function getSql($model)
{
$replace = function ($sql, $bindings)
{
$needle = '?';
foreach ($bindings as $replace){
$pos = strpos($sql, $needle);
if ($pos !== false) {
if (gettype($replace) === "string") {
$replace = ' "'.addslashes($replace).'" ';
}
$sql = substr_replace($sql, $replace, $pos, strlen($needle));
}
}
return $sql;
};
$sql = $replace($model->toSql(), $model->getBindings());
return $sql;
}
As an input parameter you can use either of these
作为输入参数，您可以使用其中任何一个
Illuminate\Database\Eloquent\Builder
Illuminate\Database\Eloquent\Relations\HasMany
Illuminate\Database\Query\Builder
Illuminate\Database\Eloquent\Builder
Illuminate\Database\Eloquent\Relations\HasMany
照亮\数据库\查询\构建器
回答by Ravi Mane
First You will need to enable the query log by calling:
首先，您需要通过调用启用查询日志：
DB::enableQueryLog();
after queries using the DB facade you can write:
在使用 DB 门面查询后，您可以编写：
dd(DB::getQueryLog());
the output will like below:
输出将如下所示：
array:1 [▼
0 => array:3 [▼
"query" => "select * from `users` left join `website_user` on `users`.`id` = `website_user`.`user_id` left join `region_user` on `users`.`id` = `region_user`.`user_id` left ?"
"bindings" => array:5 [?]
"time" => 3.79
]
]
回答by justnajm
This is the far best solution I can suggest to any one for debug-ing eloquent last query or final query although this has been discussed as well:
这是我可以向任何人推荐的最好的解决方案，用于调试雄辩的最后一个查询或最终查询，尽管这也已经讨论过：
// query builder
$query = DB::table('table_name')->where('id', 1);
// binding replaced
$sql = str_replace_array('?', $query->getBindings(), $query->toSql());
// for laravel 5.8^
$sql = Str::replaceArray('?', $query->getBindings(), $query->toSql());
// print
dd($sql);
