Raw SQL
SELECT
users.name AS staff,
facilities.name AS facility
FROM
users
INNER JOIN facilities ON facilities.id = users.facility_id
WHERE
facilities.name = 'Mulago Hospital'
LIMIT 2;\DB::select($rawSql);Query Builder
\DB::table('users')
->join('facilities', 'facilities.id', '=', 'users.facility_id')
->select(['users.name as staff', 'facilities.name as facility'])
->where('facilities.name', 'Mulago Hospital')
->take(2)
// ->get();
->dump();Eloquent
\App\Models\User::query()
->whereHas('facility', function($query) {
$query->select(['facilities.name as facility']);
$query->where('facilities.name', 'Mulago Hospital');
});
->select(['users.name as staff'])
->take(2)
// ->get();
->dump();Global listener Local listener
\Event::listen(\Illuminate\Database\Events\QueryExecuted::class, function ($query) {
// \DB::listen(function ($query) {
dump([
'sql' => $query->sql,
'bindings' => $query->bindings,
'time' => $query->time,
]);
});Fill bindings
function interpolateQuery(string $query, array $bindings)
{
$keys = $values = [];
foreach ($bindings as $key => $value) {
if (is_string($key)) {
$keys[] = '/:' . $key . '/';
}
if ($value instanceof \DateTime) {
$values[$key] = $value->format('\'Y-m-d H:i:s\'');
continue;
}
if (is_numeric($value)) {
$values[$key] = $value;
continue;
}
if (is_array($value)) {
$values[$key] = "'" . implode("', '", $value) . "'";
continue;
}
if (is_null($value)) {
$values[$key] = 'NULL';
continue;
}
$values[$key] = "'" . $value . "'";
}
if (empty($keys)) {
return preg_replace_callback('/[?]/', function ($matches) use (&$values) {
return array_shift($values);
}, $query);
}
return preg_replace($keys, $values, $query);
}\DB::listen(function ($query) {
$location = collect(debug_backtrace())->filter(function ($trace) {
return isset($trace['file']) && !str_contains($trace['file'], 'vendor/');
})->first();
// $bindings = implode(", ", $query->bindings);
// Log::debug("Sql: $query->sql\nBindings: $bindings\nTime: $query->time\nFile: {$location['file']}\nLine: {$location['line']}");
$sql = self::interpolateQuery($query->sql, $query->bindings);
Log::debug("[SQL {$query->time}ms] \"{$sql};\" {$location['file']}:{$location['line']}");
});Psy Shell v0.9.9 (PHP 7.3.11 — cli) by Justin Hileman
>>> \DB::listen(function ($query) {
...
... });
=> null
>>>
>>> $users = (function() {
... return \DB::table('users')
... ->join('facilities', 'facilities.id', '=', 'users.facility_id')
... ->select(['users.name as staff', 'facilities.name as facility'])
... ->where('facilities.name', 'Mulago Hospital')
... ->take(2)
... ->get();
... })();
"select "users"."name" as "staff", "facilities"."name" as "facility" from "users" inner join "facilities" on "facilities"."id" = "users"."facility_id" where "facilities"."name" = 'Mulago Hospital' limit 2"
>>> Psy Shell v0.9.9 (PHP 7.3.11 — cli) by Justin Hileman
>>> \DB::listen(function ($query) {
...
... });
=> null
>>>
>>> $users = (function() {
... $query = \App\Models\User::query();
... $query->whereHas('facility', function($query) {
... $query->select(['facilities.name as facility']);
... $query->where('facilities.name', 'Mulago Hospital');
... });
... $query->select(['users.name as staff']);
... $query->take(2);
... return $query->get();
... })();
"select "users"."name" as "staff", "facilities"."name" as "facility" from "users" inner join "facilities" on "facilities"."id" = "users"."facility_id" where "facilities"."name" = 'Mulago Hospital' limit 2"
>>>Resources:
