How to Get the Query Executed in Laravel 5? DB::getQueryLog() Returning Empty Array
PhpLoggingLaravelLaravel 5Php Problem Overview
I'm trying to view the log for a query, but DB::getQueryLog() is just returning an empty array:
$user = User::find(5);
print_r(DB::getQueryLog());
Result
Array
(
)
How can I view the log for this query?
Php Solutions
Solution 1 - Php
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 thehandle method of some BeforeAnyDbQueryMiddleware middleware and then retrieve the executed queries in the terminate 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
Solution 2 - Php
If all you really care about is the actual query (the last one run) for quick debugging purposes:
DB::enableQueryLog();
# your laravel query builder goes here
$laQuery = DB::getQueryLog();
$lcWhatYouWant = $laQuery[0]['query']; # <-------
# optionally disable the query log:
DB::disableQueryLog();
do a print_r() on $laQuery[0] to get the full query, including the bindings. (the $lcWhatYouWant variable above will have the variables replaced with ??)
If you're using something other than the main mysql connection, you'll need to use these instead:
DB::connection("mysql2")->enableQueryLog();
DB::connection("mysql2")->getQueryLog();
(with your connection name where "mysql2" is)
Solution 3 - Php
You need to first enable query logging
DB::enableQueryLog();
Then you can get query logs by simply:
dd(DB::getQueryLog());
It would be better if you enable query logging before application starts, which you can do in a BeforeMiddleware and then retrieve the executed queries in AfterMiddleware.
Solution 4 - Php
Put this on routes.php file:
\Event::listen('Illuminate\Database\Events\QueryExecuted', function ($query) {
echo'<pre>';
var_dump($query->sql);
var_dump($query->bindings);
var_dump($query->time);
echo'</pre>';
});
Submitted by msurguy, source code in this page. You will find this fix-code for laravel 5.2 in comments.
Solution 5 - Php
Apparently with Laravel 5.2, the closure in DB::listen only receives a single parameter.
So, if you want to use DB::listen in Laravel 5.2, you should do something like:
DB::listen(
function ($sql) {
// $sql is an object with the properties:
// sql: The query
// bindings: the sql query variables
// time: The execution time for the query
// connectionName: The name of the connection
// To save the executed queries to file:
// Process the sql and the bindings:
foreach ($sql->bindings as $i => $binding) {
if ($binding instanceof \DateTime) {
$sql->bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
} else {
if (is_string($binding)) {
$sql->bindings[$i] = "'$binding'";
}
}
}
// Insert bindings into query
$query = str_replace(array('%', '?'), array('%%', '%s'), $sql->sql);
$query = vsprintf($query, $sql->bindings);
// Save the query to file
$logFile = fopen(
storage_path('logs' . DIRECTORY_SEPARATOR . date('Y-m-d') . '_query.log'),
'a+'
);
fwrite($logFile, date('Y-m-d H:i:s') . ': ' . $query . PHP_EOL);
fclose($logFile);
}
);
Solution 6 - Php
Use toSql() instead of get() like so:
$users = User::orderBy('name', 'asc')->toSql();
echo $users;
// Outputs the string:
'select * from `users` order by `name` asc'
Solution 7 - Php
For laravel 5.8 you just add dd or dump.
Ex:
DB::table('users')->where('votes', '>', 100)->dd();
or
DB::table('users')->where('votes', '>', 100)->dump();
Solution 8 - Php
(Laravel 5.2) I find the simplest way is just to add one code line to monitor the sql queries:
\DB::listen(function($sql) {var_dump($sql); });
Solution 9 - Php
Query Execution
\Event::listen('Illuminate\Database\Events\QueryExecuted', function ($query) {
$sql = $query->sql;
$time = $query->time;
$connection = $query->connection->getName();
Log::debug('query : '.$sql);
Log::debug('time '.$time);
Log::debug('connection '.$connection);
});
Query
StaffRegister::all();
Output
[2021-03-14 08:00:57] local.DEBUG: query : select * from `staff_registers`
[2021-03-14 08:00:57] local.DEBUG: time 0.93
[2021-03-14 08:00:57] local.DEBUG: connection mysql
complete structure
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\Log;
use App\Models\StaffRegister;
class AuthController extends Controller
{
public function index(){
\Event::listen('Illuminate\Database\Events\QueryExecuted', function ($query) {
$sql = $query->sql;
$time = $query->time;
$connection = $query->connection->getName();
Log::debug('query : '.$sql);
Log::debug('time '.$time);
Log::debug('connection '.$connection);
});
$obj = StaffRegister::all();
return $obj;
}
}
Accurate Method For GET REPOSNSE
Solution 10 - Php
In continuing of the Apparently with Laravel 5.2, the closure in DB::listen only receives a single parameter... response above : you can put this code into the Middleware script and use it in the routes.
Additionally:
use Monolog\Logger;
use Monolog\Handler\StreamHandler;
$log = new Logger('sql');
$log->pushHandler(new StreamHandler(storage_path().'/logs/sql-' . date('Y-m-d') . '.log', Logger::INFO));
// add records to the log
$log->addInfo($query, $data);
Solution 11 - Php
This code is for:
- Laravel 5.2
- Log the statements into the mysql database
Here is the code, which is based on @milz 's answer:
DB::listen(function($sql) {
$LOG_TABLE_NAME = 'log';
foreach ($sql->bindings as $i => $binding) {
if ($binding instanceof \DateTime) {
$sql->bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
} else {
if (is_string($binding)) {
$sql->bindings[$i] = "'$binding'";
}
}
}
// Insert bindings into query
$query = str_replace(array('%', '?'), array('%%', '%s'), $sql->sql);
$query = vsprintf($query, $sql->bindings);
if(stripos($query, 'insert into `'.$LOG_TABLE_NAME.'`')===false){
$toLog = new LogModel();
$toLog->uId = 100;
$toLog->sql = $query;
$toLog->save();
}
});
The core is the if(stripos... line, which prevents the recursion of inserting the insert into log sql statement into database.
Solution 12 - Php
Suppose you want to print the SQL query of the following statements.
$user = User::find(5);
You just need to do as follows:
DB::enableQueryLog();//enable query logging
$user = User::find(5);
print_r(DB::getQueryLog());//print sql query
This will print the last executed query in Laravel.
Solution 13 - Php
I think the answer located in this article: https://arjunphp.com/laravel-5-5-log-eloquent-queries/
is fast and simple to achieve query logging.
You just have to add to the AppServiceProvider in the boot method a callback to listen to DB queries:
namespace App\Providers;
use DB;
use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider
{
public function boot()
{
DB::listen(function($query) {
logger()->info($query->sql . print_r($query->bindings, true));
});
}
}
Solution 14 - Php
Add this function to your helper file and simply call.
function getRawQuery($sql){
$query = str_replace(array('?'), array('\'%s\''), $sql->toSql());
$query = vsprintf($query, $sql->getBindings());
return $query;
}
Output: "select * from user where status = '1' order by id desc limit 25 offset 0"
Solution 15 - Php
For laravel 5 and onwards using only DB::getQueryLog() , will not do. BY default in this the value of
protected $loggingQueries = false;
change it to
protected $loggingQueries = true;
in the below file for logging query.
/vendor/laravel/framework/src/illuminate/Database/Connection.php
And then we can use the DB::getQueryLog() where you want to print the query.