Laravel 如何使用绑定获取查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27314506/
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
Laravel how to get query with bindings?
提问by Mateusz Nowak
I have some query that I need to pass to another query using query builder
我有一些查询需要使用查询构建器传递给另一个查询
$query = DB::table('table')->whereIn('some_field', [1,2,30])->toSql();
Model::join(DB::raw("({$query}) as table"), function($join) {
$join->on('model.id', '=', 'table.id');
})
which should results with
这应该导致
Select * from model join (select * from table where some_field in (1,2,30)) as table on model.id = table.id
but the bindings are not passed, which force me to do
但绑定没有通过，这迫使我做
$query = DB::table('table')->whereRaw('some_field in ('. join(',', [1,2,30]) .')')->toSql();
what can be unsafe at times. How can I get the query with bindings?
有时可能是不安全的。如何使用绑定获取查询？
回答by Douglas.Sesar
Check out the getBindings()method on the Builderclass
查看类getBindings()上的方法Builder
$query = DB::table('table')->whereIn('some_field', [1,2,30]);
$sql = $query->toSql();
$bindings = $query->getBindings();
回答by Misha
public static function getQueries(Builder $builder)
{
$addSlashes = str_replace('?', "'?'", $builder->toSql());
return vsprintf(str_replace('?', '%s', $addSlashes), $builder->getBindings());
}
回答by Andrew Brown
Laravel now offers debugging directly on your Builder!!!
Laravel 现在可以直接在你的 Builder 上调试！！！
https://laravel.com/docs/queries#debugging
https://laravel.com/docs/queries#debugging
\App\User::where('age', '18')->dump();
\App\User::where('age', '18')->dd();
Outputs
输出
"select * from `users` where `age` = ?"
[
0 => "18"
]
回答by Urja Satodiya
You can define below code block as helper function and use wherever required. It will bind numeric as well as string value with quotations.
您可以将以下代码块定义为辅助函数并在需要时使用。它将用引号绑定数字和字符串值。
public static function getSqlWithBindings($query)
{
return vsprintf(str_replace('?', '%s', $query->toSql()), collect($query->getBindings())->map(function ($binding) {
return is_numeric($binding) ? $binding : "'{$binding}'";
})->toArray());
}
Example:
例子：
$query = Document::where('model', 'contact')->where('model_id', '1');
dd(Document::getSqlWithBindings($query));
Output:
输出：
"select * from `document` where `model` = 'contact' and `model_id` = 1"
回答by André
If you want to get an executed query including bindings from the query log:
如果要从查询日志中获取包含绑定的已执行查询：
\DB::enableQueryLog();
\DB::table('table')->get();
dd(str_replace_array('?', \DB::getQueryLog()[0]['bindings'],
\DB::getQueryLog()[0]['query']));
回答by Nickson Yap
The following function ensures the resulting SQL doesn't confuse bindings with columns by enclosing the ?to be '?'
下面的函数确保产生的SQL由包围所述不与列不要混淆绑定?是'?'
public static function getFinalSql($query)
{
$sql_str = $query->toSql();
$bindings = $query->getBindings();
$wrapped_str = str_replace('?', "'?'", $sql_str);
return str_replace_array('?', $bindings, $wrapped_str);
}
回答by Leif
Since the other answers do not properly quote the expressions, here is my approach. It uses the escaping function, that belongs to the current database connection.
由于其他答案没有正确引用表达式，这是我的方法。它使用属于当前数据库连接的转义函数。
It replaces the question marks one by one with the corresponding binding, which is retrieved from $bindings via array_shift(), consuming the array in the process. Note, that $bindings has to be passed by reference for this to work.
它将问号一一替换为对应的绑定，通过 array_shift() 从 $bindings 中检索，在过程中消耗数组。请注意，必须通过引用传递 $bindings 才能使其工作。
function getSql($query)
{
$bindings = $query->getBindings();
return preg_replace_callback('/\?/', function ($match) use (&$bindings, $query) {
return $query->getConnection()->getPdo()->quote(array_shift($bindings));
}, $query->toSql());
}
回答by mwafi
You can do something like this:
你可以这样做：
$escapedBindings = array();
foreach($query->getBindings() as $item) {$escapedBindings[] = '"'.$item.'"';}
$sql_with_bindings = Str::replaceArray('?', $escapedBindings, $query->toSql());
回答by guyaloni
I created this function. It is partial, might be parameters which are not covered, for me it was enough.
More than welcomed to add your improvements in a comment!
我创建了这个函数。它是部分的，可能是未涵盖的参数，对我来说已经足够了。
非常欢迎在评论中添加您的改进！
function getFullSql($query) {
$sqlStr = $query->toSql();
foreach ($query->getBindings() as $iter=>$binding) {
$type = gettype($binding);
switch ($type) {
case "integer":
case "double":
$bindingStr = "$binding";
break;
case "string":
$bindingStr = "'$binding'";
break;
case "object":
$class = get_class($binding);
switch ($class) {
case "DateTime":
$bindingStr = "'" . $binding->format('Y-m-d H:i:s') . "'";
break;
default:
throw new \Exception("Unexpected binding argument class ($class)");
}
break;
default:
throw new \Exception("Unexpected binding argument type ($type)");
}
$currentPos = strpos($sqlStr, '?');
if ($currentPos === false) {
throw new \Exception("Cannot find binding location in Sql String for bundung parameter $binding ($iter)");
}
$sqlStr = substr($sqlStr, 0, $currentPos) . $bindingStr . substr($sqlStr, $currentPos + 1);
}
$search = ["select", "distinct", "from", "where", "and", "order by", "asc", "desc", "inner join", "join"];
$replace = ["SELECT", "DISTINCT", "\n FROM", "\n WHERE", "\n AND", "\n ORDER BY", "ASC", "DESC", "\n INNER JOIN", "\n JOIN"];
$sqlStr = str_replace($search, $replace, $sqlStr);
return $sqlStr;
}
回答by user9893392
Output to the log all queries with inserted bindings sorted from the slowest query to the fastest:
将所有带有插入绑定的查询输出到日志中，从最慢的查询到最快的查询：
\DB::enableQueryLog();
// Put here your queries
$query = DB::table('table')->whereIn('some_field', [1,2,30]);
$query2 = DB::table('table2')->where('some_field', '=', 10);
$logQueries = \DB::getQueryLog();
usort($logQueries, function($a, $b) {
return $b['time'] <=> $a['time'];
});
foreach ($logQueries as $item) {
\Log::info(str_replace_array('?', $item['bindings'], $item['query']));
\Log::info($item['time']. ' ms');
}
