Bind parameters into the SQL query for Laravel ORM

gistfile1.php

<?php

class MyModel extends Eloquent {
  
  public function getSql() 
  {
    $builder = $this->getBuilder();	
    $sql = $builder->toSql();
    foreach($builder->getBindings() as $binding)
    {
      $value = is_numeric($binding) ? $binding : "'".$binding."'";
      $sql = preg_replace('/\?/', $value, $sql, 1);
    }
    return $sql;
  }
  
}

Very helpful 👌

Just in case you want to have the sql query in case of exception (To be specific on Lumen 5.3):

        if ($e instanceof Illuminate\Database\QueryException) {
            // Log SQL
            $bindings = $e->getBindings();
            foreach ($bindings as &$binding) {
                $binding = is_numeric($binding) ? $binding : "'" . $binding . "'";
            }
            $sqlString = Str::replaceArray('?', $bindings, $e->getSql());
        }

This does not work if I had a relation on my eloquent model LIke
Post::with("comments")->toSql();
only return -- select * from posts --

Thanks

thks very much.. kiss kiss

Macroable way,

Add toRawSql() to Query Builder.

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

Add an alias in Eloquent Builder.

\Illuminate\Database\Eloquent\Builder::macro('toRawSql', function(){
    return ($this->getQuery()->toRawSql());
});

@ijasxyz thank you!!

When binding is a bool, its print an empty value.

Follow my adaptation fixing this issue:

public static function eloquentSqlWithBindings($queryBuilder)
{
    $sql = str_replace('?', '%s', $queryBuilder->toSql());

    $handledBindings = array_map(function ($binding) {
        if (is_numeric($binding)) {
            return $binding;
        }

        if (is_bool($binding)) {
            return ($binding) ? 'true' : 'false';
        }

        return "'{$binding}'";
    }, $queryBuilder->getBindings());

    return vsprintf($sql, $handledBindings);
}

@danilopinotti the query fails on the following select:
DB::raw("(STR_TO_DATE(date_string, '%m/%d/%Y')) AS date_time")

error:
vsprintf(): Too few arguments

Here's updated version which fixes quite a few binding value bugs and also aligns more closely with how Laravel serializes true / false values into 1 / 0

\Illuminate\Database\Query\Builder::macro('fullSql', function () {
	$sql = str_replace(['%', '?'], ['%%', '%s'], $this->toSql());

	$handledBindings = array_map(function ($binding) {
		if (is_numeric($binding)) {
			return $binding;
		}

		$value = str_replace(['\\', "'"], ['\\\\', "\'"], $binding);

		return "'{$value}'";
	}, $this->getConnection()->prepareBindings($this->getBindings()));

	$fullSql = vsprintf($sql, $handledBindings);

	return $fullSql;
});

\Illuminate\Database\Query\Builder::macro('ddd', function () {
	dd($this->fullSql());
});

Examples:

dump(\DB::query()->where([
	'number'                    => 12,
	'decimal'                   => 5.5,
	'bool_true'                 => true,
	'bool_false'                => false,
	'carbon'                    => now(),
	'polymorphic_classable'     => 'App\User',
	'with_sprint_formats'       => '%M %e %y',
	'raw'                       => \DB::raw('some raw expression'),
	'question_string'           => 'hello?',
	'single_quote'              => "'",
	'stringable_class'          => new class {
		public function __toString()
		{
			return 'foobar';
		}
	}
])->fullSql());

$query->ddd(); // will dump and die the full sql.
$query->dd(); // Built into Laravel already, will print the sql / bindings seperately.

Output:

select * where (`number` = 12 and `decimal` = 5.5 and `bool_true` = 1 and `bool_false` = 0 and `carbon` = '2020-01-14 23:09:09' and `polymorphic_classable` = 'App\\User' and `with_sprint_formats` = '%M %e %y' and `raw` = some raw expression and `question_string` = 'hello?' and `single_quote` = '\'' and `stringable_class` = 'foobar')

@garygreen awesome. Thanks man!

@JesseObrien thank you!!.

fullSql

@garygreen Looks like it does not work with '%like%' case, when I use whereRaw('name like %test-name%'), it replaces % with %%

laravel-debugbar and/or Telescope will log your queries out too :) This is helpful snippet. Thank you

Slight variation for Laravel 9:

\Illuminate\Database\Query\Builder::macro('toRawSql', fn (): string => str($this->toSql())
    ->replaceArray('?', collect($this->getConnection()->prepareBindings($this->getBindings()))
        ->map(fn ($binding) => is_numeric($binding) ? $binding : str($binding)->replace(['\\', "'"], ['\\\\', "\'"])->prepend("'")->append("'")->__toString())
        ->toArray()
    )
);