Laravel debug (dump) database queries

laravel eloquent query debug.md

Laravel debug database queries

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();

Via listeners

Global listener

\Event::listen(\Illuminate\Database\Events\QueryExecuted::class, function ($query) {
  dump([
    'sql' => $query->sql,
    'bindings' => $query->bindings,
    'time' => $query->time,
  ]);
});

Local listener

\DB::listen(function ($query) {
  dump([
    'sql' => $query->sql,
    'bindings' => $query->bindings,
    'time' => $query->time,
  ]);
});

Fill bindings

Named param placeholders

\DB::listen(function ($query) {
  $keys = array();
  $values = $params;

  # build a regular expression for each parameter
  foreach ($params as $key => $value) {
    if (is_string($key)) {
      $keys[] = '/:'.$key.'/';
    } else {
      $keys[] = '/[?]/';
    }

    if (is_string($value))
      $values[$key] = "'" . $value . "'";

    if (is_array($value))
      $values[$key] = "'" . implode("','", $value) . "'";

    if (is_null($value))
      $values[$key] = 'NULL';
  }

  dump(preg_replace($keys, $values, $query));
});

Question mark param placeholders

\DB::listen(function ($query) {
  foreach ($query->bindings as $i => $binding) {
    if ($binding instanceof \DateTime) {
      $query->bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
    } else {
      if (is_string($binding)) {
        $query->bindings[$i] = "'$binding'";
      }
    }
  }

  $log = str_replace(array('%', '?'), array('%%', '%s'), $query->sql);

  $query = vsprintf($log, $query->bindings);

  dump($query);
});

Tinker examples

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:

1. https://laravel.com/docs/master/queries#debugging
2. https://stackoverflow.com/a/41795919/2732184
3. https://stackoverflow.com/a/34638344/2732184
4. https://www.youtube.com/watch?v=3TJfR1Ta4GU