Output of Builder SQL query with passed values

To analyze SQL queries in Laravel, out of the box there is a toSql method of the \Illuminate\Database\Eloquent\Builder class. But this method returns a view (prepared statements) of sql query without the specified variables, which is not always convenient.

Using the User model as an example, we get the Builder:

$builder = \App\Models\User::whereName('Hello')->whereEmail('hello@example.com');
dd($builder->toSql());
// select * from `users` where `name` = ? and `email` = ?

The result of the toSql method returned placeholders.

To implement substitution of placeholders for real data, you need to replace them with values from the getBindings() method.

You can implement the substitution by adding a macro by placing the code in the AppServiceProvider.php file of the boot method:

\Illuminate\Database\Eloquent\Builder::macro(
    'toSqlString',
    function () {
        /** @var \Illuminate\Database\Eloquent\Builder */
        $builder = $this;

        /** @var string */
        $sql = $builder->toSql();

        $query = $sql;

        foreach ($builder->getBindings() as $binding) {
            /** @var string */
            $value = is_numeric($binding) ? $binding : "'" . $binding . "'";
            $replace = preg_replace('/\?/', $value, $query, 1);
            if (is_string($replace)) {
                $query = $replace;
            }
        }

        return $query;
    }
);

The macro extends the methods of the \Illuminate\Database\Eloquent\Builder class by adding toSqlString(). Using the toSqlString() method, display the full sql query:

$builder = \App\Models\User::whereName('Hello')->whereEmail('hello@example.com');
dd($builder->toSqlString());
// select * from `users` where `name` = 'Hello' and `email` = 'hello@example.com'