use \Illuminate\Database\Capsule\Manager as Capsule;use \Illuminate\Events\Dispatcher;use \Illuminate\Container\Container;
$capsule = new Capsule;
$capsule->addConnection([// connection details]);// Set the event dispatcher used by Eloquent models... (optional)$capsule->setEventDispatcher(new Dispatcher(new Container));
// Make this Capsule instance available globally via static methods... (optional)$capsule->setAsGlobal();
// Setup the Eloquent ORM...(optional unless you've used setEventDispatcher())$capsule->bootEloquent();
// Listen for Query Events for Debug$events = new Dispatcher;$events->listen('illuminate.query', function($query, $bindings, $time, $name){// Format binding data for sql insertionforeach ($bindings as $i => $binding) {if ($binding instanceof \DateTime) {$bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');} else if (is_string($binding)) {$bindings[$i] = "'$binding'";`enter code here`}}
// Insert bindings into query$query = str_replace(array('%', '?'), array('%%', '%s'), $query);$query = vsprintf($query, $bindings);
// Debug SQL queriesecho 'SQL: [' . $query . ']';});
$capsule->setEventDispatcher($events);
/*** getSql** Usage:* getSql( DB::table("users") )** Get the current SQL and bindings** @param mixed $query Relation / Eloquent Builder / Query Builder* @return array Array with sql and bindings or else false*/function getSql($query){if( $query instanceof Illuminate\Database\Eloquent\Relations\Relation ){$query = $query->getBaseQuery();}
if( $query instanceof Illuminate\Database\Eloquent\Builder ){$query = $query->getQuery();}
if( $query instanceof Illuminate\Database\Query\Builder ){return [ 'query' => $query->toSql(), 'bindings' => $query->getBindings() ];}
return false;}
/*** logQuery** Get the SQL from a query in a closure** Usage:* logQueries(function() {* return User::first()->applications;* });** @param closure $callback function to call some queries in* @return Illuminate\Support\Collection Collection of queries*/function logQueries(closure $callback){// check if query logging is enabled$logging = DB::logging();
// Get number of queries$numberOfQueries = count(DB::getQueryLog());
// if logging not enabled, temporarily enable itif( !$logging ) DB::enableQueryLog();
$query = $callback();
$lastQuery = getSql($query);
// Get querylog$queries = new Illuminate\Support\Collection( DB::getQueryLog() );
// calculate the number of queries done in callback$queryCount = $queries->count() - $numberOfQueries;
// Get last queries$lastQueries = $queries->take(-$queryCount);
// disable query loggingif( !$logging ) DB::disableQueryLog();
// if callback returns a builder object, return the sql and bindings of itif( $lastQuery ){$lastQueries->push($lastQuery);}
return $lastQueries;}
用法：
getSql( DB::table('users') );// returns// [// "sql" => "select * from `users`",// "bindings" => [],// ]
getSql( $project->rooms() );// returns// [// "sql" => "select * from `rooms` where `rooms`.`project_id` = ? and `rooms`.`project_id` is not null",// "bindings" => [ 7 ],// ]
<?php
namespace App\Providers;
use Illuminate\Support\Facades\DB;use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider{/*** Bootstrap any application services.** @return void*/public function boot(){DB::listen(function ($query) {// $query->sql// $query->bindings// $query->time});}
/*** Register the service provider.** @return void*/public function register(){//}}
public function jobs(){return $this->belongsToMany(Job::class, 'eqtype_jobs')->withPivot(['created_at','updated_at','id'])->orderBy('pivot_created_at','desc');}
public function jobs(){return $this->belongsToMany(Job::class, 'eqtype_jobs')->withPivot(['created_ats','updated_at','id'])->orderBy('pivot_created_at','desc');}
SQL: select jobs.*, eqtype_jobs.set_id as pivot_set_id, eqtype_jobs.job_id as pivot_job_id, eqtype_jobs.created_ats as pivot_created_ats, eqtype_jobs.updated_at as pivot_updated_at, eqtype_jobs.id as pivot_id from jobs inner join eqtype_jobs on jobs.id = eqtype_jobs.job_id where eqtype_jobs.set_id = 56 order by pivot_created_at desc limit 20 offset 0
Note: from Laravel 5.1 to 5.3, Since Eloquent Builder doesn't make use of the Macroable trait, cannot add toRawSql an alias to the Eloquent Builder on the fly. Follow the below example to achieve the same.
$users = DB::table('users')->select(DB::raw('count(*) as user_count, username '))->where('uid', '>=', 10)->limit(100)->groupBy('username')->get();dd($users);
查看日志storage/logs/laravel-2019-10-27.log：
[2019-10-27 17:39:17] local.DEBUG: [SQL EXEC] {"raw sql":"select count(*) as user_count, username from `users` where `uid` >= '10' group by `username` limit 100","time":304.21}
//create query$query=DB::table(...)...->where(...)...->orderBy(...)...$log=[];//array of log lines...//invoked on query execution if query log is enabledDB::listen(function ($query)use(&$log){$log[]=$query;//enqueue query data to logs});//enable query logDB::enableQueryLog();$res=$query->get();//execute
function parse_sql(string $sql, array $bindings) : string{$compiled_bindings = array_map('compile_binding', $bindings);
return preg_replace_array("/\?/", $compiled_bindings, $sql);}
function compile_binding($binding){$grammar = new MySqlGrammar;
if (is_bool($binding)){return (int)$binding; //This line depends on the database implementation}
if(is_string($binding)){return "'$binding'";}
if ($binding instanceof DateTimeInterface){return $binding->format($grammar->getDateFormat());}
return $binding;}
