CODEWITHSUNDEEP
phplaraveleloquent
Mateusz Nowak
Mateusz Nowak

Reputation: 4121

Laravel how to get query with bindings?

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?

Upvotes: 84

Views: 140612

Answers (21)

Abdul Moiz
Abdul Moiz

Reputation: 502

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

Upvotes: 1

Douglas.Sesar
Douglas.Sesar

Reputation: 4430

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

Upvotes: 102

Farzan Badakhshan
Farzan Badakhshan

Reputation: 403

Laravel 10 can now return raw queries with bindings!

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

Upvotes: 1

Douglas.Sesar
Douglas.Sesar

Reputation: 4430

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]'"

Upvotes: 3

The Schwartz
The Schwartz

Reputation: 767

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

Upvotes: 0

Felix Geenen
Felix Geenen

Reputation: 2707

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

Upvotes: 0

Govar
Govar

Reputation: 124

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.

Upvotes: 0

Leif
Leif

Reputation: 2170

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

Upvotes: 2

lukio3
lukio3

Reputation: 349

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

Upvotes: 6

guyaloni
guyaloni

Reputation: 5882

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

Upvotes: 1

yaroslawww
yaroslawww

Reputation: 1088

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

Upvotes: 5

Miguel Amatón
Miguel Amatón

Reputation: 21

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.

Upvotes: 2

Ted
Ted

Reputation: 4166

You can do something like this:

$escapedBindings = array();

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

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

Upvotes: 2

user9893392
user9893392

Reputation: 11

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

Upvotes: 0

Urja Satodiya
Urja Satodiya

Reputation: 412

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"

Upvotes: 10

Andrew Brown
Andrew Brown

Reputation: 5424

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"
]

Upvotes: 36

Nickson Yap
Nickson Yap

Reputation: 1276

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

Upvotes: 3

guyaloni
guyaloni

Reputation: 5882

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

Upvotes: 2

Andr&#233;
Andr&#233;

Reputation: 2142

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

Upvotes: 2

Misha
Misha

Reputation: 419

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

Upvotes: 31

Arjon Jason Castro
Arjon Jason Castro

Reputation: 238

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

Upvotes: -3

Related Questions