CODEWITHSUNDEEP
phplaravelormeloquentlaravel-query-builder
Patrick Maciel
Patrick Maciel

Reputation: 4944

Get the Query Executed in Laravel 3/4

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?

Upvotes: 188

Views: 176510

Answers (22)

shmuels
shmuels

Reputation: 1393

As of Laravel 5.7.7+ you can use Laravel Telescope to see queries.

Laravel Telescope makes a wonderful companion to your local Laravel development environment. Telescope provides insight into the requests coming into your application, exceptions, log entries, database queries, queued jobs, mail, notifications, cache operations, scheduled tasks, variable dumps, and more.

Upvotes: 0

rmobis
rmobis

Reputation: 26992

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.

Upvotes: 320

Nava Bogatee
Nava Bogatee

Reputation: 1765

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

Upvotes: 0

Kamil Kiełczewski
Kamil Kiełczewski

Reputation: 92417

L4 one-liner

(which write query):

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

Upvotes: 1

Aamir
Aamir

Reputation: 2283

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

Upvotes: 0

Ricardo Rossi
Ricardo Rossi

Reputation: 524

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/

Upvotes: 16

Mandeep Gill
Mandeep Gill

Reputation: 4895

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

\DB::enableQueryLog();

Upvotes: 5

Manish
Manish

Reputation: 328

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.

Upvotes: 0

Bryan
Bryan

Reputation: 2211

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.

Upvotes: 11

Dulitha K
Dulitha K

Reputation: 2088

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

Upvotes: 2

Wallace Vizerra
Wallace Vizerra

Reputation: 3542

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());

Upvotes: 0

Christland
Christland

Reputation: 31

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.

Upvotes: 3

omar j
omar j

Reputation: 541

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

Upvotes: 6

aebersold
aebersold

Reputation: 11506

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

Upvotes: 3

Markus Hedlund
Markus Hedlund

Reputation: 24254

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

Upvotes: 19

JamesPlayer
JamesPlayer

Reputation: 1874

For Eloquent you can just do:

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

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

Upvotes: 26

SnapShot
SnapShot

Reputation: 5544

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

Upvotes: 7

bicycle
bicycle

Reputation: 8430

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

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

Upvotes: 2

karelv
karelv

Reputation: 814

Or as alternative to laravel 3 profiler you can use:

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

Upvotes: 4

Adam
Adam

Reputation: 211

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>

Upvotes: 14

misaizdaleka
misaizdaleka

Reputation: 1826

For Laraver 4 it's 

DB::getQueryLog()

Upvotes: 14

dualed
dualed

Reputation: 10502

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

Upvotes: 31

Related Questions