Get "PHP 8 in a Nuthshell" (Now comes with PHP 8.3)
Amit Merchant

Amit Merchant

A blog on PHP, JavaScript, and more

Outputting Eloquent query unparameterized in Laravel

One of the many benefits of using Laravel as a framework is the immense customizability it comes with. So, you can use this to overcome some of the limitations.

For instance, currently, if you want to output an Eloquent query in its SQL form, you can invoke the toSql() method onto it like so.

$books = Book::where('author', 'Ruskin Bond')->toSql();

// select * from `books` where `author` = ?

As you can tell, the toSql() would output the query but if you notice it doesn’t really output the parameters passed to it. Instead, it would just print ?. This is because Laravel uses prepared statements. They’re a way of writing an SQL statement without dropping variables directly into the SQL string. The ? you see are placeholders or bindings for the information which will later be substituted and automatically sanitized by PDO.

This means you need to replace the ?s with actual values in your query manually which as you can tell is cumbersome if you just want to debug it in your favorite SQL GUI program such as MySQL Workbench.

So, how to get around this? Read on.

The toRawSql macro

Well, recently, in a tweet by @kirschbaum_dev, I got to know about this custom toRawSql macro (which you can define in the boot method of your AppServiceProvider) which can be used to output the SQL query unparameterized.

use Illuminate\Database\Eloquent\Builder;

Builder::macro('toRawSql', function() {
    return array_reduce($this->getBindings(), function($sql, $binding) {
        return preg_replace('/\?/', is_numeric($binding)
            ? $binding
            : "'".$binding."'", $sql, 1);
    }, $this->toSql());

As you can tell, the macro makes use of the getBindings() method which returns an array of all the parameter values of the query coupled with the preg_replace function to replace those with the ?s in the query returned by toSql() method.

So, if we want to use this macro in our previous example, we can use it like so.

$books = Book::where('author', 'Ruskin Bond')->toRawSql();

// select * from `books` where `author` = 'Ruskin Bond'

As you can tell, using this macro, we now have a raw SQL query with the parameter values already filled in!

Pretty handy, right?

Learn the fundamentals of PHP 8 (including 8.1, 8.2, and 8.3), the latest version of PHP, and how to use it today with my new book PHP 8 in a Nutshell. It's a no-fluff and easy-to-digest guide to the latest features and nitty-gritty details of PHP 8. So, if you're looking for a quick and easy way to PHP 8, this is the book for you.

Like this article? Consider leaving a


👋 Hi there! I'm Amit. I write articles about all things web development. You can become a sponsor on my blog to help me continue my writing journey and get your brand in front of thousands of eyes.