Get the Query Executed in Laravel 3/4

Question

How can I retrieve the raw executed SQL query in Laravel 3/4 using Laravel Query Builder or Eloquent ORM?

For example, something like this:

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

Or:

(posts (id, user_id, ...))

User::find(1)->posts->get();

Otherwise, at the very least how can I save all queries executed to laravel.log?

@Adamnick here: http://stackoverflow.com/questions/27753868/how-to-get-the-query-executed-in-laravel-5-dbgetquerylog-returning-empty-arr — Patrick Maciel, Aug 26 '15 at 16:58

rmobis · Accepted Answer · 2021-07-10T19:38:01.003

320

Laravel 4+

Note for Laravel 5 users: You'll need to call DB::enableQueryLog() before executing the query. Either just above the line that runs the query or inside a middleware.

In Laravel 4 and later, you have to call DB::getQueryLog() to get all ran queries.

$queries = DB::getQueryLog();
$last_query = end($queries);

Or you can download a profiler package. I'd recommend barryvdh/laravel-debugbar, which is pretty neat. You can read for instructions on how to install in their repository.

Laravel 3

In Laravel 3, you can get the last executed query from an Eloquent model calling the static method last_query on the DB class.

DB::last_query();

This, however, requires that you enable the profiler option in application/config/database.php. Alternatively you could, as @dualed mentioned, enable the profiler option, in application/config/application.php or call DB::profile() to get all queries ran in the current request and their execution time.

edited Jul 10 '21 at 19:38

answered Jan 26 '13 at 10:50

rmobis

26,129
8
64
65

2

Your code for Laravel 4 doesn't work. I get this `ErrorException`: Warning: `call_user_func_array()` expects parameter 1 to be a valid callback, class `Illuminate\Database\MySqlConnection` does not have a method `getQueryList`. – duality_ Mar 05 '13 at 12:00
My bad, the correct method is `getQueryLog`. Fixed it now. Thanks! – rmobis Mar 05 '13 at 17:32
Strange... I get last_query() is not defined on the Query object error. I am just calling on an uninstantiated Eloquent model. – Aditya M P Apr 15 '13 at 09:47
1

For Laravel 3 it's actually DB::last_query(); You also need to set 'profile' to true in your application/config/database.php – Dan Smart Apr 16 '13 at 08:22
4

This doesn't seem to work for an Eloquent Model on L4. When I execute Model::find($id) and perform DB::getQueryLog() return blank array(). Any idea how to get the queries for an Eloquent Model? – Abishek May 12 '13 at 21:25
dd(end(DB::getQueryLog())); – MECU Apr 30 '14 at 01:23
1

L4 oneliner: `$q=\DB::getQueryLog();dd(end($q));` – Kamil Kiełczewski Jul 19 '16 at 19:46
@KamilKiełczewski isn't that just a longer version of MECU's answer? I really don't see the point. – rmobis Jul 19 '16 at 23:27
@rmobis MECU answer dont work in my L4 project - this is why i put my version. May be MECU answer work on L3 or L5 (?) – Kamil Kiełczewski Jul 20 '16 at 09:06
@KamilKiełczewski he just forgot the `\ ` before `DB::getQueryLog()`. Apart from that, the two are identical. – rmobis Jul 20 '16 at 13:52
@rmobis - no i have different error - try yourself. there was a problem with end(here_must_be_veraiable_not_function) – Kamil Kiełczewski Jul 20 '16 at 14:38
@KamilKiełczewski Oh, you're right. It's because `end`'s argument is passed by reference so it has to be an actual variable. Good catch. – rmobis Jul 20 '16 at 23:21
@rmobis ok so please give point to my comment with oneliner to be better visible to other people :) – Kamil Kiełczewski Jul 21 '16 at 06:35
in Laravel 3 long queries are truncated, any idea? – qwertzman Mar 02 '18 at 13:24
1

I am getting to be a regular visitor of this page. – Jovylle Jul 09 '21 at 06:21

dualed · Answer 2 · 2013-01-26T11:33:43.450

31

You can enable the "Profiler" in Laravel 3 by setting

'profiler' => true,

In your application/config/application.php and application/config/database.php

This enables a bar at the bottom of each page. One of its features is listing the executed queries and how long each one took.

enter image description here

edited Jan 26 '13 at 11:33

answered Jan 26 '13 at 10:45

dualed

10,262
1
26
29

14

Note that in Laravel 4, the Profiler is not included, you have to install it by yourself (e.g. using composer). See [this SO question](http://stackoverflow.com/questions/14536165/get-the-query-executed-in-laravel-3-4). – duality_ Mar 05 '13 at 11:56
1

It's discussed in the [first answer there](http://stackoverflow.com/a/14536215/291557). – duality_ Feb 24 '14 at 06:44

JamesPlayer · Answer 3 · 2014-07-25T05:02:55.553

26

For Eloquent you can just do:

$result->getQuery()->toSql();

But you need to remove the "->get()" part from your query.

edited Jul 25 '14 at 05:02

answered Jul 25 '14 at 04:32

JamesPlayer

1,834
2
15
21

score 19 · Answer 4 · answered Jul 29 '14 at 09:17

I would recommend using the Chrome extension Clockwork with the Laravel package https://github.com/itsgoingd/clockwork. It's easy to install and use.

Clockwork is a Chrome extension for PHP development, extending Developer Tools with a new panel providing all kinds of information useful for debugging and profiling your PHP scripts, including information on request, headers, GET and POST data, cookies, session data, database queries, routes, visualisation of application runtime and more. Clockwork includes out of the box support for Laravel 4 and Slim 2 based applications, you can add support for any other or custom framework via an extensible API.

enter image description here

score 16 · Answer 5 · edited Feb 13 '16 at 05:35

Since the profiler is not yet out in Laravel 4, I've created this helper function to see the SQL being generated:


    public static function q($all = true) 
    {
        $queries = DB::getQueryLog();

        if($all == false) {
            $last_query = end($queries);
            return $last_query;
        }

        return $queries;
    }

NOTE: Set the $all flag to false if you only want the last SQL query.

I keep this sort of functions in a class called DBH.php (short for Database Helper) so I can call it from anywhere like this:

dd(DBH::q());

Here is the output I get: enter image description here

In case you are wondering, I use Kint for the dd() formatting. http://raveren.github.io/kint/

`if($all == false)`? Why not simply `if(!$all)` – toesslab Feb 22 '16 at 08:34 — toesslab, Feb 22 '16 at 08:34

score 14 · Answer 6 · answered Mar 18 '13 at 16:18

14

For Laraver 4 it's

DB::getQueryLog()

answered Mar 18 '13 at 16:18

misaizdaleka

1,776
3
21
32

score 14 · Answer 7 · edited Sep 30 '13 at 12:26

14

Here is a quick Javascript snippet you can throw onto your master page template. As long as it's included, all queries will be output to your browser's Javascript Console. It prints them in an easily readable list, making it simple to browse around your site and see what queries are executing on each page.

When you're done debugging, just remove it from your template.

<script type="text/javascript">
    var queries = {{ json_encode(DB::getQueryLog()) }};
    console.log('/****************************** Database Queries ******************************/');
    console.log(' ');
    queries.forEach(function(query) {
        console.log('   ' + query.time + ' | ' + query.query + ' | ' + query.bindings[0]);
    });
    console.log(' ');
    console.log('/****************************** End Queries ***********************************/');
</script>

edited Sep 30 '13 at 12:26

rmobis

26,129
8
64
65

answered Sep 30 '13 at 02:09

Adam

211
3
2

I think you would need "" around the {{ json_encode... }} part – mydoglixu Jun 01 '15 at 16:39
@mydoglixu Since `DB::getQueryLog()` returns an array, there's no need to surround it with "". `json_encode` will translate it accordingly. – rmobis Jun 30 '15 at 07:43
@mobis - I meant that you need the "" outside the {{ ... }} so that javascript doesn't throw an error. like this: var queries = "json output"; – mydoglixu Jun 30 '15 at 11:41
@mydoglixu You don't, because a JSON array (or object) is valid JavaScript. It would break if you did. – rmobis Jul 01 '15 at 16:17
@mobis - oh yeah, duh – mydoglixu Jul 07 '15 at 13:53

score 11 · Answer 8 · answered Sep 25 '15 at 12:53

Laravel 5

Note that this is the procedural approach, which I use for quick debugging

    DB::enableQueryLog();

    // Run your queries
    // ...

    // Then to retrieve everything since you enabled the logging:
    $queries = DB::getQueryLog();
    foreach($queries as $i=>$query)
    {
        Log::debug("Query $i: " . json_encode($query));
    }

in your header, use:

     use DB;
     use Illuminate\Support\Facades\Log;

The output will look something like this (default log file is laravel.log):

[2015-09-25 12:33:29] testing.DEBUG: Query 0: {"query":"select * from 'users' where ('user_id' = ?)","bindings":["9"],"time":0.23}

***I know this question specified Laravel 3/4 but this page comes up when searching for a general answer. Newbies to Laravel may not know there is a difference between versions. Since I never see DD::enableQueryLog() mentioned in any of the answers I normally find, it may be specific to Laravel 5 - perhaps someone can comment on that.

score 7 · Answer 9 · answered Jun 20 '14 at 13:25

7

You can also listen for query events using this:

DB::listen(function($sql, $bindings, $time)
{
    var_dump($sql);
});

See the information from the docs here under Listening For Query Events

answered Jun 20 '14 at 13:25

SnapShot

5,464
5
42
40

score 6 · Answer 10 · answered Oct 29 '14 at 17:00

Using the query log doesnt give you the actual RAW query being executed, especially if there are bound values. This is the best approach to get the raw sql:

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

or more involved:

$query = Article::whereIn('author_id', [1,2,3])->orderBy('published', 'desc')->toSql();
dd($query);

score 5 · Answer 11 · answered Jan 27 '16 at 13:05

5

If you are using Laravel 5 you need to insert this before query or on middleware :

\DB::enableQueryLog();

answered Jan 27 '16 at 13:05

Mandeep Gill

4,577
1
28
34

score 4 · Answer 12 · answered Nov 10 '13 at 16:13

4

Or as alternative to laravel 3 profiler you can use:

https://github.com/paulboco/profiler or https://github.com/barryvdh/laravel-debugbar

answered Nov 10 '13 at 16:13

karelv

756
9
20

score 3 · Answer 13 · answered Aug 07 '14 at 13:02

in Laravel 4 you can actually use an Event Listener for database queries.

Event::listen('illuminate.query', function($sql, $bindings)
{
    foreach ($bindings as $val) {
        $sql = preg_replace('/\?/', "'{$val}'", $sql, 1);
    }

    Log::info($sql);
});

Place this snippet anywhere, e.g. in start/global.php. It'll write the queries to the info log (storage/log/laravel.log).

score 3 · Answer 14 · answered Dec 09 '14 at 12:30

3

Event::listen('illuminate.query', function($sql, $param)
{
    \Log::info($sql . ", with[" . join(',', $param) ."]<br>\n");
});

put it in global.php it will log your sql query.

answered Dec 09 '14 at 12:30

Christland

31
3

score 2 · Answer 15 · answered May 28 '14 at 00:26

The Loic Sharma SQL profiler does support Laravel 4, I just installed it. The instructions are listed here. The steps are the following:

Add "loic-sharma/profiler": "1.1.*" in the require section in composer.json
Perform self-update => php composer.phar self-update in the console.
Perform composer update => php composer.phar update loic-sharma/profiler in the console as well `
Add 'Profiler\ProfilerServiceProvider', in the provider array in app.php
Add 'Profiler' => 'Profiler\Facades\Profiler', in the aliasses array in app.php as well
Run php artisan config:publish loic-sharma/profiler in the console

Dulitha K · Answer 16 · 2015-09-18T17:02:42.780

2

Last query print

$queries = \DB::getQueryLog();
$last_query = end($queries);

// Add binding to query
foreach ($last_query['bindings'] as $val) {
        $last_query['query'] = preg_replace('/\?/', "'{$val}'", $last_query['query'], 1);
}
dd($last_query);

edited Sep 18 '15 at 17:02

answered Feb 13 '15 at 04:50

Dulitha K

2,088
1
19
18

Kamil Kiełczewski · Answer 17 · 2017-08-23T13:42:01.350

1

L4 one-liner

(which write query):

$q=\DB::getQueryLog();dd(end($q));

edited Aug 23 '17 at 13:42

answered Aug 02 '17 at 12:36

Kamil Kiełczewski

85,173
29
368
345

score 0 · Answer 18 · edited Jun 20 '20 at 09:12

0

Laravel 3

Another way to do this is:

#config/database.php

'profiler' => true

For all Queries result:

print_r(DB::profiler());

For last Result:

print_r(DB::last_query());

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 24 '15 at 11:45

Wallace Vizerra

3,382
2
28
29

score 0 · Answer 19 · edited Oct 30 '15 at 12:20

0

To get the last executed query in laravel,We will use DB::getQueryLog() function of laravel it return all executed queries. To get last query we will use end() function which return last executed query.

$student = DB::table('student')->get();
$query = DB::getQueryLog();
$lastQuery = end($query);
print_r($lastQuery);

I have taken reference from http://www.tutsway.com/how-to-get-the-last-executed-query-in-laravel.php.

edited Oct 30 '15 at 12:20

Deenadhayalan Manoharan

5,436
14
30
50

answered May 17 '15 at 12:57

Manish

328
3
9

Your answer does not seem to contribute any new knowledge to what the accepted answer by Raphael_ already covers. – Jaak Kütt May 18 '15 at 06:26

score 0 · Answer 20 · answered Nov 26 '16 at 06:57

0

There is very easy way to do it, from your laravel query just rename any column name, it will show you an error with your query.. :)

answered Nov 26 '16 at 06:57

Aamir

2,173
1
29
58

Quick hacky way. Not to useful in production, but in development mode it's fine in some cases. – instead May 28 '18 at 11:51

score 0 · Answer 21 · answered Nov 18 '20 at 13:40

In Laravel 8.x you can listen to the event by registering your query listener in a service provider as documented in laravel.com website.

//header
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

public function boot()
{
   
  DB::listen(function ($query) {            
   Log::debug("SQL : " . $query->sql);
  });

}

You can then see all the queries in the laravel.log file inside storage\logs\laravel.log

Get the Query Executed in Laravel 3/4

21 Answers21

Laravel 4+

Laravel 3

Laravel 5

L4 one-liner

Laravel 3

Linked