How do I get the query builder to output its raw SQL query with inline params

Question

Given the following code:

DB::table('users')->where( 'gender' , '=', 'male', true)->toSql();

I get the raw SQL query string but it used params.
```
select * from `users` where `gender`=?
```
Can I somehow inline parameters?
Can i say to builder to not use backquotes? now I use DB::raw('gender') expressions, but its not what i whant to use.

Possible duplicate of [Getting raw SQL query string from PDO prepared statements](https://stackoverflow.com/questions/210564/getting-raw-sql-query-string-from-pdo-prepared-statements) — Leon Husmann, Oct 04 '18 at 14:05

Peter · Answer 1 · 2018-10-04T18:21:06.047

You are looking for the getBindings() method to get the actual parameters. So something like this should work:

$completeSql = str_replace_array('?', $query->getBindings(), $query->toSql());

https://laravel.com/docs/5.7/helpers#method-str-replace-array for info on the str_replace_array() helper which works well here.

And I think DB::raw() is the way to avoid the backticks without extending stuff.

The backticks are added in the MySqlGrammar wrapValue() method. You could extend that class and then override the parts you don't like. Then you should be able to use

DB::connection()->setSchemaGrammar(new YourExtendedMySqlGrammar());

https://laravel.com/docs/5.7/queries#raw-expressions for info on raw expressions might be of use too.

score 0 · Answer 2 · answered Oct 04 '18 at 14:06

0

echo sprintf(str_replace('?', '%s',DB::table('users')->where( 'gender' , '=', 'male', true)->toSql()), 'male');

alternatively you could turn on query logging in your DB and look at them in there

answered Oct 04 '18 at 14:06

noid

106
6

Auch, you kidding? what if 20 params? – Alexander Popov Oct 04 '18 at 15:23
then use my suggested alternative ;) – noid Oct 04 '18 at 15:34

score 0 · Answer 3 · answered Oct 04 '18 at 15:08

0

Laravel Debugbar does this for you out of the box with little to no code required. All queries and their parameters are shown in the query tab and can be copy/pasted into whatever SQL client you choose.

https://github.com/barryvdh/laravel-debugbar

answered Oct 04 '18 at 15:08

Chris Ruskai

359
1
2
14

I not need it for debug, I use it in Athena which not works with params, only complete sql – Alexander Popov Oct 04 '18 at 15:22

score 0 · Answer 4 · answered Oct 04 '18 at 15:57

If this isn't for debug, you could use getBindings().

$q = \DB::table('contact')->where('created_at', '2018-01-01');
$sql = $q->toSql();
$bind = $q->getBindings();
$inline = vsprintf(str_replace("?", "'%s'", $sql), $bind);
echo $inline;

Obviously, this only works for single valued types such as int, date, string, etc. If you have arrays, you will need to convert $bind so its values are only strings.

score 0 · Answer 5 · answered Oct 04 '18 at 16:46

Some time ago, I wanted a debugging tool that could present full, easy to read, copy/pastable SQL. At the time I was using it to expose N+1 lazy-loads that were hurting performance. So I actually dissected DebugBar to figure out how they managed it, and came up with something that sounds like it'd suit your purposes as well.

This basically just reformats what Laravel catches in the query log. (Of course they actually have to finish executing to be visible here.) Just make sure, if you're using any of the raw methods, to encapsulated field names in back-ticks, or the capitalization will look a little screwy.

DB::enableQueryLog();
DB::flushQueryLog();

//Execute whatever code results in SQL queries

$queries = DB::getQueryLog();
DB::disableQueryLog();
DB::flushQueryLog();

$dbName = null;
$compiled = [];
foreach ($queries as $query) {
    if (DB::getDatabaseName() != $useDb) {
        $dbName = DB::getDatabaseName();
        $compiled[] = "USE `$dbName`;";
    }

    $sql = $query['query'];
    $values = $query['bindings'];

    $enclosures = [
        'back_tick' => '`',
        'apostrophe' => "'"
    ];

    $matches = [];
    foreach ($enclosures as $name => $enclosure) {
        $matches[$name] = [];
        preg_match_all("/$enclosure.*?$enclosure/", $sql, $matches[$name]);
        $matches[$name] = array_last($matches[$name]);
        $sql = preg_replace("/$enclosure.*?$enclosure/", "$enclosure?$enclosure", $sql);
    }

    $sql = strtoupper($sql);

    foreach ($enclosures as $name => $enclosure) {
        $sql = str_replace_array("$enclosure?$enclosure", $matches[$name], $sql);
    }

    $values = array_map(function ($value) {
        if (!is_numeric($value) && !is_null($value)) {
            $value = DB::connection()->getPdo()->quote($value);
        }
        return $value;
    }, $values);

    $sql = str_replace_array('?', $values, $sql);
    $sql = rtrim($sql, ';').';';

    $compiled[] = $sql;
}

By the way, the DB::getQueryLog() also includes the time it takes for a query to run, making it even more useful for diagnosing performance issues. Just bear in mind there, connection-time is a factor. Locally hosting your app that's remoting into a database will take longer to come back than when your project is actually live.

How do I get the query builder to output its raw SQL query with inline params

5 Answers5