How do I obtain raw SQL from a query builder?

The Detective's Notebook: Unveiling the raw SQL

Reporting for duty: Enabling Query Logging

In your quest for SQL, logging is your trusty sidekick in Laravel:

\DB::enableQueryLog(); // Standby for duty!
// Deploy query
User::where('name', 'John')->get(); // Look! John's at it again!
// Report findings
dd(\DB::getQueryLog()); // Detective's report: 'Here's all the dirt I found!'


This code shows you the raw SQL, bindings, and even the execution time and connection name — a real digital detective's report.

Filling in the blanks: Placeholder Substitution

Ever needed to replace those pesky '?' placeholders with the actual values? Here's how we do it:

$sql = User::where('name', 'John')->toSql(); // What's John up to now?
$bindings = User::where('name', 'John')->getBindings(); // Give me the deets!
$boundSql = vsprintf(str_replace(['?'], ['\'%s\''], $sql), $bindings); // Voila! '?' found a date!
echo $boundSql;


This fills in the placeholders with the corresponding values, making the SQL statement easy to read, and saving us all from a '?' existential crisis!

Spy-gadget: Real-time SQL Monitoring

And for those who love a real-time chase, Laravel lets you track SQL queries as they run, by using an event listener like this:

\Event::listen('illuminate.query', function($query, $bindings, $time, $connectionName) {
    // Format the captured query and unveil it
});


Debug with Style: Unleash Peak Performance

Give it a head-to-toe check!

Systematically layout the SQL transaction data for some high-level debugging:

The SQL statement - a peak into your query. Bindings or parameters - what's making your query unique. The execution time - how's your query performing. Connection info - where's your query being executed.

Add some oomph to your toolkit!

Level up with Eloquent debug bar or laravel-telescope package for a more intuitive debugging interface — because who said debugging can't be done in style?

Custom Logging: Making Debugging a Breeze

Use a custom logger function to always have your SQL queries and bindings in sync minimally formatted:

customLogFunction(); // Bippity-Boppity-Boo! Here's your SQL shoe!


Look, don’t touch! Handle Data Types with Care

When using a function to embed bindings into the query, remember they come in all shapes and sizes. So, do a type check before you coerce them into a string:

if (gettype($binding) === 'string') {
    str_replace('?', "'" . $binding . "'", $query);
} else {
    str_replace('?', $binding, $query);
}
