Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Bind parameters into the SQL query for Laravel ORM
<?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;
}
}
@Marian0

This comment has been minimized.

Copy link

@Marian0 Marian0 commented Jun 29, 2015

Very helpful 👌

@hpaknia

This comment has been minimized.

Copy link

@hpaknia hpaknia commented Oct 21, 2016

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());
        }
@mkdesignn

This comment has been minimized.

Copy link

@mkdesignn mkdesignn commented Nov 18, 2016

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

@bunyamink

This comment has been minimized.

Copy link

@bunyamink bunyamink commented Feb 22, 2018

Thanks

@nguyentrongthanh123

This comment has been minimized.

Copy link

@nguyentrongthanh123 nguyentrongthanh123 commented Sep 26, 2018

thks very much.. kiss kiss

@ijasxyz

This comment has been minimized.

Copy link

@ijasxyz ijasxyz commented Sep 27, 2018

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());
});
@ramilexe

This comment has been minimized.

Copy link

@ramilexe ramilexe commented Nov 20, 2018

@ijasxyz thank you!!

@danilopinotti

This comment has been minimized.

Copy link

@danilopinotti danilopinotti commented Aug 23, 2019

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);
}
@cmple

This comment has been minimized.

Copy link

@cmple cmple commented Oct 20, 2019

@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

@garygreen

This comment has been minimized.

Copy link

@garygreen garygreen commented Jan 14, 2020

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')
@cmple

This comment has been minimized.

Copy link

@cmple cmple commented Jan 15, 2020

@garygreen awesome. Thanks man!

@Shrikant-28

This comment has been minimized.

Copy link

@Shrikant-28 Shrikant-28 commented Jan 21, 2020

@JesseObrien thank you!!.

@Anatoliy-Petrov

This comment has been minimized.

Copy link

@Anatoliy-Petrov Anatoliy-Petrov commented Aug 4, 2020

fullSql

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

@connor11528

This comment has been minimized.

Copy link

@connor11528 connor11528 commented Apr 28, 2021

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment