php 从 Laravel 查询生成器生成原始 MySQL 查询

Generate The Raw MySQL Query From Laravel Query Builder

提问by Saeed Vaziry

How can i get mysqlquery of a laravelquery

我如何获得mysql查询的laravel查询

Convert:

转变：

App\User::where('balance','>',0)->where(...)->get();

To:

到：

SELECT * FROM users WHERE `balance`>0 and ...

回答by RAUSHAN KUMAR

use toSql()method of laravel to get the query to be executed like

使用toSql()laravel的方法来获取要执行的查询，如

App\User::where('balance','>',0)->where(...)->toSql();

But Laravel will not show you parameters in your query, because they are bound after preparation of the query. To get the bind parameters, use this

但是 Laravel 不会在您的查询中显示参数，因为它们是在准备好查询之后绑定的。要获取绑定参数，请使用此

$query=App\User::where('balance','>',0)->where(...);
print_r($query->getBindings() );

enable the query log as DB::enableQueryLog()and then output to the screen the last queries ran you can use this,

启用查询日志DB::enableQueryLog()，然后将上次运行的查询输出到屏幕上，您可以使用它，

dd(DB::getQueryLog());

回答by honarkhah

you can add this function to your helpers

您可以将此功能添加到您的助手中

function getRealQuery($query, $dumpIt = false)
{
    $params = array_map(function ($item) {
        return "'{$item}'";
    }, $query->getBindings());
    $result = str_replace_array('\?', $params, $query->toSql());
    if ($dumpIt) {
        dd($result);
    }
    return $result;
}

---

---

and use like this:

并像这样使用：

getRealQuery(App\User::where('balance','>',0)->where(...),true)

回答by jetwaves

Here is a helper function who tells you the last SQL executed.

这是一个辅助函数，它告诉您最后执行的 SQL。

use DB;
public static function getLastSQL()
{
    $queries = DB::getQueryLog();
    $last_query = end($queries);
          // last_query is the SQL with with data binding like 
          //   { 
          //       select ? from sometable where field = ? and field2 = ? ;
          //       param1,
          //       param2，
          //       param3，
          //   }
          //   which is hard to read.
    $last_query = bindDataToQuery($last_query);     
          // here, last_query is the last SQL you have executed as normal SQL
          //     select param1 from sometable where field=param2 and field2 = param3;
    return $last_query
}

Here is the bindDataToQuery function, who fill the '?' blanks with real params.

这里是bindDataToQuery函数，谁填写'?' 带有真实参数的空白。

protected static function bindDataToQuery($queryItem){
    $query = $queryItem['query'];
    $bindings = $queryItem['bindings'];
    $arr = explode('?',$query);
    $res = '';
    foreach($arr as $idx => $ele){
        if($idx < count($arr) - 1){
            $res = $res.$ele."'".$bindings[$idx]."'";
        }
    }
    $res = $res.$arr[count($arr) -1];
    return $res;
}

回答by gaurav

Method 1

方法一

To print a single query, use toSql() method of laravel to get the query to be executed like

要打印单个查询，请使用 laravel 的 toSql() 方法来获取要执行的查询，例如

App\User::where('balance','>',0)->where(...)->toSql();

Method 2

方法二

Laravel can optionally log in memory all queries that have been run for the current request. But in some cases, such as when inserting a large number of rows, this can cause the application to use excess memory, so you should avoid this.

Laravel 可以选择将所有针对当前请求运行的查询记录到内存中。但是在某些情况下，例如插入大量行时，这会导致应用程序使用过多的内存，因此应避免这种情况。

To enable the log, you may use the enableQueryLogmethod as

要启用日志，您可以使用enableQueryLog方法作为

DB::connection()->enableQueryLog();

To get an array of the executed queries, you may use the getQueryLogmethod as

要获取已执行查询的数组，您可以使用getQueryLog方法作为

$queries = DB::getQueryLog();

you can get more details here Laravel Enable Query Log

您可以在此处获得更多详细信息Laravel 启用查询日志

Method 3

方法三

Another approach to display all queries used in Laravel without enabling the query log install the LaravelDebugBarfrom here Laravel Debug Bar. It is a package that allows you to quickly and easily keep tabs on your application during development.

另一种显示 Laravel 中使用的所有查询而不启用查询日志的方法从这里Laravel Debug Bar安装LaravelDebugBar。它是一个包，可让您在开发过程中快速轻松地密切关注您的应用程序。

回答by Nikhil Gyan

Try this:

尝试这个：

$results = App\User::where('balance','>',0)->where(...)->toSql();
dd($results);

Note: get() has been replaced with toSql() to display the raw SQL query.

注意：get() 已替换为 toSql() 以显示原始 SQL 查询。

回答by Nigel Ren

A simple way to display all queries used in Laravel without any code changes at all is to install the LaravelDebugBar (https://laravel-news.com/laravel-debugbar).

无需任何代码更改即可显示 Laravel 中使用的所有查询的一种简单方法是安装 LaravelDebugBar ( https://laravel-news.com/laravel-debugbar)。

As part of the functionality you get a tab which will show you all of the queries that a page has used.

作为功​​能的一部分，您将获得一个选项卡，该选项卡将显示页面已使用的所有查询。

回答by Roshan Perera

In Laravel 5.4 (I didn't check this in other versions), add this function into the "App"=>"Providers"=>"AppServiceProvider.php" .

在 Laravel 5.4 中（我没有在其他版本中检查），将此功能添加到 "App"=>"Providers"=>"AppServiceProvider.php" 中。

public function boot()
{

    if (App::isLocal()) {

        DB::listen(
            function ($sql) {
                // $sql is an object with the properties:
                //  sql: The query
                //  bindings: the sql query variables
                //  time: The execution time for the query
                //  connectionName: The name of the connection

                // To save the executed queries to file:
                // Process the sql and the bindings:
                foreach ($sql->bindings as $i => $binding) {
                    if ($binding instanceof \DateTime) {
                        $sql->bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
                    } else {
                        if (is_string($binding)) {
                            $sql->bindings[$i] = "'$binding'";
                        }
                    }
                }

                // Insert bindings into query
                $query = str_replace(array('%', '?'), array('%%', '%s'), $sql->sql);

                $query = vsprintf($query, $sql->bindings);

                // Save the query to file
                /*$logFile = fopen(
                    storage_path('logs' . DIRECTORY_SEPARATOR . date('Y-m-d') . '_query.log'),
                    'a+'
                );*/
                Log::notice("[USER] $query");
            }
        );
    }
}

After that install, https://github.com/ARCANEDEV/LogViewerand then you can see every executed SQL queries without editing the code.

安装后， https://github.com/ARCANEDEV/LogViewer，然后您无需编辑代码即可查看每个已执行的 SQL 查询。

回答by alepeino

Instead of interfering with the application with print statements or "dds", I do the following when I want to see the generated SQL:

dd当我想查看生成的 SQL 时，我不会用打印语句或“ s”来干扰应用程序，而是执行以下操作：

DB::listen(function ($query) { 
    Log::info($query->sql, $query->bindings);
});

// (DB and Log are the facades in Illuminate\Support\Facades namespace)

This will output the sql to the Laravel log (located at storage/logs/laravel.log). A useful command for following writes to this file is

这会将 sql 输出到 Laravel 日志（位于storage/logs/laravel.log）。跟踪写入此文件的有用命令是

tail -n0 -f storage/logs/laravel.log

回答by Mayank Pandeyz

To print the raw sql query, try:

要打印原始 sql 查询，请尝试：

DB::enableQueryLog();
// Your query here
$queries = DB::getQueryLog();
print_r($queries);

Reference

参考

回答by Rahul

To get mysql query in laravel you need to log your query as

要在 Laravel 中获取 mysql 查询，您需要将查询记录为

DB::enableQueryLog();
App\User::where('balance','>',0)->where(...)->get();
print_r(DB::getQueryLog());

Check reference : https://laravel.com/docs/5.0/database#query-logging

检查参考：https: //laravel.com/docs/5.0/database#query-logging