Laravel 5 Eloquent: How to get raw sql that is being executed? (with binded data)

Question

Im trying to figure out how to get the raw sql query being executed including the binded data in it. Here is what ive got:

\DB::connection()->enableQueryLog();
$query = \DB::getQueryLog();
$lastQuery = end($query);

And here is what the result looks like:

array(3) {
  ["query"]=>
  string(57) "select * from `table_1` where `field_1` = ? limit 1"
  ["bindings"]=>
  array(1) {
    [0]=>
    string(34) "xyz"
  }
}

So how do I get a dump of a full sql query like this (the good old fashioned way)?

select * from `table_1` where `field_1` = 'xyz' limit 1

Thanks

it is easy : http://stackoverflow.com/questions/27753868/how-to-get-the-query-executed-in-laravel-5-dbgetquerylog-returning-empty-arr — hasan movahed, Sep 01 '16 at 11:24

score 8 · Answer 1 · answered Dec 08 '17 at 23:07

You may want to check out the Laravel debugbar. I wouldn't develop a Laravel app without it. It will give you a breakdown of all queries (including repeated queries and ajax queries), with the speed they executed at and a note to the line in the controller/method that called them. (It provides TONS more info as well, such as views, gates, routes, etc.)

Also, Laravel has a toSql() method that you can use instead of your example. It will only show you the prepared statement as your example does, but it's at least a little cleaner. If you use toSql(), you have to apply it before you execute the query though.

$foo = Foo::where('bar', 'baz');
$foo_sql = $foo->toSql();
$foo->get();

score 5 · Answer 2 · answered Sep 21 '17 at 11:54

5

Add this in your routes Folder :

\Event::listen('Illuminate\Database\Events\QueryExecuted', function ($query) {
    Log::info( json_encode($query->sql) );
    Log::info( json_encode($query->bindings) );
    Log::info( json_encode($query->time)   );
});

Log::info() ==> will log the SQL query in your storage/logs/laravel.log file

var_dump() ==> will display in the API call output

answered Sep 21 '17 at 11:54

cosmoloc

2,884
5
29
48

Awesome. Saved me some quality time. – Murwa Jan 13 '19 at 05:46

score 3 · Answer 3 · answered Sep 21 '17 at 12:06

3

Try to add event listener for query :

Event::listen('illuminate.query', function($query)
{
    var_dump($query);
});

or

$results = User::where('id',$id)->toSql();
dd($results)

answered Sep 21 '17 at 12:06

Govind Samrow

9,981
13
53
90

score 3 · Answer 4 · answered Dec 19 '19 at 05:51

Add this function to your application and simply call.

function getQuery($sql){
        $query = str_replace(array('?'), array('\'%s\''), $sql->toSql());
        $query = vsprintf($query, $sql->getBindings());     
        return $query;
}
$foo = Foo::where('bar', 'baz');
print_r(getQuery($foo));

Output: select * from Foo where bar = 'baz'

score 0 · Answer 5 · answered Feb 19 '19 at 14:31

0

Another option is to get the queries from Laravel Debugbar:

$queries = debugbar()->getCollector('queries');  
$statements = $queries->collect()['statements'];
dd($statements);

answered Feb 19 '19 at 14:31

Dimo Doncheff

56
3

Laravel 5 Eloquent: How to get raw sql that is being executed? (with binded data)

5 Answers5

Linked