How Do I Get the Query Builder to Output Its Raw SQL Query as a String?

Loading...

How Do I Get the Query Builder to Output Its Raw SQL Query as a String?

Given the following code:
DB::table('users')->get();

I want to get the raw SQL query string that the database query builder above will generate. In this example, it would be SELECT * FROM users.
How do I do this?

Solutions/Answers:

Answer 1:

To output to the screen the last queries ran you can use this:

DB::enableQueryLog(); // Enable query log

// Your Eloquent query

dd(DB::getQueryLog()); // Show results of log

I believe the most recent queries will be at the bottom of the array.

You will have something like that:

array(1) {
  [0]=>
  array(3) {
    ["query"]=>
    string(21) "select * from "users""
    ["bindings"]=>
    array(0) {
    }
    ["time"]=>
    string(4) "0.92"
  }
}

(Thanks to Joshua’s comment below.)

Answer 2:

Use the toSql() method on a QueryBuilder instance.

DB::table('users')->toSql() would return:

select * from `users`

This is easier than wiring up an event listener, and also lets you check what the query will actually look like at any point while you’re building it.

Answer 3:

DB::QueryLog() only work after you execute the query $builder->get(). if you want to get the query before execute the query you can use $builder->toSql() method. this is the example how to get the sql and bind it:

    $query = str_replace(array('?'), array('\'%s\''), $builder->toSql());
    $query = vsprintf($query, $builder->getBindings());
    dump($query);

    $result = $builder->get();

Answer 4:

You can listen to the ‘illuminate.query’ event. Before the query add the following event listener:

Event::listen('illuminate.query', function($query, $params, $time, $conn) 
{ 
    dd(array($query, $params, $time, $conn));
});

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

This will print out something like:

array(4) {
  [0]=>
  string(21) "select * from "users""
  [1]=>
  array(0) {
  }
  [2]=>
  string(4) "0.94"
  [3]=>
  string(6) "sqlite"
}

Answer 5:

If you are trying to get the Log using Illuminate without Laravel use:

\Illuminate\Database\Capsule\Manager::getQueryLog();

You could also nock up a quick function like so:

function logger() {
    $queries = \Illuminate\Database\Capsule\Manager::getQueryLog();
    $formattedQueries = [];
    foreach( $queries as $query ) :
        $prep = $query['query'];
        foreach( $query['bindings'] as $binding ) :
            $prep = preg_replace("#\?#", is_numeric($binding) ? $binding : "'" . $binding . "'", $prep, 1);
        endforeach;
        $formattedQueries[] = $prep;
    endforeach;
    return $formattedQueries;
}

EDIT

updated versions seem to have query logging disabled by default (the above returns an empty array). To turn back on, when initialising the Capsule Manager, grab an instance of the connection and call the enableQueryLog method

$capsule::connection()->enableQueryLog();

EDIT AGAIN

Taking the actual question into consideration, you could actually do the following to convert the current single query instead of all previous queries:

$sql = $query->toSql();
$bindings = $query->getBindings();

Answer 6:

There is a method in eloquent for getting query string.

toSql()

in our case,

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

return

select * from users

is the exact solution that return the SQL query string..Hope this helpful…

Answer 7:

$data = User::toSql();
echo $data; //this will retrun select * from users. //here User is model

Answer 8:

If you use laravel 5.1 and MySQL you can use this function made by me:

/*
 *  returns SQL with values in it
 */
function getSql($model)
{
    $replace = function ($sql, $bindings)
    {
        $needle = '?';
        foreach ($bindings as $replace){
            $pos = strpos($sql, $needle);
            if ($pos !== false) {
                if (gettype($replace) === "string") {
                     $replace = ' "'.addslashes($replace).'" ';
                }
                $sql = substr_replace($sql, $replace, $pos, strlen($needle));
            }
        }
        return $sql;
    };
    $sql = $replace($model->toSql(), $model->getBindings());

    return $sql;
}

As an input parameter you can use either of these

Illuminate\Database\Eloquent\Builder

Illuminate\Database\Eloquent\Relations\HasMany

Illuminate\Database\Query\Builder

Answer 9:

First way:

Simply you can do following stuff using toSql() method,

$query = DB::table('users')->get();

echo $query->toSql();

If it’s not working you can set-up the thing from laravel documentation.

Second way:

Another way to do it is

DB::getQueryLog()

but if it’s returns an empty array then by default it’s disabled visit this,

just enable with DB::enableQueryLog() and it will work 🙂

for more info visit Github Issue to know more about it.

Hope it helps 🙂

Answer 10:

A ‘macroable’ replacement to get the SQL query with the bindings.

  1. Add below macro function in AppServiceProvider boot() method.

    \Illuminate\Database\Query\Builder::macro('toRawSql', function(){
        return array_reduce($this->getBindings(), function($sql, $binding){
            return preg_replace('/\?/', is_numeric($binding) ? $binding : "'".$binding."'" , $sql, 1);
        }, $this->toSql());
    });
    
  2. Add an alias for the Eloquent Builder. (Laravel 5.4+)

    \Illuminate\Database\Eloquent\Builder::macro('toRawSql', function(){
        return ($this->getQuery()->toRawSql());
    });
    
  3. Then debug as usual. (Laravel 5.4+)

    E.g. Query Builder

    \Log::debug(\DB::table('users')->limit(1)->toRawSql())
    

    E.g. Eloquent Builder

    \Log::debug(\App\User::limit(1)->toRawSql());
    

Note: from Laravel 5.1 to 5.3, Since Eloquent Builder doesn’t make use of the Macroable trait, cannot add toRawSql an alias to the Eloquent Builder on the fly. Follow the below example to achieve the same.

E.g. Eloquent Builder (Laravel 5.1 – 5.3)

\Log::debug(\App\User::limit(1)->getQuery()->toRawSql());

Answer 11:

First You will need to enable the query log by calling:

DB::enableQueryLog();

after queries using the DB facade you can write:

dd(DB::getQueryLog());

the output will like below:

array:1 [▼
  0 => array:3 [▼
    "query" => "select * from `users` left join `website_user` on `users`.`id` = `website_user`.`user_id` left join `region_user` on `users`.`id` = `region_user`.`user_id` left ▶"
    "bindings" => array:5 [▶]
    "time" => 3.79
  ]
]

Answer 12:

use debugbar package

composer require "barryvdh/laravel-debugbar": "2.3.*"

enter image description here

Answer 13:

From laravel 5.2 and onward. you can use DB::listen to get executed queries.

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

Or if you want to debug a single Builder instance then you can use toSql method.

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

Answer 14:

The most easiest way is to make deliberate mistake. For example, I want to see the full SQL query of the following relation:

 public function jobs()
        {
            return $this->belongsToMany(Job::class, 'eqtype_jobs')
                   ->withPivot(['created_at','updated_at','id'])
                   ->orderBy('pivot_created_at','desc');
        }

I just to make a column to be not found, here I choose created_at and I changed it to created_ats by adding trailing s to be:

public function jobs()
            {
                return $this->belongsToMany(Job::class, 'eqtype_jobs')
                       ->withPivot(['created_ats','updated_at','id'])
                       ->orderBy('pivot_created_at','desc');
            }

So, the debuger will return the following error:

(4/4) ErrorException SQLSTATE[42S22]: Column not found: 1054 Unknown
column ‘eqtype_jobs.created_ats’ in ‘field list’ (SQL: select
jobs.*, eqtype_jobs.set_id as pivot_set_id,
eqtype_jobs.job_id as pivot_job_id, eqtype_jobs.created_ats
as pivot_created_ats, eqtype_jobs.updated_at as
pivot_updated_at, eqtype_jobs.id as pivot_id from jobs inner
join eqtype_jobs on jobs.id = eqtype_jobs.job_id where
eqtype_jobs.set_id = 56 order by pivot_created_at desc limit 20
offset 0) (View:
/home/said/www/factory/resources/views/set/show.blade.php)

The above error message returns the full SQL query with the mistake

SQL: select  jobs.*, eqtype_jobs.set_id as pivot_set_id,  eqtype_jobs.job_id as pivot_job_id, eqtype_jobs.created_ats as pivot_created_ats, eqtype_jobs.updated_at as  pivot_updated_at, eqtype_jobs.id as pivot_id from jobs inner join eqtype_jobs on jobs.id = eqtype_jobs.job_id where  eqtype_jobs.set_id = 56 order by pivot_created_at desc limit 20 offset 0

Now, just remove the extra s from created_at and test this SQL as you like in any SQL editor such as phpMyAdmin SQL editor!

Notice:

The solution has been tested with Laravel 5.4.

References

Loading...