给定以下代码:
DB::table('users')->get();
我想得到上面的数据库查询生成器将生成的原始SQL查询字符串。在本例中,它将是SELECT*FROM用户。
我该怎么做?
给定以下代码:
DB::table('users')->get();
我想得到上面的数据库查询生成器将生成的原始SQL查询字符串。在本例中,它将是SELECT*FROM用户。
我该怎么做?
当前回答
$data = User::toSql();
echo $data; //this will retrun select * from users. //here User is model
其他回答
如果您试图使用Illuminate而不使用Laravel获取日志,请使用:
\Illuminate\Database\Capsule\Manager::getQueryLog();
你也可以像这样设置一个快速功能:
function logger()
{
$queries = \Illuminate\Database\Capsule\Manager::getQueryLog();
$formattedQueries = [];
foreach ($queries as $query) :
$prep = $query['query'];
foreach ($query['bindings'] as $binding) :
if (is_bool($binding)) {
$val = $binding === true ? 'TRUE' : 'FALSE';
} else if (is_numeric($binding)) {
$val = $binding;
} else {
$val = "'$binding'";
}
$prep = preg_replace("#\?#", $val, $prep, 1);
endforeach;
$formattedQueries[] = $prep;
endforeach;
return $formattedQueries;
}
EDIT
更新的版本似乎在默认情况下禁用了查询日志记录(上面返回了一个空数组)。要重新启用,在初始化Capsule Manager时,获取连接的实例并调用enableQueryLog方法
$capsule::connection()->enableQueryLog();
再次编辑
考虑到实际问题,您实际上可以执行以下操作来转换当前的单个查询,而不是之前的所有查询:
$sql = $query->toSql();
$bindings = $query->getBindings();
这是我可以向任何人建议的调试雄辩的最后一个查询或最终查询的最佳解决方案,尽管这也已经讨论过了:
// 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);
如果您使用的不是Laravel,而是Elquent包,那么:
use \Illuminate\Database\Capsule\Manager as Capsule;
use \Illuminate\Events\Dispatcher;
use \Illuminate\Container\Container;
$capsule = new Capsule;
$capsule->addConnection([
// connection details
]);
// Set the event dispatcher used by Eloquent models... (optional)
$capsule->setEventDispatcher(new Dispatcher(new Container));
// Make this Capsule instance available globally via static methods... (optional)
$capsule->setAsGlobal();
// Setup the Eloquent ORM...(optional unless you've used setEventDispatcher())
$capsule->bootEloquent();
// Listen for Query Events for Debug
$events = new Dispatcher;
$events->listen('illuminate.query', function($query, $bindings, $time, $name)
{
// Format binding data for sql insertion
foreach ($bindings as $i => $binding) {
if ($binding instanceof \DateTime) {
$bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
} else if (is_string($binding)) {
$bindings[$i] = "'$binding'";`enter code here`
}
}
// Insert bindings into query
$query = str_replace(array('%', '?'), array('%%', '%s'), $query);
$query = vsprintf($query, $bindings);
// Debug SQL queries
echo 'SQL: [' . $query . ']';
});
$capsule->setEventDispatcher($events);
我通过监听查询日志并附加到日志数组来实现:
//create query
$query=DB::table(...)...->where(...)...->orderBy(...)...
$log=[];//array of log lines
...
//invoked on query execution if query log is enabled
DB::listen(function ($query)use(&$log){
$log[]=$query;//enqueue query data to logs
});
//enable query log
DB::enableQueryLog();
$res=$query->get();//execute
用于获取带有绑定的SQL查询的“可宏”替换。
在AppServiceProvider boot()方法中添加以下宏函数。\Illuminate\Database\Query\Builder::宏('toRawSql',function(){return array_reduce($this->getBindings(),函数($sql,$binding){return preg_replace('/\?/',is_numeric($binding)$绑定:“”$结合“'”,$sql,1);},$this->to SQL());});为Elquent Builder添加别名。(Laravel 5.4+)\Illuminate\Database\Elquent\Builder::宏('toRawSql',函数(){return($this->getQuery()->toRawSql());});然后照常调试。(Laravel 5.4+)例如,查询生成器\日志::debug(\DB::table('users')->limit(1)->toRawSql())例如,Elquent Builder\日志::debug(\App\User::limit(1)->toRawSql());
注意:从Laravel 5.1到5.3,由于Elquent Builder不使用Macroable特性,因此不能在RawSql中添加Elquent生成器的别名。遵循以下示例以实现相同的效果。
例如,Elquent Builder(Laravel 5.1-5.3)
\Log::debug(\App\User::limit(1)->getQuery()->toRawSql());