How Do I Get the Query Builder to Output Its Raw SQL Query as a String

Question

Given the following code   DB  table  users  - gt 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

User · Answer

Add this function to your application and simply call.

function getQuery($sql){
        $query = str_replace(array('?'), array('\'%s\''), $sql->toSql());
        $query = vsprintf($query, $sql->getBindings());     
        return $query;
}

Output: "select * from user where lang = 'en' and status = '1' order by updated_at desc limit 25 offset 0"

User · Answer

Use    data   DB  select  select   from users where id    id     id    gt  1    print r  data     Output will like below   Array    0    gt  stdClass Object    id    gt  1  name    gt  parisa  last    gt  naderi  username    gt  png  password    gt  2132  role    gt  0

User · Answer

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  - gt where  id  1   echo  user- gt toSql     echo  user- gt 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- gt getBindings    user- gt toSql     echo  queryWithParam   Hope this also helps

User · Answer

From laravel 5 2 and onward  you can use DB  listen to get executed queries   DB  listen function   query            query- gt sql         query- gt bindings         query- gt time       Or if you want to debug a single Builder instance then you can use toSql method   DB  table  posts  - gt toSql

User · Answer

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- gt getBaseQuery               if   query instanceof Illuminate Database Eloquent Builder                  query    query- gt getQuery               if   query instanceof Illuminate Database Query Builder                 return    query    gt   query- gt toSql     bindings    gt   query- gt getBindings                 return false            logQuery       Get the SQL from a query in a closure       Usage     logQueries function            return User  first  - gt 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- gt count   -  numberOfQueries          Get last queries      lastQueries    queries- gt 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- gt push  lastQuery              return  lastQueries      Usage   getSql  DB  table  users         returns               sql    gt   select   from  users            bindings    gt            getSql   project- gt rooms         returns              sql    gt   select   from  rooms  where  rooms   project id      and  rooms   project id  is not null           bindings    gt    7

User · Answer

You can use toSql method - the easiest way DB  table  users  - gt 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  - gt whereIn  some field    1 2 30       sql with bindings   str replace array       query- gt getBindings     query- gt toSql      dd  sql with bindings

User · Answer

To See Laravel Executed Query use laravel query log  DB  enableQueryLog      queries   DB  getQueryLog

User · Answer

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- gt addConnection          connection details        Set the event dispatcher used by Eloquent models     optional   capsule- gt setEventDispatcher new Dispatcher new Container        Make this Capsule instance available globally via static methods     optional   capsule- gt setAsGlobal        Setup the Eloquent ORM    optional unless you ve used setEventDispatcher     capsule- gt bootEloquent        Listen for Query Events for Debug  events   new Dispatcher   events- gt listen  illuminate query   function  query   bindings   time   name           Format binding data for sql insertion     foreach   bindings as  i   gt   binding            if   binding instanceof  DateTime                 bindings  i     binding- gt 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- gt setEventDispatcher  events

User · Answer

I did it by listening query logs and appending to a log array     create query  query DB  table        - gt where        - gt orderBy          log      array of log lines       invoked on query execution if query log is enabled DB  listen function   query use  amp  log        log    query   enqueue query data to logs       enable query log DB  enableQueryLog     res  query- gt get     execute

User · Answer

My way of doing this  based on the log view  only needs to modify the file app Providers AppServiceProvider php    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- gt sql            queryRawSql   vsprintf  querySql   query- gt bindings           Log  debug   SQL EXEC                       raw sql     gt   queryRawSql                   time    gt   query- gt time                                        My sql handle code      users   DB  table  users       - gt select DB  raw  count    as user count  username         - gt where  uid     gt     10      - gt limit 100      - gt groupBy  username       - gt get     dd  users      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   gt    10  group by  username  limit 100   time  304 21

User · Answer

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- gt belongsToMany Job  class   eqtype jobs                      - gt withPivot   created at   updated at   id                       - gt 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- gt belongsToMany Job  class   eqtype jobs                          - gt withPivot   created ats   updated at   id                           - gt 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

User · Answer

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    quot   binding  quot                               prep   preg replace  quot      quot    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  - gt 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- gt toSql     bindings    query- gt getBindings

User · Answer

First way  Simply you can do following stuff using toSql   method   query   DB  table  users  - gt get     echo  query- gt 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

User · Answer

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- gt sql           query- gt bindings           query- gt time               So you don t need to put DB  enableQuerylog   and DB  getQuerylog   in every function

User · Answer

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    lt  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- gt sql                 query- gt bindings                 query- gt time                                   Register the service provider                 return void             public function register                              Source

User · Answer

data   User  toSql    echo  data    this will retrun select   from users    here User is model

User · Answer

You need to add binding in your sql output in order to found it readable  You can use the following code to print raw sql queries   users   User  where  status   1    users query   str replace array       array     s       users- gt toSql      users query   vsprintf  users query   users- gt getBindings     dump  users query     all users    users- gt get

User · Answer

DB  QueryLog   works only after you execute the query using  builder- gt get    If you want to get the raw query before or without executing the query  you can use the  builder- gt toSql   method  Example to get the raw SQL and to replace     with actual binding values   query   str replace array       array     s       builder- gt toSql      query   vsprintf  query   builder- gt getBindings     dump  query     result    builder- gt 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

User · Answer

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 - gt 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

User · Answer

In my opinion  this will be the best approach as a beginner   echo   lt pre gt    print r  query- gt toSql     print r  query- gt getBindings       This is also depicted here  https   stackoverflow com a 59207557 9573341

User · Answer

Add this code to your AppServiceProvider and get Log file            DB  listen function   query                  Log  info                    query- gt sql                    query- gt bindings                    query- gt time

User · Answer

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- gt count    but if I do  query- gt 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

User · Answer

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   gt  array 3         query    gt   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    gt  array 5          time    gt  3 79

User · Answer

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  - gt where  id   1       binding replaced  sql   str replace array       query- gt getBindings     query- gt toSql         for laravel 5 8   sql   Str  replaceArray       query- gt getBindings     query- gt toSql         print dd  sql

User · Answer

As of Laravel 5 8 15 the query builder now has dd and dump methods so you can do  DB  table  data  - gt where  a   1 - gt dump

User · Answer

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  - gt get      This will print out something like   array 4       0   gt    string 21   select   from  users      1   gt    array 0           2   gt    string 4   0 94     3   gt    string 6   sqlite

User · Answer

Print last query  DB  enableQueryLog      query          DB  getQueryLog     lastQuery      end  query   print r  lastQuery

User · Answer

There is a method in eloquent for getting query string    toSql    in our case    DB  table  users  - gt toSql       return  select   from users   is the exact solution that return the SQL query string  Hope this helpful

User · Answer

Try this    results   DB  table  users  - gt toSql    dd  results     Note  get   has been replaced with toSql   to display the raw SQL query

User · Answer

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

User · Answer

A  macroable  replacement to get the SQL query with the bindings    Add below macro function in AppServiceProvider boot   method    Illuminate Database Query Builder  macro  toRawSql   function        return array reduce  this- gt getBindings    function  sql   binding           return preg replace         is numeric  binding     binding        binding        sql  1           this- gt toSql          Add an alias for the Eloquent Builder   Laravel 5 4     Illuminate Database Eloquent Builder  macro  toRawSql   function        return   this- gt getQuery  - gt toRawSql          Then debug as usual   Laravel 5 4    E g  Query Builder   Log  debug  DB  table  users  - gt limit 1 - gt toRawSql      E g  Eloquent Builder   Log  debug  App User  limit 1 - gt 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 - gt getQuery  - gt toRawSql

User · Answer

use debugbar package   composer require  barryvdh laravel-debugbar    2 3

User · Answer

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- gt toSql      foreach    builder- gt getBindings   as  binding          value   is numeric  binding     binding        binding           sql   preg replace          value   sql  1         return  sql

User · Answer

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  gt  gt  gt  DB  listen function   query    dump  query- gt sql   dump  query- gt bindings   dump  query- gt time         gt  null  gt  gt  gt  App User  find 1   select   from  users  where  users   id      limit 1  array 1     0   gt  1   6 99   gt  App User   3131      id  1       name   admin        email   admin example com        created at   2019-01-11 19 06 23        updated at   2019-01-11 19 06 23         gt  gt  gt

User · Answer

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   gt    array 3          query    gt      string 21   select   from  users         bindings    gt      array 0                time    gt      string 4   0 92           Thanks to Joshua s comment below

User · Answer

You can use this package for get all the queries which are executing when you load your page  https   github com barryvdh laravel-debugbar

User · Answer

Use the toSql   method on a QueryBuilder instance   DB  table  users  - gt 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

User · Answer

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       quot string quot                           replace      quot   addslashes  replace    quot                                        sql   substr replace  sql   replace   pos  strlen  needle                                    return  sql              sql    replace  model- gt toSql     model- gt 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

[php] How Do I Get the Query Builder to Output Its Raw SQL Query as a String?

Examples related to php

Examples related to sql

Examples related to laravel

Examples related to laravel-4

Examples related to laravel-query-builder