Laravel 5.X: How Do I Get Raw SQL Query As String With Binders From Query Builder | Query Builder To Raw SQL

Laravel 5.X: How Do I Get Raw SQL Query As String With Binders From Query Builder. Its very easy and now few steps from here. It's interesting.

use Illuminate\Support\Facades\DB;

$model = DB::table('employee')
    ->where("employee.id", "<>", 0)
    ->whereIn("employee.id", array(1, 2))
    ->where("employee.type", "PERMANENT")
    ->where(function ($q) {
        $q->where("employee.id", 1)->orWhere("employee.id", 2);
    })
    ->limit(10)->offset(0)
    ->groupBy("employee.id")
    ->select("employee.id");

$replace = function ($sql, $bindings) {
    $pos = 0;
    $needle = '?';
    foreach ($bindings as $replace) {
        if (is_string($replace)) {
            $replace = DB::connection()->getPdo()->quote($replace);
        }
        $pos = strpos($sql, $needle, $pos);
        if ($pos !== false) {
            $sql = substr_replace($sql, $replace, $pos, strlen($needle));
            $pos = $pos + strlen($replace);
        }
    }
    return $sql;
};

$sql = $replace($model->toSql(), $model->getBindings());
echo "SQL=" . $sql;

And output would be like below:

SELECT `employee`.`id`
FROM `employee`
WHERE `employee`.`id` <> 0 AND `employee`.`id` IN (1, 2) AND
      `employee`.`type` = 'PERMANENT' AND (`employee`.`id` = 1 OR `employee`.`id` = 2)
GROUP BY `employee`.`id`
LIMIT 10 OFFSET 0

How to get the query executed in Laravel

As a developer you must face some situation where you have to need the sql executed. In Laravel you can see your sql query generated each step. Below is a simple code snippet to output the sql query generated by Laravel query generator. Also you have the parameters there used in Laravel query.

DB::enableQueryLog();
$total = DB::table('user')
       ->where('group_id', '=', 1)
       ->where('status', '=', 1)
       ->count();
$queries = DB::getQueryLog();
$last_query = end($queries);
echo "<pre>"; print_r($last_query); echo "</pre>";

Output following:

Array
(
    [query] => select count(*) as aggregate from `user` where `group_id` = ? and `status` = ?
    [bindings] => Array
        (
            [0] => 64
            [1] => 1
        )

    [time] => 0
)

Raw Queries in Laravel | Get Sql Query And Bindings From Laravel Eloquent Query Builder | Execute Raw Query With Parameters

Laravel has a strong query builder to build queries. It is as much powerful as we need. But sometimes we need to execute raw sql using Laravel. Below is a code snippet to execute raw sql queries using Laravel. The below code will return some results.

$results = DB::select( DB::raw("SELECT * FROM table_name WHERE field_id = :field_id"),
array(
    'field_id' => 5,
));

If you dont need to return results and only need to do some statement level queries then do the following:

DB::statement("UPDATE USER SET STATUS='ACTIVE' WHERE ID=1;");

You can get Raw SQL and Bindings from Laravel eloquent query builder:

$query = DB::table("my_table")->where("id", $id);
echo $query->toSql();
print_r($query->getBindings());