Laravel - how to get the query with bind parameters?

Question

I can get the not-bind query on with this way :

\DB::enableQueryLog();
$items = OrderItem::where('name', '=', 'test')->get();
$log = \DB::getQueryLog();
print_r($log);

Output is :

(
    [0] => Array
        (
            [query] => select * from "order_items" where "order_items"."name" = ? and "order_items"."deleted_at" is null
            [bindings] => Array
                (
                    [0] => test
                )
            [time] => 0.07
        )
)

But what I really need is bind query like this :

select * from "order_items" where "order_items"."name" = 'test' and "order_items"."deleted_at" is null

I know I can do this with raw PHP but is there any solution in laravel core?

Answer 1

Actually I've created one function within helpers.php for same. You can also use same function within your helpers.php file

if (! function_exists('ql'))
{
    /**
     * Get Query Log
     *
     * @return array of queries
     */
    function ql()
    {
        $log = \DB::getQueryLog();

        $pdo = \DB::connection()->getPdo();

        foreach ($log as &$l)
        {
            $bindings = $l['bindings'];

            if (!empty($bindings))
            {
                foreach ($bindings as $key => $binding)
                {
                    // This regex matches placeholders only, not the question marks,
                    // nested in quotes, while we iterate through the bindings
                    // and substitute placeholders by suitable values.
                    $regex = is_numeric($key)
                        ? "/\?(?=(?:[^'\\\']*'[^'\\\']*')*[^'\\\']*$)/"
                        : "/:{$key}(?=(?:[^'\\\']*'[^'\\\']*')*[^'\\\']*$)/";

                    $l['query'] = preg_replace($regex, $pdo->quote($binding), $l['query'], 1);
                }
            }
        }

        return $log;
    }
}

if (! function_exists('qldd'))
{
    /**
     * Get Query Log then Dump and Die
     *
     * @return array of queries
     */
    function qldd()
    {
        dd(ql());
    }
}

if (! function_exists('qld'))
{
    /**
     * Get Query Log then Dump
     *
     * @return array of queries
     */
    function qld()
    {
        dump(ql());
    }
}

Simply place these three functions within your helpers.php file and you can use same as follows:

$items = OrderItem::where('name', '=', 'test')->get();
qldd(); //for dump and die

or you can use

qld(); // for dump only

Answer 2

Here I extended the answer of @blaz

In app\Providers\AppServiceProvider.php

Add this on boot() method

    if (env('APP_DEBUG')) {
        DB::listen(function($query) {
            File::append(
                storage_path('/logs/query.log'),
                self::queryLog($query->sql, $query->bindings) . "\n\n"
            );
        });
    }

and also added a private method

private function queryLog($sql, $binds)
{
    $result = "";
    $sql_chunks = explode('?', $sql);

    foreach ($sql_chunks as $key => $sql_chunk) {
        if (isset($binds[$key])) {
            $result .= $sql_chunk . '"' . $binds[$key] . '"';
        }
    }

    $result .= $sql_chunks[count($sql_chunks) -1];

    return $result;
}

Answer 3

Yeah, you're right :/ This is a highly requested feature, and i have no idea why its not a part of the framework yet...

This is not the most elegant solution, but you can do something like this:

    function getPureSql($sql, $binds) {
        $result = "";

        $sql_chunks = explode('?', $sql);
        foreach ($sql_chunks as $key => $sql_chunk) {
            if (isset($binds[$key])) {
                $result .= $sql_chunk . '"' . $binds[$key] . '"';
            }
        }

        return $result;
    }

    $query = OrderItem::where('name', '=', 'test');
    $pure_sql_query = getPureSql($query->toSql(), $query->getBindings()); 

    // Or like this:
    $data = OrderItem::where('name', '=', 'test')->get();

    $log = DB::getQueryLog();
    $log = end($log);
    $pure_sql_query = getPureSql($log['query'], $log['bindings']);

Answer 4

You can do that with:

    OrderItem::where('name', '=', 'test')->toSql();