给定以下代码:

DB::table('users')->get();

我想得到上面的数据库查询生成器将生成的原始SQL查询字符串。在本例中,它将是SELECT*FROM用户。

我该怎么做?


当前回答

Use:

$data = DB::select('select * from users where id = :id', ['id' => 1]);
print_r($data);

输出如下:

Array ( [0] => stdClass Object ( [id] => 1 [name] => parisa [last] => naderi [username] => png [password] => 2132 [role] => 0 ) )

其他回答

我创建了一些简单的函数来从一些查询中获取SQL和绑定。

/**
 * getSql
 *
 * Usage:
 * getSql( DB::table("users") )
 * 
 * Get the current SQL and bindings
 * 
 * @param  mixed  $query  Relation / Eloquent Builder / Query Builder
 * @return array          Array with sql and bindings or else false
 */
function getSql($query)
{
    if( $query instanceof Illuminate\Database\Eloquent\Relations\Relation )
    {
        $query = $query->getBaseQuery();
    }

    if( $query instanceof Illuminate\Database\Eloquent\Builder )
    {
        $query = $query->getQuery();
    }

    if( $query instanceof Illuminate\Database\Query\Builder )
    {
        return [ 'query' => $query->toSql(), 'bindings' => $query->getBindings() ];
    }

    return false;
}

/**
 * logQuery
 *
 * Get the SQL from a query in a closure
 *
 * Usage:
 * logQueries(function() {
 *     return User::first()->applications;
 * });
 * 
 * @param  closure $callback              function to call some queries in
 * @return Illuminate\Support\Collection  Collection of queries
 */
function logQueries(closure $callback) 
{
    // check if query logging is enabled
    $logging = DB::logging();

    // Get number of queries
    $numberOfQueries = count(DB::getQueryLog());

    // if logging not enabled, temporarily enable it
    if( !$logging ) DB::enableQueryLog();

    $query = $callback();

    $lastQuery = getSql($query);

    // Get querylog
    $queries = new Illuminate\Support\Collection( DB::getQueryLog() );

    // calculate the number of queries done in callback
    $queryCount = $queries->count() - $numberOfQueries;

    // Get last queries
    $lastQueries = $queries->take(-$queryCount);

    // disable query logging
    if( !$logging ) DB::disableQueryLog();

    // if callback returns a builder object, return the sql and bindings of it
    if( $lastQuery )
    {
        $lastQueries->push($lastQuery);
    }

    return $lastQueries;
}

用法:

getSql( DB::table('users') );
// returns 
// [
//     "sql" => "select * from `users`",
//     "bindings" => [],
// ]

getSql( $project->rooms() );
// returns
// [
//     "sql" => "select * from `rooms` where `rooms`.`project_id` = ? and `rooms`.`project_id` is not null",
//     "bindings" => [ 7 ],
// ]

从laravel 5.2开始。您可以使用DB::listen获取执行的查询。

DB::listen(function ($query) {
    // $query->sql
    // $query->bindings
    // $query->time
});

或者,如果要调试单个Builder实例,则可以使用toSql方法。

DB::table('posts')->toSql(); 

下面是一个完美的例子:

https://laravel.com/docs/5.8/database#listening-用于查询事件

打开app\Providers\AppServiceProvider.php并将以下内容添加到Boot()函数中:

DB::listen(function ($query) {
    var_dump([
        $query->sql,
        $query->bindings,
        $query->time
    ]);
});

因此,您不需要在每个函数中放置DB::enableQuerylog()和DB::getQuerylog(()。

虽然我很喜欢这个框架,但我讨厌它像垃圾一样运行。

DB::enableQueryLog()完全无用。DB::听同样没用。当我说$query->count()时,它显示了部分查询,但如果我执行$query->get(),它就没有什么好说的了。

唯一一个似乎能持续工作的解决方案是,故意在ORM参数中添加一些语法或其他错误,例如不存在的列/表名,在调试模式下在命令行上运行代码,最后将通过完整的“frickin”查询吐出SQL错误。否则,如果从web服务器运行,则希望错误出现在日志文件中。

为了记录所有执行的查询,可以使用DB::enableQueryLog()icw DB::getQueryLog(()。输出具有以下结构。

[
  [
    "query" => "select * from "users" where name = ?"
    "bindings" => ["John Doe"]
    "time" => 0.34
  ],
  ...
]

此外,我在这里结合了一些答案,以便获得完美的函数,用编译的绑定解析sql。见下文。我甚至创建了一个实现此功能的自定义生成器类,例如User::where('name','JohnDoe')->parse();

function parse_sql(string $sql, array $bindings) : string
{
  $compiled_bindings  = array_map('compile_binding', $bindings);

  return preg_replace_array("/\?/", $compiled_bindings, $sql);
}

function compile_binding($binding)
{
  $grammar = new MySqlGrammar;

  if (is_bool($binding))
  {
    return (int)$binding; //This line depends on the database implementation
  }

  if(is_string($binding))
  {
    return "'$binding'";
  }

  if ($binding instanceof DateTimeInterface)
  {
    return $binding->format($grammar->getDateFormat());
  }

  return $binding;
}