How do I get the query builder to output its raw SQL query as a string?

Question

Given the following code:

DB::table('users')->get();

I want to get the raw SQL query string that the database query builder above will generate. In this example, it would be SELECT * FROM users.

How do I do this?

Searcy

Laravel Eloquent ORM get raw query: echo User::where('status', 1)->toSql(); – Antique

I am using a packet for Laravel - Telescope, it logs all queries and do many more things. – Impartial

All of these answers start with the DB class instead of a model. What if you're calling methods on a model class?. – Refrain

The way I do is not suggested but is the easiest, I just add a typo in the existing eloquent query and then check in laravel logs where I find the entire query. @Searcy – Subsumption

In Laravel 10 you are able to simplify the raw sql output by DB::table('users')->toRawSql(); – Vocative

Answer 1

To output to the screen the last queries ran you can use this:

\DB::enableQueryLog(); // Enable query log

// Your Eloquent query executed by using get()

dd(\DB::getQueryLog()); // Show results of log

I believe the most recent queries will be at the bottom of the array.

You will have something like that:

array(1) {
  [0]=>
  array(3) {
    ["query"]=>
    string(21) "select * from "users""
    ["bindings"]=>
    array(0) {
    }
    ["time"]=>
    string(4) "0.92"
  }
}

(Thanks to Joshua's comment below.)

Hargrove

Is there anyway Laravel can output this to the browser's console.log? – Searcy

hmm im not sure but you may be able to accompish what you want with a composer package https://mcmap.net/q/54961/-laravel-4-sql-log-console – Hargrove

Might even be better to output it to your application's log using the Log class: Log::debug(DB::getQueryLog()) – Megalocardia

@meiryo, if you want to have the SQL queries log in your console, maybe look at adding github.com/barryvdh/laravel-debugbar to your composer? – Laurent

You may need to enable this as it's turned off by default now. You can use this command to turn it on temporarily: DB::enableQueryLog(); – Utimer

I tried your answer. What I tried is DB::enableQueryLog(); dd(DB::getQueryLog()); But it returns just [].... – Bartholomeo

@IamtheMostStupidPerson run DB::enableQueryLog() first, then your query and last getQueryLog() in that order and you'll get a log instead of [] – Campinas

If you have multiple databases, you may need to do DB::connection('database')->getQueryLog() – Hallelujah

@all who is wondering why you get [] you are still not "calling" the sql. So just write somewhere before $yourQuery->get() – Waylin

Could be obvious but make sure you are placing the Log::debug(DB::getQueryLog()) after your DB or elqouent query. Works for Laravel 5.5. – Uzzial

you get the result query after it been executed! – Malinda

@Searcy if you want fast output of your queries in console without using the heavy laravel-debugbar logic, you could write a middleware that runs last and injects a small javascript snippet into HTML. – Jasper

If an exception occurs then the query isn't executed so you'll get [] even if you put ->get() – Dortheydorthy

With Laravel 7.x you'll need to add this at the top of the file: use Illuminate\Support\Facades\DB; – Sentinel

Just saved me hours of work. It's that call to DB::enableQueryLog(); that other answers don't clarify. – Gleeman

Answer 2

Use the toSql() method on a QueryBuilder instance.

DB::table('users')->toSql() would return:

select * from `users`

This is easier than wiring up an event listener, and also lets you check what the query will actually look like at any point while you're building it.

Note: This method works for query builder or Eloquent, however toSql() is used instead of first() or get(). You cannot run the query and also get the SQL at the same time using this method.

Loopy

I think this is the easiest way when using Eloquent outside Laravel – Coimbra

@Stormsson That's not possible because PHP never has the query with the bindings replaced with their values. To get the queries in their entirety you need to log them from MySQL. There's more info here: #1786822 – Parsimony

@Stormsson you can use getBindings method. This'll return the bindings in order that they'll be bound to the SQL statement. – Deedradeeds

@Stormsson in some cases you can use DB:raw for tham $sorted->where('currency', \DB::raw("'{$currency}'"));. It is a stupid way but sometimes it can will be very helpful – Islamism

Very helpful for debugging complicated queries that Eloquent refuses to run as those don't show up in the query log. – Thach

The toSql() is mainly for dev debugging and it's stupid that it does not try to bind variables in the php level by it self as below answer do. – Joni

Enyone like me (suggestion). we use get() in the end of our query function just replace it to toSql(); then use dd(......) it 'll work. – Felicity

To get query with bindinds $query = \DB::table('users')->where('id', 10); $sql = str_replace_array('?', $query->getBindings(), $query->toSql()); dd($sql); – Pinto

This worked for me: $sql = str_replace('?', "'?'", $query->toSql()); $sql = str_replace_array('?', $query->getBindings(), $sql); dd($sql); – Splurge

for laravel 6 $query = \DB::table('users')->where('id', 10); Str::replaceArray('?', $query->getBindings(), $query->toSql()); output select * from users where id = 10 – Exponible

See updated answer for laravel 5.8+ using DB::table('users')->dd() or DB::table('users')->dump() – Dortheydorthy

Answer 3

DB::QueryLog() works only after you execute the query using $builder->get().

If you want to get the raw query before or without executing the query, you can use the $builder->toSql() method.

Example to get the raw SQL and to replace '?' with actual binding values:

$query = str_replace(array('?'), array('\'%s\''), $builder->toSql());
$query = vsprintf($query, $builder->getBindings());
dump($query);

$result = $builder->get();

Or you can deliberately trigger an error, for example, by using a non-existent table or column. Then you can see the generated query in the exception message.

Dichromatic

This is, by far, the best answer, simple and straight to the point. Thanks :) – Evacuate

As a one-liner: $query = vsprintf(str_replace(array('?'), array('\'%s\''), $builder->toSql()), $builder->getBindings()); – Rawlings

This should be included in the framework as a native function.. thanks – Hallowell

Note that this will not work if your query already has percent signs such as for a LIKE query or when formatting dates. You'll need to escape those first with double percent signs. – Lepore

Are there security concerns when doing this? Do the bindings come sanitized from $builder->getBindings()? – Lenny

This is the best answer! I've a made a macros: ``` class AppServiceProvider extends ServiceProvider { public function boot() { // ... \Illuminate\Database\Query\Builder::macro( 'dumpSql', function () { $query = str_replace(array('?'), array('\'%s\''), $this->toSql()); $query = vsprintf($query, $this->getBindings()); dump($query); return $this; } ); } } ``` – Quadruple

Answer 4

You can listen to the 'illuminate.query' event. Before the query add the following event listener:

Event::listen('illuminate.query', function($query, $params, $time, $conn) 
{ 
    dd(array($query, $params, $time, $conn));
});

DB::table('users')->get();

This will print out something like:

array(4) {
  [0]=>
  string(21) "select * from "users""
  [1]=>
  array(0) {
  }
  [2]=>
  string(4) "0.94"
  [3]=>
  string(6) "sqlite"
}

Meader

I get Call to undefined method Illuminate\Database\Query\Builder::listen() in Laravel 4 – Disincline

Thanks this is great. Its good to note that dd is a function that produces a Dump the given variable and end execution of the script and also that to import Event, include use Illuminate\Support\Facades\Event; – Triadelphous

@radtek: Instead of use Illuminate\Support\Facades\Event; you can simply do use Event; since it's a facade. – Saffren

Answer 5

If you are trying to get the Log using Illuminate without Laravel use:

\Illuminate\Database\Capsule\Manager::getQueryLog();

You could also nock up a quick function like so:

function logger()
{
    $queries = \Illuminate\Database\Capsule\Manager::getQueryLog();
    $formattedQueries = [];
    foreach ($queries as $query) :
        $prep = $query['query'];

        foreach ($query['bindings'] as $binding) :

            if (is_bool($binding)) {
                $val = $binding === true ? 'TRUE' : 'FALSE';
            } else if (is_numeric($binding)) {
                $val = $binding;
            } else {
                $val = "'$binding'";
            }

            $prep = preg_replace("#\?#", $val, $prep, 1);
        endforeach;
        $formattedQueries[] = $prep;
    endforeach;
    return $formattedQueries;
}

EDIT

updated versions seem to have query logging disabled by default (the above returns an empty array). To turn back on, when initialising the Capsule Manager, grab an instance of the connection and call the enableQueryLog method

$capsule::connection()->enableQueryLog();

EDIT AGAIN

Taking the actual question into consideration, you could actually do the following to convert the current single query instead of all previous queries:

$sql = $query->toSql();
$bindings = $query->getBindings();

Sift

i am getting this type of return from query "name = [{"name":"rifat"}]" what i need to do to get "name = rifat" only? – Tender

I would print out your bindings, looks like you are passing an array instead of a string – Sift

This is a helpful start, but it seems to neglect to add single quotes around parameterized values, such as when I pass a string like 'US/Eastern'. – Beatty

@Beatty this is true hence why I stated quick function. I believe the underlying code will use prepare (php.net/manual/en/mysqli.prepare.php) methods, which is why just the ? is required. You could php.net/manual/en/function.is-numeric.php to determine whether or not to encapsulate the input within single quotes. – Sift

@LukeSnowden Your answer is genius! I finally took the time to try out your new version (which I edited above to include your is_numeric idea), and it works! I love this. Thank you. – Beatty

getBindings() where have you been all my life. – Marlanamarlane

Answer 6

There is a method in eloquent for getting query string.

toSql()

in our case,

 DB::table('users')->toSql(); 

return

select * from users

is the exact solution that return the SQL query string..Hope this helpful...

Autumn

what about the query bindings? e.g. when you do ->where('foo', '=', 'bar') bar wont show in the sql – Elsaelsbeth

Answer 7

$data = User::toSql();
echo $data; //this will retrun select * from users. //here User is model

Dendro

This is much more precise, controlled, and meets the need of the question. – Gorky

Thank you for your comment. – Dendro

You can append ->toSql() as you would do if there are more arguments after the model. e.g. User::where('id', 1)->toSql() – Roughish

Answer 8

This is the far best solution I can suggest to any one for debug-ing eloquent last query or final query although this has been discussed as well:

// query builder
$query = DB::table('table_name')->where('id', 1);

// binding replaced
$sql = str_replace_array('?', $query->getBindings(), $query->toSql());

// for laravel 5.8^
$sql = Str::replaceArray('?', $query->getBindings(), $query->toSql());

// print
dd($sql);

Barbee

you need to use the below line in the top of your code: for beginner use Illuminate\Support\Str; to use the Str class – Cavell

Answer 9

If you use laravel 5.1 and MySQL you can use this function made by me:

/*
 *  returns SQL with values in it
 */
function getSql($model)
{
    $replace = function ($sql, $bindings)
    {
        $needle = '?';
        foreach ($bindings as $replace){
            $pos = strpos($sql, $needle);
            if ($pos !== false) {
                if (gettype($replace) === "string") {
                     $replace = ' "'.addslashes($replace).'" ';
                }
                $sql = substr_replace($sql, $replace, $pos, strlen($needle));
            }
        }
        return $sql;
    };
    $sql = $replace($model->toSql(), $model->getBindings());
    
    return $sql;
}

As an input parameter you can use either of these

Illuminate\Database\Eloquent\Builder

Illuminate\Database\Eloquent\Relations\HasMany

Illuminate\Database\Query\Builder

Hyperform

Answer improved to include all remarks made in comments. Thank you very much. – Hyperform

Answer 10

First You will need to enable the query log by calling:

DB::enableQueryLog();

after queries using the DB facade you can write:

dd(DB::getQueryLog());

the output will like below:

array:1 [▼
  0 => array:3 [▼
    "query" => "select * from `users` left join `website_user` on `users`.`id` = `website_user`.`user_id` left join `region_user` on `users`.`id` = `region_user`.`user_id` left ▶"
    "bindings" => array:5 [▶]
    "time" => 3.79
  ]
]

Rodmun

very helpful answer – Weller

hi i have used $result = DB::select('select * from sqrt_user_modules where user_id = :id', ['id' => $user]); DB::enableQueryLog(); but didnt get any output dd(DB::getQueryLog()); – Weller

do we need to include any library – Weller

Step 1: DB::enableQueryLog(); step 2 :$result = DB::select('select * from sqrt_user_modules where user_id = :id', ['id' => $user]); step 3: dd(DB::getQueryLog()); – Rodmun

The only clear solution – Doucette

Answer 11

A 'macroable' replacement to get the SQL query with the bindings.

1. Add below macro function in AppServiceProvider boot() method.

   \Illuminate\Database\Query\Builder::macro('toRawSql', function(){
       return array_reduce($this->getBindings(), function($sql, $binding){
           return preg_replace('/\?/', is_numeric($binding) ? $binding : "'".$binding."'" , $sql, 1);
       }, $this->toSql());
   });
   

2. Add an alias for the Eloquent Builder. (Laravel 5.4+)

   \Illuminate\Database\Eloquent\Builder::macro('toRawSql', function(){
       return ($this->getQuery()->toRawSql());
   });
   

3. Then debug as usual. (Laravel 5.4+)

   E.g. Query Builder

   \Log::debug(\DB::table('users')->limit(1)->toRawSql())
   

   E.g. Eloquent Builder

   \Log::debug(\App\User::limit(1)->toRawSql());
   

Note: from Laravel 5.1 to 5.3, Since Eloquent Builder doesn't make use of the Macroable trait, cannot add toRawSql an alias to the Eloquent Builder on the fly. Follow the below example to achieve the same.

E.g. Eloquent Builder (Laravel 5.1 - 5.3)

\Log::debug(\App\User::limit(1)->getQuery()->toRawSql());

Bassett

Whoops, I came late. Just want to submit an answer using Macro. This is the nicest answer. Should be the accepted answer :D – Forme

You can abstract away the latter in a scope on the base Model – Trichromatic

Answer 12

The most easiest way is to make deliberate mistake. For example, I want to see the full SQL query of the following relation:

public function jobs()
{
    return $this->belongsToMany(Job::class, 'eqtype_jobs')
        ->withPivot(['created_at','updated_at','id'])
        ->orderBy('pivot_created_at','desc');
}

I just to make a column to be not found, here I choose created_at and I changed it to created_ats by adding trailing s to be:

public function jobs()
{
    return $this->belongsToMany(Job::class, 'eqtype_jobs')
        ->withPivot(['created_ats','updated_at','id'])
        ->orderBy('pivot_created_at','desc');
}

So, the debuger will return the following error:

(4/4) ErrorException SQLSTATE[42S22]: Column not found: 1054 Unknown column 'eqtype_jobs.created_ats' in 'field list' (SQL: select jobs.*, eqtype_jobs.set_id as pivot_set_id, eqtype_jobs.job_id as pivot_job_id, eqtype_jobs.created_ats as pivot_created_ats, eqtype_jobs.updated_at as pivot_updated_at, eqtype_jobs.id as pivot_id from jobs inner join eqtype_jobs on jobs.id = eqtype_jobs.job_id where eqtype_jobs.set_id = 56 order by pivot_created_at desc limit 20 offset 0) (View: /home/said/www/factory/resources/views/set/show.blade.php)

The above error message returns the full SQL query with the mistake

SQL: select  jobs.*, eqtype_jobs.set_id as pivot_set_id,  eqtype_jobs.job_id as pivot_job_id, eqtype_jobs.created_ats as pivot_created_ats, eqtype_jobs.updated_at as  pivot_updated_at, eqtype_jobs.id as pivot_id from jobs inner join eqtype_jobs on jobs.id = eqtype_jobs.job_id where  eqtype_jobs.set_id = 56 order by pivot_created_at desc limit 20 offset 0

Now, just remove the extra s from created_at and test this SQL as you like in any SQL editor such as phpMyAdmin SQL editor!

###Notice: The solution has been tested with Laravel 5.4.

Ectomere

This is the best answer by far! So simple! :) – Shreeves

This will not show the query with bindings, ie bindings will show like :id – Lambeth

@ShanthaKumara Indeed, I don't know what is the version or the configuration of Laravel that you have used. However, every snippet or code in my answer was copied and pasted from real code output of Laravel 5.4 project. – Ectomere

Still working perfectly in Laravel 10. – Drover

Answer 13

First way:

Simply you can do following stuff using toSql() method,

$query = DB::table('users')->get();

echo $query->toSql();

If it's not working you can set-up the thing from laravel documentation.

Second way:

Another way to do it is

DB::getQueryLog()

but if it's returns an empty array then by default it's disabled visit this,

just enable with DB::enableQueryLog() and it will work :)

for more info visit Github Issue to know more about it.

Hope it helps :)

Rope

Answer 14

As of Laravel 5.8.15 the query builder now has dd and dump methods so you can do

DB::table('data')->where('a', 1)->dump();

Expansible

Thanks. dd works really well. DB::table('data')->where('a', 1)->dd(); – Hussy

Better than other answers listed. – Bueschel

Answer 15

In my opinion, this will be the best approach as a beginner:

echo "<pre>";
print_r($query->toSql());
print_r($query->getBindings());

This is also depicted here. https://mcmap.net/q/54962/-does-laravel-39-s-tosql-method-mask-ids-column-value-being-replaced-by-question-mark

Unmade

Answer 16

There's a lot of information already answered, will just post my own findings that i've been using whenever i need to output the sql query before it's being executed.

Consider below sample:

$user = DB::table('user')->where('id',1);
echo $user->toSql();

echo $user->toSql() = This will just out put the raw query but will not show the parameter(s) passed.

To output the query with the parameter being passed we can use laravel getBindings() and helper str_replace_array like this:

$queryWithParam = str_replace_array('?',$user->getBindings(),$user->toSql());
echo $queryWithParam;

Hope this also helps.

Stupa

Answer 17

use debugbar package

composer require "barryvdh/laravel-debugbar": "2.3.*"

Impose

Answer 18

From laravel 5.2 and onward. you can use DB::listen to get executed queries.

DB::listen(function ($query) {
    // $query->sql
    // $query->bindings
    // $query->time
});

Or if you want to debug a single Builder instance then you can use toSql method.

DB::table('posts')->toSql(); 

Alodium

The listen thing is useful, declare it before running the query, and dump out the sql & bindings within the method. Imperfect but works quicker/easier than other answers. – Track

Answer 19

With the latest Version of Laravel 10 you can now output your SQL Queries including the bindings simply by using toRawSQL() on your QueryBuilder:

User::where('email', '[email protected]')->toRawSql();
// "SELECT * FROM users WHERE email = '[email protected]'"

The new dd() output might be handy as well:

User::where('email', '[email protected]')->dd();
// "SELECT * FROM users WHERE email = ?"
// [
//  0 => "[email protected]"
// ]

See the Laravel News announcement for more information

Vocative

Worth noting that this new feature relies on manually escaping values and may still present a vulnerability to SQL injection. Output from Builder::toRawSql() should only be used for debugging purposes. – Paraprofessional

@Paraprofessional not necesarly. You can use it to store hashed queries and "restore" them with next request. Something like md5('secret' . $rawQuery) => $rawQuery kept server-side can help running Livewire Modals where you can't and don't want to pass whole collection as attribute. Use case: async modal to select the item from collection. – Unseal

Answer 20

To See Laravel Executed Query use laravel query log

DB::enableQueryLog();

$queries = DB::getQueryLog();

Water

Answer 21

You can use toSql method - the easiest way

DB::table('users')->toSql();

And also if you have bindings in your query and want to see the query with bindings. You cant use somthing like that:

$query = DB::table('table')->whereIn('some_field', [1,2,30]); 

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

dd($sql_with_bindings);

Mohawk

Answer 22

In order to log all the executed queries you can use DB::enableQueryLog() icw DB::getQueryLog(). The output has the structure below.

[
  [
    "query" => "select * from "users" where name = ?"
    "bindings" => ["John Doe"]
    "time" => 0.34
  ],
  ...
]

Furthermore, I combined some answers here in order to get the perfect function to parse the sql with the compiled bindings. See below. I even created a custom Builder class implementing this functionality in order to do e.g. User::where('name','John Doe')->parse();

function parse_sql(string $sql, array $bindings) : string
{
  $compiled_bindings  = array_map('compile_binding', $bindings);

  return preg_replace_array("/\?/", $compiled_bindings, $sql);
}

function compile_binding($binding)
{
  $grammar = new MySqlGrammar;

  if (is_bool($binding))
  {
    return (int)$binding; //This line depends on the database implementation
  }

  if(is_string($binding))
  {
    return "'$binding'";
  }

  if ($binding instanceof DateTimeInterface)
  {
    return $binding->format($grammar->getDateFormat());
  }

  return $binding;
}

Fabi

$grammar->quoteString($value) should do this for you. – Goulden

Answer 23

This is the function, I placed in my base model class. Simply pass the query builder object into it and the SQL string will be returned.

function getSQL($builder) {
  $sql = $builder->toSql();
  foreach ( $builder->getBindings() as $binding ) {
    $value = is_numeric($binding) ? $binding : "'".$binding."'";
    $sql = preg_replace('/\?/', $value, $sql, 1);
  }
  return $sql;
}

Cunningham

Answer 24

Try this:

$results = DB::table('users')->toSql();
dd($results);

Note: get() has been replaced with toSql() to display the raw SQL query.

Kinematics

Answer 25

For laravel 5.5.X

If you would like to receive each SQL query executed by your application, you may use the listen method. This method is useful for logging queries or debugging. You may register your query listener in a service provider:

<?php

namespace App\Providers;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        DB::listen(function ($query) {
            // $query->sql
            // $query->bindings
            // $query->time
        });
    }

    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

Source

Palawan

Answer 26

As much as I love this framework, I hate when it acts like crap.

DB::enableQueryLog() is totally useless. DB::listen is equally useless. It showed part of the query when I said $query->count(), but if I do $query->get(), it has nothing to say.

The only solution that appears to work consistently is to intentionally put some syntax or other error in the ORM parameters, like an nonexistent column/table name, run your code on the command line while in debug mode, and it will spit out the SQL error with the full frickin' query finally. Otherwise, hopefully the error appears in the log file if ran from the web server.

Nessus

The query log works fine for me at least. You should have some other errors in your application – Harelda

Answer 27

If you are using tinker and want to log the SQL query formed you can do

$ php artisan tinker
Psy Shell v0.9.9 (PHP 7.3.5 — cli) by Justin Hileman
>>> DB::listen(function ($query) { dump($query->sql); dump($query->bindings); dump($query->time); });
=> null
>>> App\User::find(1)
"select * from `users` where `users`.`id` = ? limit 1"
array:1 [
  0 => 1
]
6.99
=> App\User {#3131
     id: 1,
     name: "admin",
     email: "[email protected]",
     created_at: "2019-01-11 19:06:23",
     updated_at: "2019-01-11 19:06:23",
   }
>>>

Egocentric

This has made it so much easier to debug complex apps. – Haywood

Answer 28

First Option

Definitely there are ways to output just a single query and have that debugged in phpMyAdmin or other tools to understand how the query perform.

One good way to dump your query along with the variables (also known as bindings) you can add below function as a common helper in your project

function queryToSQL($query, $logQuery = true)
{
    $addSlashes = str_replace('?', "'?'", $query->toSql());

    $sql = str_replace('%', '#', $addSlashes);

    $sql = str_replace('?', '%s', $sql);

    $sql = vsprintf($sql, $query->getBindings());

    $sql = str_replace('#', '%', $sql);

    if ($logQuery) {
        Log::debug($sql);
    }

    return $sql;
}

Second Option

This is an alternate approach rather having dump each of your queries, you can make use of Telescope, this tool gives you deeper insights of all the queries that might have been fired in background and how much time each one of them took along with all bindings displayed

Third option

Laravel Debugbar is an amazing plugin that helps you debug everything under tiny bottom bar, but this smoothly only for your UI based activities, for API's or commands the way to debug is missed out where Telescope becomes a great helper

Crow

Answer 29

You can use this package for get all the queries which are executing when you load your page

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

Dolt

That package is good when you don't have query errors. If you have an SQL error it won't show anything – Trinl

Answer 30

Print last query

DB::enableQueryLog();

$query        = DB::getQueryLog();
$lastQuery    = end($query);
print_r($lastQuery);

Incantation

Answer 31

My way of doing this, based on the log view, only needs to modify the file app/Providers/AppServiceProvider.php:

1. Add this code into app/Providers/AppServiceProvider.php

/**
 * Bootstrap any application services.
 *
 * @return void
 */
public function boot()
{
    //
    DB::listen(function ($query) {
        $querySql = str_replace(['?'], ['\'%s\''], $query->sql);
        $queryRawSql = vsprintf($querySql, $query->bindings);
        Log::debug('[SQL EXEC]', [
                "raw sql"  => $queryRawSql,
                "time" => $query->time,
            ]
        );
    });
}

2. My sql handle code :

$users = DB::table('users')
    ->select(DB::raw('count(*) as user_count, username '))
    ->where('uid', '>=', 10)
    ->limit(100)
    ->groupBy('username')
    ->get()
;
dd($users);

3. See log storage/logs/laravel-2019-10-27.log :

[2019-10-27 17:39:17] local.DEBUG: [SQL EXEC] {"raw sql":"select count(*) as user_count, username  from `users` where `uid` >= '10' group by `username` limit 100","time":304.21} 

Tussle

Answer 32

If you are not using Laravel but using Eloquent package then:

use \Illuminate\Database\Capsule\Manager as Capsule;
use \Illuminate\Events\Dispatcher;
use \Illuminate\Container\Container;

$capsule = new Capsule;

$capsule->addConnection([
    // connection details
]);
// Set the event dispatcher used by Eloquent models... (optional)
$capsule->setEventDispatcher(new Dispatcher(new Container));

// Make this Capsule instance available globally via static methods... (optional)
$capsule->setAsGlobal();

// Setup the Eloquent ORM...(optional unless you've used setEventDispatcher())
$capsule->bootEloquent();

// Listen for Query Events for Debug
$events = new Dispatcher;
$events->listen('illuminate.query', function($query, $bindings, $time, $name)
{
    // Format binding data for sql insertion
    foreach ($bindings as $i => $binding) {
        if ($binding instanceof \DateTime) {
            $bindings[$i] = $binding->format('\'Y-m-d H:i:s\'');
        } else if (is_string($binding)) {
            $bindings[$i] = "'$binding'";`enter code here`
        }
    }

    // Insert bindings into query
    $query = str_replace(array('%', '?'), array('%%', '%s'), $query);
    $query = vsprintf($query, $bindings);

    // Debug SQL queries
    echo 'SQL: [' . $query . ']';
});

$capsule->setEventDispatcher($events);

Nickelous

Answer 33

I've created some simple functions to get the SQL and bindings from some queries.

/**
 * getSql
 *
 * Usage:
 * getSql( DB::table("users") )
 * 
 * Get the current SQL and bindings
 * 
 * @param  mixed  $query  Relation / Eloquent Builder / Query Builder
 * @return array          Array with sql and bindings or else false
 */
function getSql($query)
{
    if( $query instanceof Illuminate\Database\Eloquent\Relations\Relation )
    {
        $query = $query->getBaseQuery();
    }

    if( $query instanceof Illuminate\Database\Eloquent\Builder )
    {
        $query = $query->getQuery();
    }

    if( $query instanceof Illuminate\Database\Query\Builder )
    {
        return [ 'query' => $query->toSql(), 'bindings' => $query->getBindings() ];
    }

    return false;
}

/**
 * logQuery
 *
 * Get the SQL from a query in a closure
 *
 * Usage:
 * logQueries(function() {
 *     return User::first()->applications;
 * });
 * 
 * @param  closure $callback              function to call some queries in
 * @return Illuminate\Support\Collection  Collection of queries
 */
function logQueries(closure $callback) 
{
    // check if query logging is enabled
    $logging = DB::logging();

    // Get number of queries
    $numberOfQueries = count(DB::getQueryLog());

    // if logging not enabled, temporarily enable it
    if( !$logging ) DB::enableQueryLog();

    $query = $callback();

    $lastQuery = getSql($query);

    // Get querylog
    $queries = new Illuminate\Support\Collection( DB::getQueryLog() );

    // calculate the number of queries done in callback
    $queryCount = $queries->count() - $numberOfQueries;

    // Get last queries
    $lastQueries = $queries->take(-$queryCount);

    // disable query logging
    if( !$logging ) DB::disableQueryLog();

    // if callback returns a builder object, return the sql and bindings of it
    if( $lastQuery )
    {
        $lastQueries->push($lastQuery);
    }

    return $lastQueries;
}

Usage:

getSql( DB::table('users') );
// returns 
// [
//     "sql" => "select * from `users`",
//     "bindings" => [],
// ]

getSql( $project->rooms() );
// returns
// [
//     "sql" => "select * from `rooms` where `rooms`.`project_id` = ? and `rooms`.`project_id` is not null",
//     "bindings" => [ 7 ],
// ]

Deiform

Answer 34

Here comes the perfect example:

https://laravel.com/docs/5.8/database#listening-for-query-events

Open app\Providers\AppServiceProvider.php and add the following to Boot() function:

DB::listen(function ($query) {
    var_dump([
        $query->sql,
        $query->bindings,
        $query->time
    ]);
});

So you don't need to put DB::enableQuerylog() and DB::getQuerylog() in every function.

Arrow

Answer 35

you can use clockwork

Clockwork is a Chrome extension for PHP development, extending Developer Tools with a new panel providing all kinds of information useful for debugging and profiling your PHP applications, including information about request, headers, get and post data, cookies, session data, database queries, routes, visualisation of application runtime and more.

but works also in firefox

Terri

Answer 36

Add this code to your AppServiceProvider and get Log file

         \DB::listen(function ($query) {
             \Log::info(
                 $query->sql,
                 $query->bindings,
                 $query->time
             );
         });

Novation

Answer 37

Here is the solution I use:

DB::listen(function ($sql, $bindings, $time) {
    $bound = preg_replace_callback("/\?/", function($matches) use ($bindings) {
        static $localBindings;
        if (!isset($localBindings)) {
            $localBindings = $bindings;
        }
        $val = array_shift($localBindings);

        switch (gettype($val)) {
            case "boolean":
                $val = ($val === TRUE) ? 1 : 0;  // mysql doesn't support BOOL data types, ints are widely used
                // $val = ($val === TRUE) ? "'t'" : "'f'";   // todo: use this line instead of the above for postgres and others
                break;

            case "NULL":
                $val = "NULL";
                break;

            case "string":
            case "object":
                $val = "'". addslashes($val). "'";   // correct escaping would depend on the RDBMS
                break;
        }
        return $val;
    }, $sql);
    array_map(function($x) { 
        (new \Illuminate\Support\Debug\Dumper)->dump($x); 
    }, [$sql, $bindings, $bound]);
});

Please, read the comments in the code. I know, it is not perfect but for my everyday debugging it is OK. It tries to build the bound query with more-or-less reliability. However, don't trust it completely, the database engines escape the values differently which this short function does not implement. So, take the result carefully.

Jehius

Answer 38

I did it by listening query logs and appending to a log array:

//create query
$query=DB::table(...)...->where(...)...->orderBy(...)...
$log=[];//array of log lines
...
//invoked on query execution if query log is enabled
DB::listen(function ($query)use(&$log){
    $log[]=$query;//enqueue query data to logs
});
//enable query log
DB::enableQueryLog();
$res=$query->get();//execute

Roadstead