Laravel how to get query with bindings?

Question

I have some query that I need to pass to another query using query builder

$query = DB::table('table')->whereIn('some_field', [1,2,30])->toSql();

Model::join(DB::raw("({$query}) as table"), function($join) {
    $join->on('model.id', '=', 'table.id');
})

which should results with

Select * from model join (select * from table where some_field in (1,2,30)) as table on model.id = table.id

but the bindings are not passed, which force me to do

$query = DB::table('table')->whereRaw('some_field in ('. join(',', [1,2,30]) .')')->toSql();

what can be unsafe at times. How can I get the query with bindings?

Is `some_field` an integer? If so you could use `join(',', array_map('intval', [1,2,30]))` to make sure the array only contains integers. — Marwelln, Dec 05 '14 at 11:10
You can use `toRawSql()` with Laravel 10 to get the whole query including the bindings — Felix Geenen, Jul 02 '23 at 18:59

score 90 · Answer 1 · edited Jul 23 '23 at 22:14

90

Update (July 2023):

As of Laravel 10.15.0 you can use dumpRawSql() or ddRawSql()

DB::table('table')->whereIn('some_field', [1,2,3])->ddRawSql();

Original:

Check out the getBindings() method on the Builder class

getBindings()

$query = DB::table('table')->whereIn('some_field', [1,2,30]);

$sql = $query->toSql();

$bindings = $query->getBindings();

edited Jul 23 '23 at 22:14

Alan Reed

470
4
11

answered May 01 '17 at 15:32

Douglas.Sesar

4,214
3
29
36

122

To get SQL with Bindings `$sql_with_bindings = str_replace_array('?', $query->getBindings(), $query->toSql());` – Ennio Sousa Aug 01 '18 at 18:07
3

How do I favorite a comment? :D – Charles Wood May 28 '19 at 15:29
52

Note that `str_replace_array()` is deprecated. Instead use the Str utility class: `use Illuminate\Support\Str; Str::replaceArray('?', $query->getBindings(), $query->toSql())`. – Soulriser Jul 12 '19 at 17:13
7

@EnnioSousa, this can be quite dangerous because the bindings `getBindings` gives are unescaped. – Boris D. Teoharov Jul 23 '19 at 13:42
1

The propose is not make SQL query as output escaped and ready to be executed, if you're doing this, it is not recommended. The purpose is only get SQL with bindings to see raw query executed by Laravel. – Ennio Sousa Jul 23 '19 at 19:04
@EnnioSousa there is no `str_replace_array()` function defined. I am confused. – androidu Nov 05 '19 at 11:06
3

@EnnioSousa please look at Soulriser's comment above concerning the str_replace_array() function – Douglas.Sesar Nov 05 '19 at 18:08
2

@androidu str_replace_array us deorecated, use Illuminate\Support\Str; Str::replaceArray() instead. – Ennio Sousa Nov 06 '19 at 14:09

score 36 · Answer 2 · answered Dec 10 '19 at 03:34

36

Laravel now offers debugging directly on your Builder!!!

https://laravel.com/docs/queries#debugging

\App\User::where('age', '18')->dump();
\App\User::where('age', '18')->dd();

Outputs

"select * from `users` where `age` = ?"
[
    0 => "18"
]

answered Dec 10 '19 at 03:34

Andrew Brown

5,330
3
22
39

score 28 · Answer 3 · answered Nov 16 '18 at 11:56

28

public static function getQueries(Builder $builder)
{
    $addSlashes = str_replace('?', "'?'", $builder->toSql());
    return vsprintf(str_replace('?', '%s', $addSlashes), $builder->getBindings());
}

answered Nov 16 '18 at 11:56

Misha

389
3
4

2

this is what i was looking for – ManojKiran A Apr 03 '19 at 19:19
1

Turns out that the usage of '%s' will conflict with wildcards when using 'LIKE – Nickson Yap Jul 30 '19 at 18:15
2

Had the same issue. You can simply use `str_replace(['?', '%'], ["'?'", '%%'], $builder->toSql())`, that'll do the trick. – robbash Feb 06 '21 at 03:06

score 10 · Answer 4 · answered Dec 12 '19 at 12:46

You can define below code block as helper function and use wherever required. It will bind numeric as well as string value with quotations.

public static function getSqlWithBindings($query)
{
    return vsprintf(str_replace('?', '%s', $query->toSql()), collect($query->getBindings())->map(function ($binding) {
        return is_numeric($binding) ? $binding : "'{$binding}'";
    })->toArray());
}

Example:

$query = Document::where('model', 'contact')->where('model_id', '1');
dd(Document::getSqlWithBindings($query));

Output:

"select * from `document` where `model` = 'contact' and `model_id` = 1"

score 6 · Answer 5 · edited Apr 15 '22 at 21:33

6

Building upon Douglas.Sesar's answer.

I found I also needed to put the bindings in single quotations to be able to easily paste it into my database IDE.

$sql = $query->toSql();
$bindings = $query->getBindings();

$sql_with_bindings = preg_replace_callback('/\?/', function ($match) use ($sql, &$bindings) {
    return json_encode(array_shift($bindings));
}, $sql);

edited Apr 15 '22 at 21:33

amurrell

2,383
22
26

answered Sep 02 '20 at 10:03

lukio3

339
3
3

Hey I used this and I liked it - However, the quoting did not work well with FALSE - so I fixed it with `return json_encode(array_shift($bindings));` - I think I will make this edit. – amurrell Apr 15 '22 at 21:32

yaroslawww · Answer 6 · 2020-11-20T12:01:28.703

5

$sqlQuery = Str::replaceArray(
    '?',
    collect($query->getBindings())
        ->map(function ($i) {
            if (is_object($i)) {
                $i = (string)$i;
            }
            return (is_string($i)) ? "'$i'" : $i;
        })->all(),
    $query->toSql());

edited Nov 20 '20 at 12:01

answered Oct 29 '20 at 09:56

yaroslawww

978
9
12

Nickson Yap · Answer 7 · 2019-08-30T19:16:47.440

3

The following function ensures the resulting SQL doesn't confuse bindings with columns by enclosing the ? to be '?'

    public static function getFinalSql($query)
    {
        $sql_str = $query->toSql();
        $bindings = $query->getBindings();

        $wrapped_str = str_replace('?', "'?'", $sql_str);

        return str_replace_array('?', $bindings, $wrapped_str);
    }

edited Aug 30 '19 at 19:16

answered Jul 30 '19 at 18:18

Nickson Yap

1,146
15
23

It works but it does not escape the bindings, Could be problematic for some. – Adam Fowler Oct 22 '19 at 22:37
\Str::replaceArray use instead str_replace_array – Soyab Badi Aug 05 '22 at 10:43

score 2 · Answer 8 · edited Jan 27 '19 at 19:43

2

If you want to get an executed query including bindings from the query log:

\DB::enableQueryLog();
\DB::table('table')->get();
dd(str_replace_array('?', \DB::getQueryLog()[0]['bindings'], 
      \DB::getQueryLog()[0]['query']));

edited Jan 27 '19 at 19:43

Marcin Orlowski

72,056
11
123
141

answered Jan 27 '19 at 19:38

André

2,042
1
23
26

One can loop DB::getQueryLog() to get all queries with bindings op to that point! Thanks for this. – Ogier Schelvis Jun 26 '19 at 07:56
Very important! If you use a connection which is different from the default one, you must specify it using `DB::connection('myConn')->enableQueryLog();`. – guyaloni Aug 26 '19 at 12:04
1

Function `str_replace_array` is deprecated. You can use instead Str class utility: `\Str::replaceArray` – guyaloni Sep 23 '21 at 16:33

Leif · Answer 9 · 2023-01-05T08:31:52.567

Since the other answers do not properly quote the expressions, here is my approach. It uses the escaping function that belongs to the current database connection.

It replaces the question marks one by one with the corresponding binding, which is retrieved from $bindings via array_shift(), consuming the array in the process. Note, that $bindings has to be passed by reference for this to work.

function getSql($query)
{
        $bindings = $query->getBindings();

        return preg_replace_callback('/\?/', function ($match) use (&$bindings, $query) {
            return $query->getConnection()->getPdo()->quote(array_shift($bindings));
        }, $query->toSql());
}

guyaloni · Answer 10 · 2019-08-14T09:08:31.920

I created this function. It is partial, might be parameters which are not covered, for me it was enough.
More than welcomed to add your improvements in a comment!

function getFullSql($query) {
  $sqlStr = $query->toSql();
  foreach ($query->getBindings() as $iter=>$binding) {

    $type = gettype($binding);
    switch ($type) {
      case "integer":
      case "double":
        $bindingStr = "$binding";
        break;
      case "string":
        $bindingStr = "'$binding'";
        break;
      case "object":
        $class = get_class($binding);
        switch ($class) {
          case "DateTime":
            $bindingStr = "'" . $binding->format('Y-m-d H:i:s') . "'";
            break;
          default:
            throw new \Exception("Unexpected binding argument class ($class)");
        }
        break;
      default:
        throw new \Exception("Unexpected binding argument type ($type)");
    }

    $currentPos = strpos($sqlStr, '?');
    if ($currentPos === false) {
      throw new \Exception("Cannot find binding location in Sql String for bundung parameter $binding ($iter)");
    }

    $sqlStr = substr($sqlStr, 0, $currentPos) . $bindingStr . substr($sqlStr, $currentPos + 1);
  }

  $search = ["select", "distinct", "from", "where", "and", "order by", "asc", "desc", "inner join", "join"];
  $replace = ["SELECT", "DISTINCT", "\n  FROM", "\n    WHERE", "\n    AND", "\n    ORDER BY", "ASC", "DESC", "\n  INNER JOIN", "\n  JOIN"];
  $sqlStr = str_replace($search, $replace, $sqlStr);

  return $sqlStr;
}

+1 This is the only thing that makes sense. I just don't get the last part, the problem I see there is that if I would have a column called `selected` in some table, it would say `SELECTed` after the conversion. Maybe it should be flagged with a `$pretify` parameter default to `false`, because in production I don't care how it looks ;) — dbf, Oct 27 '20 at 21:51
Oh and I would add `case "boolean":` to `integer` and `double`. Natively integer 1 and boolean true are the same as in false and 0. — dbf, Oct 27 '20 at 21:54

score 2 · Answer 11 · answered Apr 14 '20 at 09:40

2

You can do something like this:

$escapedBindings = array();

foreach($query->getBindings() as $item) {$escapedBindings[] = '"'.$item.'"';}

$sql_with_bindings = Str::replaceArray('?', $escapedBindings, $query->toSql());

answered Apr 14 '20 at 09:40

mwafi

3,946
8
56
83

score 2 · Answer 12 · answered Jul 09 '20 at 22:56

This is a very old question (2015), but since this is the first Google result I got I think it's worth to give my solution as well, in case it's useful for the next person.

Eloquent (5.7 onwards I think, I haven't tested more recent or earlier versions) has a method to change a Builder's from to wrap a subquery:

# Taken from Illuminate/Database/Query/Builder.php - Line 272
public function fromSub($query, $as) {
    [$query, $bindings] = $this->createSub($query);

    return $this->fromRaw('('.$query.') as '.$this->grammar->wrapTable($as), $bindings);
}

This however requires an already existing instance of \Illuminate\Database\Query\Builder. In order to make an empty one, you can do:

use Illuminate\Database\Capsule\Manager as DB;

$fancy = DB::table("videogames")->where("uses_sdl2", 1);
$empty = DB::table(null);

# Wrap the fancy query and set it as the "from" clause for the empty one
# NOTE: the alias is required
$empty = $empty->fromSub($fancy, "performant_games");

This will warranty that bindings are treated correctly, since they'll be handled by Eloquent itself.

score 2 · Answer 13 · answered Jul 03 '23 at 11:46

Raw Query Output With Bindings is Coming to Laravel 10 on the next tagged v10.x release (as of 7/3/2023).

Example:

User::where('email', 'foo@example.com')->toRawSql();

Which will then dump the following results:

"SELECT * FROM users WHERE email = 'foo@example.com'"

score 1 · Answer 14 · answered Sep 23 '21 at 16:36

Simple and elegant solution:

foreach (DB::getQueryLog() as $q) {
    $queryStr = \Str::replaceArray('?', $q['bindings'], $q['query']);
    echo $queryStr . ";\n";
}

(if you use a non-default connection, use DB::connection('yourConn')->getQueryLog() in the foreach command).

score 0 · Answer 15 · answered Mar 13 '20 at 00:05

Output to the log all queries with inserted bindings sorted from the slowest query to the fastest:

    \DB::enableQueryLog();

    // Put here your queries 
    $query = DB::table('table')->whereIn('some_field', [1,2,30]); 
    $query2 = DB::table('table2')->where('some_field', '=', 10); 


    $logQueries = \DB::getQueryLog();
    usort($logQueries, function($a, $b) {
        return $b['time'] <=> $a['time'];
    });

    foreach ($logQueries as $item) {
        \Log::info(str_replace_array('?', $item['bindings'], $item['query']));
        \Log::info($item['time']. ' ms');
    }

score 0 · Answer 16 · answered May 29 '23 at 06:29

You can add the following code snippet to the boot() method in your AppServiceProvider.php file:

use Illuminate\Database\Query\Builder;

public function boot()
{
    /// ......
    
    Builder::macro('sql', function () {
        $query = $this;
        $bindings = $query->getBindings();

        return preg_replace_callback('/\?/', function ($match) use (&$bindings, $query) {
            return $query->getConnection()->getPdo()->quote(array_shift($bindings));
        }, $query->toSql());
    });
}

After adding the code to the boot() method, you can utilize it in any query as follows:

// Example usage:
Model::sql();

By following these steps, you'll be able to incorporate the provided code snippet into your AppServiceProvider file and utilize the sql() method in your queries.

score 0 · Answer 17 · answered Jul 02 '23 at 19:04

A neat solution in Laravel 10 and later is to use toRawSql() instead of toSql() which prints the complete query including the bindings.

$query = DB::table('table')->whereIn('some_field', [1,2,30])->toRawSql();

Model::join(DB::raw("({$query}) as table"), function($join) {
    $join->on('model.id', '=', 'table.id');
})

score 0 · Answer 18 · answered Jul 03 '23 at 08:57

My generic solution for DB debugging is this:

DB::listen(function (QueryExecuted $query) {
  $sql_with_bindings = Str::replaceArray('?', $query->bindings, $query->sql);
  Log::info(sprintf("SQL WITH DATA: %s", $sql_with_bindings));
});

Depending on you situation, you might consider adding a:

if (config('app.debug') == true) {
}

around this if you want to avoid debug logs in production...

score 0 · Answer 19 · answered Jul 20 '23 at 10:58

0

Laravel 10 can now return raw queries with bindings!

User::where('email', 'foo@example.com')->toRawSql();

answered Jul 20 '23 at 10:58

Farzan Badakhshan

373
1
5
19

score -3 · Answer 20 · answered Nov 23 '17 at 02:48

It is all explained here..... https://ajcastro29.blogspot.com/2017/11/laravel-join-derived-tables-properly.html

I created a scope query for that thing. I think it can also be in macros..

public function scopeJoinDerived($query, $derivedQuery, $table, $one, $operator = null, $two = null, $type = 'inner', $where = false)
{
    $query->join(DB::raw("({$derivedQuery->toSql()}) as `{$table}`"), $one, $operator, $two, $type, $where);
    $join = last($query->getQuery()->joins);
    $join->bindings =  array_merge($derivedQuery->getBindings(), $join->bindings);

    return $query;
}

Laravel how to get query with bindings?

20 Answers20

Linked