(SOLVED) How to get the raw SQL query from the Laravel Query Builder ?

2 min read

How to get the raw SQL query from the Laravel Query Builder 

Method 1 

DB::QueryLog() works only after you execute the query using $builder->get().

If you want to get the raw query before or without executing the query, you can use the $builder->toSql() method.

Example to get the raw SQL and to replace '?' with actual binding values:

$query = str_replace(array('?'), array('\'%s\''), $builder->toSql());
$query = vsprintf($query, $builder->getBindings());
dump($query);

$result = $builder->get();

Or you can deliberately trigger an error, for example, by using a non-existent table or column. Then you can see the generated query in the exception message.

App\User::query() ->where('created_at', '<', now()->subYear())

->with('assignedApps', 'courses')

->orderBy('email', 'asc')

->limit(5)

->toSql();

Output:

select * from `users` where `created_at` < ? order by `email` asc limit 5

Using the Laravel Query Log

The second method is the Laravel query log that collects all queries within a request. You can enable this log, run your query and dump the output.

Example

DB::enableQueryLog();

App\User::query()
    ->where('created_at', '<', now()->subYear())
    ->with('assignedApps', 'courses')
    ->orderBy('email', 'asc')
    ->limit(5)
    ->get();

dd(DB::getQueryLog());

This code leads to the output:

array:3 [â–¼
  0 => array:3 [â–¼
    "query" => "select * from `users` where `created_at` < ? order by `email` asc limit 5"
    "bindings" => array:1 [â–¼
      0 => Illuminate\Support\Carbon @1588525477 {#1595 â–¶}
    ]
    "time" => 7.97
  ]
  1 => array:3 [â–¼
    "query" => "select `apps`.*, `user_apps`.`user_id` as `pivot_user_id`, `user_apps`.`app_id` as `pivot_app_id`, `user_apps`.`created_at` as `pivot_created_at`, `user_apps`.` â–¶"
    "bindings" => []
    "time" => 2.81
  ]
  2 => array:3 [â–¼
    "query" => "select `courses`.*, `user_courses`.`user_id` as `pivot_user_id`, `user_courses`.`course_id` as `pivot_course_id`, `user_courses`.`created_at` as `pivot_created_ â–¶"
    "bindings" => []
    "time" => 0.54
  ]
]

This gives you detailed information about the executed query and their execution time. If the query has data bindings, the query logs lists them and makes it easy to check your data,

Digging deeper

While you can get the raw SQL query with the methods above,

there are situations where you debug the runtime or memory allocation of a query and want to know why it takes longer than expected.

Total Execution Time content: 0.00012971560160319 Mins Total Execution Time social : 0.0000 Mins

Read Next

Total Execution Time red next: 0.0000 Mins

Search Now






Categories