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
– Marwelln, Commented Dec 5, 2014 at 11:10
You can use toRawSql() with Laravel 10 to get the whole query including the bindings — Felix Dransfeld
– Felix Dransfeld, Commented Jul 2, 2023 at 18:59

Alan Reed · Accepted Answer · 2023-07-23 22:14:08Z

105

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, 2023 at 22:14

Alan Reed

5234 silver badges14 bronze badges

answered May 1, 2017 at 15:32

Douglas.Sesar

4,4705 gold badges31 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ennio Sousa Over a year ago

To get SQL with Bindings $sql_with_bindings = str_replace_array('?', $query->getBindings(), $query->toSql());

Soulriser Over a year ago

Note that str_replace_array() is deprecated. Instead use the Str utility class: use Illuminate\Support\Str; Str::replaceArray('?', $query->getBindings(), $query->toSql()).

Boris D. Teoharov Over a year ago

@EnnioSousa, this can be quite dangerous because the bindings getBindings gives are unescaped.

Douglas.Sesar Over a year ago

@EnnioSousa please look at Soulriser's comment above concerning the str_replace_array() function

Ennio Sousa Over a year ago

@androidu str_replace_array us deorecated, use Illuminate\Support\Str; Str::replaceArray() instead.

|

Andrew Brown · Accepted Answer · 2019-12-10 03:34:34Z

38

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, 2019 at 3:34

Andrew Brown

5,4443 gold badges24 silver badges40 bronze badges

Comments

Misha · Accepted Answer · 2018-11-16 11:56:12Z

32

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

answered Nov 16, 2018 at 11:56

Misha

4294 silver badges4 bronze badges

3 Comments

ManojKiran Over a year ago

this is what i was looking for

Nickson Yap Over a year ago

Turns out that the usage of '%s' will conflict with wildcards when using 'LIKE

robbash Over a year ago

Had the same issue. You can simply use str_replace(['?', '%'], ["'?'", '%%'], $builder->toSql()), that'll do the trick.

Urja Satodiya · Accepted Answer · 2019-12-12 12:46:26Z

10

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"

answered Dec 12, 2019 at 12:46

Urja Satodiya

4125 silver badges11 bronze badges

Comments

amurrell · Accepted Answer · 2022-04-15 21:33:23Z

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, 2022 at 21:33

amurrell

2,4751 gold badge24 silver badges27 bronze badges

answered Sep 2, 2020 at 10:03

lukio3

3493 silver badges3 bronze badges

1 Comment

amurrell Over a year ago

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.

yaroslawww · Accepted Answer · 2020-11-20 12:01:28Z

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, 2020 at 12:01

answered Oct 29, 2020 at 9:56

yaroslawww

1,08810 silver badges13 bronze badges

Comments

Nickson Yap · Accepted Answer · 2019-08-30 19:16:47Z

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, 2019 at 19:16

answered Jul 30, 2019 at 18:18

Nickson Yap

1,28617 silver badges25 bronze badges

2 Comments

Adam Fowler Over a year ago

It works but it does not escape the bindings, Could be problematic for some.

Soyab Badi Over a year ago

\Str::replaceArray use instead str_replace_array

Douglas.Sesar · Accepted Answer · 2023-07-03 11:46:53Z

3

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', '[email protected]')->toRawSql();

Which will then dump the following results:

"SELECT * FROM users WHERE email = '[email protected]'"

answered Jul 3, 2023 at 11:46

Douglas.Sesar

4,4705 gold badges31 silver badges38 bronze badges

Comments

Marcin Orlowski · Accepted Answer · 2019-01-27 19:43:28Z

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, 2019 at 19:43

Marcin Orlowski

75.7k11 gold badges128 silver badges152 bronze badges

answered Jan 27, 2019 at 19:38

André

2,1421 gold badge24 silver badges27 bronze badges

3 Comments

Ogier Schelvis Over a year ago

One can loop DB::getQueryLog() to get all queries with bindings op to that point! Thanks for this.

guyaloni Over a year ago

Very important! If you use a connection which is different from the default one, you must specify it using DB::connection('myConn')->enableQueryLog();.

guyaloni Over a year ago

Function str_replace_array is deprecated. You can use instead Str class utility: \Str::replaceArray

guyaloni · Accepted Answer · 2019-08-14 09:08:31Z

2

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;
}

edited Aug 14, 2019 at 9:08

answered Aug 12, 2019 at 11:47

guyaloni

6,0206 gold badges63 silver badges105 bronze badges

2 Comments

dbf Over a year ago

+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 Over a year ago

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.

Ted · Accepted Answer · 2020-04-14 09:40:32Z

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, 2020 at 9:40

Ted

4,1868 gold badges63 silver badges88 bronze badges

Comments

Miguel Amatón · Accepted Answer · 2020-07-09 22:56:36Z

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.

Leif · Accepted Answer · 2023-01-05 08:31:52Z

2

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

edited Jan 5, 2023 at 8:31

answered Aug 5, 2019 at 9:29

Leif

2,1702 gold badges15 silver badges26 bronze badges

Comments

guyaloni · Accepted Answer · 2021-09-23 16:36:41Z

1

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).

answered Sep 23, 2021 at 16:36

guyaloni

6,0206 gold badges63 silver badges105 bronze badges

Comments

Farzan Badakhshan · Accepted Answer · 2023-07-20 10:58:09Z

1

Laravel 10 can now return raw queries with bindings!

User::where('email', '[email protected]')->toRawSql();

answered Jul 20, 2023 at 10:58

Farzan Badakhshan

4231 gold badge7 silver badges22 bronze badges

Comments

Abdul Moiz · Accepted Answer · 2023-10-29 08:09:40Z

1

I have a little hack to view the actual running query

User::where('id', 123)->first()

just change the column name to break the query and you will see the complete query in the error

User::where('ida', 123)->first()

It will break the query and throw an error screen 🤣 you can see which query is running their

Note: only for local development.

fun fact you can do this in every framework

answered Oct 29, 2023 at 8:09

Abdul Moiz

5002 silver badges12 bronze badges

Comments

user9893392 · Accepted Answer · 2020-03-13 00:05:07Z

0

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');
    }

answered Mar 13, 2020 at 0:05

user9893392

11

Comments

Govar · Accepted Answer · 2023-05-29 06:29:36Z

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.

Felix Dransfeld · Accepted Answer · 2023-07-02 19:04:02Z

0

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');
})

answered Jul 2, 2023 at 19:04

Felix Dransfeld

2,7371 gold badge31 silver badges41 bronze badges

Comments

The Schwartz · Accepted Answer · 2023-07-03 08:57:49Z

0

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...

answered Jul 3, 2023 at 8:57

The Schwartz

7771 gold badge8 silver badges13 bronze badges

Comments

Arjon Jason Castro · Accepted Answer · 2017-11-23 02:48:27Z

-3

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;
}

answered Nov 23, 2017 at 2:48

Arjon Jason Castro

2381 gold badge5 silver badges7 bronze badges

Collectives™ on Stack Overflow

Laravel how to get query with bindings?

21 Answers 21

7 Comments

Comments

3 Comments

Comments

1 Comment

Comments

2 Comments

Comments

3 Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

21 Answers 21

7 Comments

Comments

3 Comments

Comments

1 Comment

Comments

2 Comments

Comments

3 Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related