How to debug SQL queries in Laravel & Lumen. During development of an application, there may be times when we need to write large and complex queries. Laravel provides an awesome query builder and ORM (Eloquent) to do this task effortlessly. But sometimes we would like to see the actual SQL being generated.
In this tutorial, we are going to learn different ways to debug SQL queries in Laravel.
How to debug SQL queries in laravel & lumen
How to Debug SQL Queries in Laravel & Lumen
For better understanding, let’s take an eloquent query and then we try to generate the raw SQL query from that.
|
1 2 3 4 5 6 |
//Eloquent query $result = Booking::where(['property_id' => 2, 'room_type' => 1]) ->whereBetween('stay_date', array('2016-07-09', '2016-08-09')) ->whereNotIn('guest_status',[5,6]) ->orderBy('stay_date','asc') ->get(); |
To generate the raw SQL query, we can simply use Laravel toSql() method. Let’s try it and see the result.
|
1 2 3 4 5 6 7 |
$result = Booking::where(['property_id' => 2, 'room_type' => 1]) ->whereBetween('stay_date', array('2016-07-09', '2016-08-09')) ->whereNotIn('guest_status',[5,6]) ->orderBy('stay_date','asc') ->toSql(); dd($result); |
Output –
|
1 2 3 |
//Raw Sql Query "select * from `casa_room_night` where (`property_id` = ? and `room_type` = ?) and `stay_date` between ? and ? and `guest_status` not in (?, ?) order by `stay_date` asc" |
Laravel toSql() method is good for seeing the raw SQL query. But what about query bindings. How do we see the query bindings ? Let’s check the Laravel getBindings() method.
|
1 2 3 4 5 6 7 |
$result = Booking::where(['property_id' => 2, 'room_type' => 1]) ->whereBetween('stay_date', array('2016-07-09', '2016-08-09')) ->whereNotIn('guest_status',[5,6]) ->orderBy('stay_date','asc') ->getBindings(); dd($result); |
Output –
|
1 2 |
array(6) { [0]=> int(2) [1]=> int(1) [2]=> string(10) "2016-07-09" [3]=> string(10) "2016-08-09" [4]=> int(5) [5]=> int(3) } |
Enable and Print Query Log
In Laravel, query log is disabled by default we can enable it and print the query log.
|
1 2 3 4 5 6 7 8 9 |
DB::enableQueryLog(); //Query $result = Booking::where(['property_id' => 2, 'room_type' => 1]) ->whereBetween('stay_date', array('2016-07-09', '2016-08-09')) ->whereNotIn('guest_status',[5,6]) ->orderBy('stay_date','asc') ->get(); dd(DB::getQueryLog()); |
Output –
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
array(1) { [0]=> array(3) { ["query"]=> string(167) "select * from `casa_room_night` where (`property_id` = ? and `room_type` = ?) and `stay_date` between ? and ? and `guest_status` not in (?, ?) order by `stay_date` asc" ["bindings"]=> array(6) { [0]=> int(2) [1]=> int(1) [2]=> string(10) "2016-07-09" [3]=> string(10) "2016-08-09" [4]=> int(5) [5]=> int(3) } ["time"]=> float(2.71) } } |
Register Event Listener
Another option is to listen for query events on the DB object.
|
1 2 3 4 5 6 7 |
DB::listen( function ($sql, $bindings, $time) { var_dump($sql); var_dump($bindings); var_dump($time); } ); |
Using DB::listen we can print SQL query, parameter bindings and the time taken by the query to run.
