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

laravel raw query
laravel raw query with parameters
laravel get query string
laravel tosql
laravel raw update query
laravel print query with values
laravel db::query
laravel query log

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?

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

How can I get the raw query string from Laravel's , You can get it doing: $query = DB::table('brands') ->join('products','a','=','c') ->​whereNull('whatever'); echo $query->toSql();. But Laravel will not show you  Laravel Query Builder is the best way to connect your laravel project to the SQL database. We are going to see how to quickly get the RAW SQL query from the very clean Eloquent query. So, as explained above, we have a quick Laravel Query Builder or Eloquent statement as follows, DB::table('users');

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

Found this snip at https://gist.github.com/thonyx/c061d56dc620284ab22526294b43518a, so all credits to https://gist.github.com/thonyx :)

How to retrieve raw url query string, Is there a method in Laravel $request that retrieves raw url query string to return data like this: par1=val1&par2=val2&par3=val3&  Sometimes you may need to use a raw expression in a query. To create a raw expression, you may use the DB::raw method: $users = DB::table ('users') ->select (DB::raw ('count (*) as user_count, status')) ->where ('status', '<>', 1) ->groupBy ('status') ->get ();

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

Raw Queries in Laravel, How to use raw queries safely. Laravel's query builder has the tools we need to safely run such queries. to see if it has any way to bind our parameters: The most concise screencasts for the working developer, updated daily. There's no shortage of content at Laracasts. In fact, you could watch nonstop for days upon days, and still not see everything!

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

Database: Query Builder - Laravel, Note: The Laravel query builder uses PDO parameter binding throughout to protect your application against SQL injection  How can I use Laravel Blade to parse raw strings/data and use the output? For instance, when I try the following code I get the following exception:

Query Builder - Laravel, laravel raw query with parameters laravel get sql query with parameters laravel tosql convert laravel query to sql online simple select query in laravel laravel  I want to get a total of books by their Names from two tables one is a books table and the second is a stocks table using raw SQL in Laravel I don't know how to do it. In MySQL, we can get it by using the below query. select b.name,sum(s.quantity)as Total from stocks s JOIN books b on s.book_id = b.id GROUP by b.name

How can I get the raw query string from Laravel's query builder , How to bind parameters to DB::raw query in Laravel 5. I tried various way but I always get the following error: SQLSTATE[HY000]: General error: 2031 (SQL:  Manual Order laravel-datatables package documentation. *, (SELECT count(*) FROM user_followers WHERE user_followers. As I will cover this Post with live Working example to develop laravel 6 add query string to current url, so the get all query string parameters laravel 6 is used for this example is following below.

How to bind parameters to DB::raw query in Laravel 5 : laravel, If you need to execute some SQL query, without processing any results, like INSERT or UPDATE without any parameters, you can use DB::  Yes. Whether you are a beginner and want to learn how Laravel Eloquent or Query Builder work under the hood or you simply need to generate an SQL for some other purpose, you can definitely go and extract that SQL query. The way to do that is quick and easy: To output to the screen the last queries ran you can use this:

Comments
  • that returns the query with the placeholders instead of the actual parameters
  • to use getBinings() do i need to use any name space or something? @MatteoRiva
  • 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?
  • Turns out I had toSQL instead of toSql, which gave me a completely different result
  • This works for me. Much better than the accepted answer above.
  • I get a vsprintf too few args
  • this is what is was looking for