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.
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.
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"
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);
}
);
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.
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'
(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); });
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));
});
}
}
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);
This code is for:
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.
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.
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)
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();
Source: Stackoverflow.com