How can I get the raw query string from Laravel's query builder BEFORE executing the query?

Question

I have a complex query created by a few conditions, and I would like to get the final SQL query from the builder object is about to execute. Can I do that?

Antonio Carlos Ribeiro · Accepted Answer · 2013-11-18 10:52:12Z

88

You can get it doing:

$query = DB::table('brands')
                ->join('products','a','=','c')
                ->whereNull('whatever');

echo $query->toSql();

But Laravel will not show you parameters in your query, because they are bound after preparation of the query.

So you can also do:

print_r( $query->getBindings() );

edited Nov 18, 2013 at 10:52

answered Nov 18, 2013 at 10:46

Antonio Carlos Ribeiro

87.9k22 gold badges220 silver badges205 bronze badges

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

4 Comments

Matteo Riva Over a year ago

that returns the query with the placeholders instead of the actual parameters

incorporeal Over a year ago

to use getBinings() do i need to use any name space or something? @MatteoRiva

dansch Over a year ago

Object of class Illuminate\Database\Eloquent\Builder could not be converted to string That's what I get when trying to to toSQL from a model's query. It gives me a Builder instance. How do I get the sql from a builder instance?

dansch Over a year ago

Turns out I had toSQL instead of toSql, which gave me a completely different result

andi79h · Accepted Answer · 2020-06-19 01:17:11Z

73

For debugging this might come quite handy as it returns the SQL with the bindings, so you can instantly put it into the database console.

/**
 * Combines SQL and its bindings
 *
 * @param \Eloquent $query
 * @return string
 */
public static function getEloquentSqlWithBindings($query)
{
    return vsprintf(str_replace('?', '%s', $query->toSql()), collect($query->getBindings())->map(function ($binding) {
        return is_numeric($binding) ? $binding : "'{$binding}'";
    })->toArray());
}

edited Jun 19, 2020 at 1:17

answered Aug 10, 2018 at 0:36

andi79h

1,1771 gold badge14 silver badges18 bronze badges

4 Comments

Felix Jr Over a year ago

This works for me. Much better than the accepted answer above.

zardilior Over a year ago

I get a vsprintf too few args

ManojKiran Over a year ago

this is what is was looking for

Carmageddon Over a year ago

Thanks @andi79h! Where would you place it for easy accessibility from any class inheriting Model? since Model itself is in 3rd party library..

epicgear · Accepted Answer · 2019-10-23 00:30:04Z

Piggy back off andi79h's answer. The function works well, except it's assuming binding would not have any quotes that could break the query.

I added "addslashes" to $bindings to make it a bit safer. Although, ideally, it should be run through mysqli_real_escape_string() if you have an active connection to work with. https://www.php.net/manual/en/mysqli.real-escape-string.php

/**
 * Combines SQL and its bindings
 *
 * @param \Eloquent $query
 * @return string
 */
public static function getEloquentSqlWithBindings($query)
{
    return vsprintf(str_replace('?', '%s', $query->toSql()), collect($query->getBindings())->map(function ($binding) {
        $binding = addslashes($binding);
        return is_numeric($binding) ? $binding : "'{$binding}'";
    })->toArray());
}

Henry Ávila · Accepted Answer · 2023-09-28 10:48:40Z

8

I know it's a old question, but nowdays, you can get the Sql with all parameters using ->toRawSql()

answered Sep 28, 2023 at 10:48

Henry Ávila

5225 silver badges12 bronze badges

1 Comment

Burgi May 29 at 13:49

This should be the accepted answer!

albertski · Accepted Answer · 2021-04-13 21:55:32Z

4

Updating @andi79h's answer to handle cases where you have % in the query. (For example, DATE_FORMAT(report_date, '%c/%e/%Y') would cause a too few arguments error.

/**
 * Combines SQL and its bindings
 *
 * @param \Eloquent $query
 * @return string
 */
public static function getEloquentSqlWithBindings($query)
{
        return vsprintf(str_replace('?', '%s', str_replace('%', '%%', $query->toSql())), collect($query->getBindings())->map(function ($binding) {
            return is_numeric($binding) ? $binding : "'{$binding}'";
        })->toArray());
}

answered Apr 13, 2021 at 21:55

albertski

2,6621 gold badge28 silver badges54 bronze badges

Comments

Felix Eve · Accepted Answer · 2019-07-03 08:32:31Z

I wanted line breaks and uppercase mysql keywords in my queries so added to andi79h's answer. Not particuarly elegant and I'm sure there are lots of edge cases it won't work with but I still find it useful...

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

    $double_linebreak_words = ['(', ')'];
    $double_linebreak_words_replace = array_map(function($str){ return PHP_EOL . $str . PHP_EOL; }, $double_linebreak_words);
    $query = str_replace($double_linebreak_words, $double_linebreak_words_replace, $query);

    $mysql_keywords = ['ADD', 'ALL', 'ALTER', 'ANALYZE', 'AND', 'AS', 'ASC', 'AUTO_INCREMENT', 'BDB', 'BERKELEYDB', 'BETWEEN', 'BIGINT', 'BINARY', 'BLOB', 'BOTH', 'BTREE', 'BY', 'CASCADE', 'CASE', 'CHANGE', 'CHAR', 'CHARACTER', 'CHECK', 'COLLATE', 'COLUMN', 'COLUMNS', 'CONSTRAINT', 'CREATE', 'CROSS', 'CURRENT_DATE', 'CURRENT_TIME', 'CURRENT_TIMESTAMP', 'DATABASE', 'DATABASES', 'DAY_HOUR', 'DAY_MINUTE', 'DAY_SECOND', 'DEC', 'DECIMAL', 'DEFAULT', 'DELAYED', 'DELETE', 'DESC', 'DESCRIBE', 'DISTINCT', 'DISTINCTROW', 'DIV', 'DOUBLE', 'DROP', 'ELSE', 'ENCLOSED', 'ERRORS', 'ESCAPED', 'EXISTS', 'EXPLAIN', 'FALSE', 'FIELDS', 'FLOAT', 'FOR', 'FORCE', 'FOREIGN', 'FROM', 'FULLTEXT', 'FUNCTION', 'GEOMETRY', 'GRANT', 'GROUP', 'HASH', 'HAVING', 'HELP', 'HIGH_PRIORITY', 'HOUR_MINUTE', 'HOUR_SECOND', 'IF', 'IGNORE', 'INDEX', 'INFILE', 'INNER', 'INNODB', 'INSERT', 'INTEGER', 'INTERVAL', 'INTO', 'JOIN', 'KEY', 'KEYS', 'KILL', 'LEADING', 'LEFT', 'LIKE', 'LIMIT', 'LINES', 'LOAD', 'LOCALTIME', 'LOCALTIMESTAMP', 'LOCK', 'LONG', 'LONGBLOB', 'LONGTEXT', 'LOW_PRIORITY', 'MASTER_SERVER_ID', 'MATCH', 'MEDIUMBLOB', 'MEDIUMINT', 'MEDIUMTEXT', 'MIDDLEINT', 'MINUTE_SECOND', 'MOD', 'MRG_MYISAM', 'NATURAL', 'NOT', 'NULL', 'NUMERIC', 'ON', 'OPTIMIZE', 'OPTION', 'OPTIONALLY', 'ORDER', 'OUTER', 'OUTFILE', 'PRECISION', 'PRIMARY', 'PRIVILEGES', 'PROCEDURE', 'PURGE', 'READ', 'REAL', 'REFERENCES', 'REGEXP', 'RENAME', 'REPLACE', 'REQUIRE', 'RESTRICT', 'RETURNS', 'REVOKE', 'RIGHT', 'RLIKE', 'RTREE', 'SELECT', 'SET', 'SHOW', 'SMALLINT', 'SOME', 'SONAME', 'SPATIAL', 'SQL_BIG_RESULT', 'SQL_CALC_FOUND_ROWS', 'SQL_SMALL_RESULT', 'SSL', 'STARTING', 'STRAIGHT_JOIN', 'STRIPED', 'TABLE', 'TABLES', 'TERMINATED', 'THEN', 'TINYBLOB', 'TINYINT', 'TINYTEXT', 'TO', 'TRAILING', 'TRUE', 'TYPES', 'UNION', 'UNIQUE', 'UNLOCK', 'UNSIGNED', 'UPDATE', 'USAGE', 'USE', 'USER_RESOURCES', 'USING', 'VALUES', 'VARBINARY', 'VARCHAR', 'VARCHARACTER', 'VARYING', 'WARNINGS', 'WHEN', 'WHERE', 'WITH', 'WRITE', 'XOR', 'YEAR_MONTH', 'ZEROFILL', 'INT', 'OR', 'IS', 'IN'];
    $mysql_keywords = array_map(function($str){ return " $str "; }, $mysql_keywords);
    $mysql_keywords_lc = array_map(function($str){ return strtolower($str); }, $mysql_keywords);
    $query = str_replace($mysql_keywords_lc, $mysql_keywords, $query);

    $linebreak_before_words = ['INNER JOIN', 'LEFT JOIN', 'OUTER JOIN', 'RIGHT JOIN', 'WHERE', 'FROM', 'GROUP BY', 'SELECT'];
    $linebreak_before_words_replace = array_map(function($str){ return PHP_EOL . $str; }, $linebreak_before_words);
    $query = str_replace($linebreak_before_words, $linebreak_before_words_replace, $query);

    $linebreak_after_words = [','];
    $linebreak_after_words_replace = array_map(function($str){ return $str . PHP_EOL; }, $linebreak_after_words);
    $query = str_replace($linebreak_after_words, $linebreak_after_words_replace, $query);

    $query = str_replace('select ', 'SELECT ', $query);

    return $query;
}

Simion · Accepted Answer · 2024-06-16 07:52:13Z

0

Since Laravel 10 we can use ddRawSql() and dumpRawSql().

See: https://laravel.com/docs/10.x/queries#debugging

// Example from docs
DB::table('users')->where('votes', '>', 100)->dumpRawSql();
 
DB::table('users')->where('votes', '>', 100)->ddRawSql();

answered Jun 16, 2024 at 7:52

Simion

3514 silver badges8 bronze badges

Collectives™ on Stack Overflow

How can I get the raw query string from Laravel's query builder BEFORE executing the query?

7 Answers 7

4 Comments

4 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

4 Comments

4 Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related