Generate The Raw MySQL Query From Laravel Query Builder

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)

To print the raw sql query, try:

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

Reference

Method 1

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

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.

To enable the log, you may use the enableQueryLog method as

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

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

$queries = DB::getQueryLog();

you can get more details here Laravel Enable Query Log

Method 3

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

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

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.

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;
}

It is so strange that the laravel haven't support any way to get the raw sql easily, it is now version 6 after all...

Here's a workaround I used by myself to quickly get the raw sql with parameters without installing any extension...

Just deliberately make your original sql WRONG

Like change

DB::table('user')

to

DB::table('user1')

where the table "user1" does not exist at all!

Then run it again.

Sure there will be an exception reported by laravel.

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'user1' doesn't exist (SQL: ...)

And now you can see the raw sql with parameters is right after the string "(SQL:"

Change back from the wrong table name to the right one and there you go!

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

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

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

In Laravel 5.4 (I didn't check this in other versions), add this function into the "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/LogViewer and then you can see every executed SQL queries without editing the code.

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

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

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

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).

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

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.

A very simple and shortcut way is below Write the name of column wrong like write 'balancedd' in spite of 'balance' and the query will be displayed on error screen when you execute code with all the parameters and error that column not found.

DB::enableQueryLog(); (Query) $d= DB::getQueryLog(); print"<pre>"; print_r ($d); print"</pre>";

you will get the mysql query that is just run.