Get the Query Executed in Laravel 3/4

Get the Query Executed in Laravel 3/4

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.

How to Get the Query Executed in Laravel 5? DB::getQueryLog() Returning Empty Array

By default, the query log is disabled in Laravel 5:
https://github.com/laravel/framework/commit/e0abfe5c49d225567cb4dfd56df9ef05cc297448

You will need to enable the query log by calling:

DB::enableQueryLog();

// and then you can get query log

dd(DB::getQueryLog());

or register an event listener:

DB::listen(
    function ($sql, $bindings, $time) {
        //  $sql - select * from `ncv_users` where `ncv_users`.`id` = ? limit 1
        //  $bindings - [5]
        //  $time(in milliseconds) - 0.38 
    }
);  

Some Tips

1. Multiple DB connections

If you have more than one DB connection you must specify which connection to log

To enables query log for my_connection:

DB::connection('my_connection')->enableQueryLog();

To get query log for my_connection:

print_r(
   DB::connection('my_connection')->getQueryLog()
);

2. Where to enable query log ?

For an HTTP request lifecycle, you can enable query log in the `handle` method of some `BeforeAnyDbQueryMiddleware` [middleware][1] and then retrieve the executed queries in the [`terminate`][2] method of the same middleware.

class BeforeAnyDbQueryMiddleware
{
    public function handle($request, Closure $next)
    {
        DB::enableQueryLog();
        return $next($request);
    }

    public function terminate($request, $response)
    {
        // Store or dump the log data...
        dd(
            DB::getQueryLog()
        );
    }
}

A middleware's chain will not run for artisan commands, so for CLI execution you can enable query log in the artisan.start event listener.

For example you can put it in the bootstrap/app.php file

$app['events']->listen('artisan.start', function(){
    \DB::enableQueryLog();
});

3. Memory

Laravel keeps all queries in memory. So in some cases, such as when inserting a large number of rows, or having a long running job with a lot of queries, this can cause the application to use excess memory.

In most cases you will need the query log only for debugging, and if that is the case I would recommend you enable it only for development.

if (App::environment('local')) {
    // The environment is local
    DB::enableQueryLog();
}

References

• https://laravel.com/docs/5.0/database#query-logging

How to get a query response time on Eloquent?

Laravel itself define a microtime in index.php

define('LARAVEL_START', microtime(true));

So you should do the same after the query and calculate the difference

$begin = microtime(true);
$arf = App\Roles::where('description','test')->get();
$end = microtime(true) - $begin;

Now $end is the response time

Comparing against the globally defined microtime may not be accurate as HTTP requests go through a pipeline to reach the query

You can also listen to query events and access the time

From the docs

\DB::listen(function ($query) {
      info('Query took ' . $query->time);
});

And that is coming from Illuminate\Database\Events\QueryExecuted here

<?php

namespace Illuminate\Database\Events;

class QueryExecuted
{
    /**
     * The SQL query that was executed.
     *
     * @var string
     */
    public $sql;

    /**
     * The array of query bindings.
     *
     * @var array
     */
    public $bindings;

    /**
     * The number of milliseconds it took to execute the query.
     *
     * @var float
     */
    public $time;

    /**
     * The database connection instance.
     *
     * @var \Illuminate\Database\Connection
     */
    public $connection;

    /**
     * The database connection name.
     *
     * @var string
     */
    public $connectionName;

    /**
     * Create a new event instance.
     *
     * @param  string  $sql
     * @param  array  $bindings
     * @param  float|null  $time
     * @param  \Illuminate\Database\Connection  $connection
     * @return void
     */
    public function __construct($sql, $bindings, $time, $connection)
    {
        $this->sql = $sql;
        $this->time = $time;
        $this->bindings = $bindings;
        $this->connection = $connection;
        $this->connectionName = $connection->getName();
    }
}

Laravel getQueryLog show line query is executed

You can't do it with getQueryLog(), but you can listen to queries, generate a backtrace and finally extract line number:

// routes.php
Event::listen('illuminate.query', function($query,$binding,$time,$connections){

  $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);

  foreach ($backtrace as $trace) {
    if(array_key_exists('file',$trace) && array_key_exists('line',$trace)){
      if( strpos($trace['file'],base_path().'/app') !== false ){
        var_dump(array(
          'query'    => $query
          ,'binding' => $binding
          ,'time'    => $time
          ,'connection' => $connections
          ,'file' => $trace['file']
          ,'line' => $trace['line']
        ));
        break;
      }
    }
  }
});

How can i handle and limit the number of the query executed on the laravel project?

If you want to know which queries are sent to your db, you can use these two solutions :

• https://laravel.com/docs/5.5/database#listening-for-query-events
• https://github.com/barryvdh/laravel-debugbar

If you are looking for what does impact your performances, you can use these two instructions to target the right lines of your code :

$time = microtime(true); // we save the precise time in milliseconds
// some lines of code
dd(microtime(true) - $time); // prints the milliseconds spent since $time was saved

Let me know if it helped !

---

Related Topics