Doctrine - How to print out the real sql not just the prepared statement

Question

We re using Doctrine  a PHP ORM  I am creating a query like this    q   Doctrine Query  create  - gt select  id  - gt from  MyTable      and then in the function I m adding in various where clauses and things as appropriate  like this   q- gt where  normalisedname     OR name       array  string   originalString      Later on  before execute  -ing that query object  I want to print out the raw SQL in order to examine it  and do this    q- gt getSQLQuery      However that only prints out the prepared statement  not the full query  I want to see what it is sending to the MySQL  but instead it is printing out a prepared statement  including   s  Is there some way to see the  full  query

User · Answer

You can easily access the SQL parameters using the following approach.

   $result = $qb->getQuery()->getSQL();

   $param_values = '';  
   $col_names = '';   

   foreach ($result->getParameters() as $index => $param){              
            $param_values .= $param->getValue().',';
            $col_names .= $param->getName().',';
   } 

   //echo rtrim($param_values,',');
   //echo rtrim($col_names,',');

So if you printed out the $param_values and $col_names , you can get the parameter values passing through the sql and respective column names.

Note : If $param returns an array, you need to re iterate, as parameters inside IN (:?) usually comes is as a nested array.

Meantime if you found another approach, please be kind enough to share with us :)

Thank you!

User · Answer

I have created a Doctrine2 Logger that does exactly this  It  hydrates  the parametrized sql query with the values using Doctrine 2 own data type conversors    lt  php   namespace Drsm Doctrine DBAL Logging  use Doctrine DBAL Logging SQLLogger      Doctrine DBAL Types Type      Doctrine DBAL Platforms AbstractPlatform         A SQL logger that logs to the standard output and    subtitutes params to get a ready to execute SQL sentence      author  dsamblas gmail com     class EchoWriteSQLWithoutParamsLogger implements SQLLogger        const QUERY TYPE SELECT  SELECT       const QUERY TYPE UPDATE  UPDATE       const QUERY TYPE INSERT  INSERT       const QUERY TYPE DELETE  DELETE       const QUERY TYPE CREATE  CREATE       const QUERY TYPE ALTER  ALTER        private  dbPlatform      private  loggedQueryTypes      public function   construct AbstractPlatform  dbPlatform  array  loggedQueryTypes array              this- gt dbPlatform  dbPlatform           this- gt loggedQueryTypes  loggedQueryTypes                         inheritdoc              public function startQuery  sql  array  params   null  array  types   null                 if  this- gt isLoggable  sql                if  empty  params                    foreach   params as  key  gt  param                         type Type  getType  types  key                         value  type- gt convertToDatabaseValue  param  this- gt dbPlatform                        sql   join var export  value  true   explode       sql  2                                                 echo  sql        PHP EOL                                    inheritdoc              public function stopQuery                    private function isLoggable  sql           if  empty  this- gt loggedQueryTypes   return true          foreach  this- gt loggedQueryTypes as  validType               if  strpos  sql   validType      0  return true                    return false            Usage Example   The following peace of code will echo on standard output any INSERT UPDATE DELETE SQL sentences generated with  em Entity Manager        var   Doctrine ORM EntityManager  em     em- gt getConnection                   - gt getConfiguration                   - gt setSQLLogger                      new EchoWriteSQLWithoutParamsLogger                           em- gt getConnection  - gt getDatabasePlatform                            array                              EchoWriteSQLWithoutParamsLogger  QUERY TYPE UPDATE                              EchoWriteSQLWithoutParamsLogger  QUERY TYPE INSERT                              EchoWriteSQLWithoutParamsLogger  QUERY TYPE DELETE

User · Answer

To print out an SQL query in Doctrine  use    query- gt getResult  - gt getSql

User · Answer

Maybe it can be useful for someone      Printing the SQL with real values  vals    query- gt getFlattenedParams    foreach explode       query- gt getSqlQuery    as  i   gt   part         sql    isset  sql     sql   null     part      if  isset  vals  i     sql     vals  i      echo  sql

User · Answer

My solution           Get SQL from query         author Yosef Kaminskyi      param QueryBilderDql  query     return int     public function getFullSQL  query         sql    query- gt getSql         paramsList    this- gt getListParamsByDql  query- gt getDql          paramsArr   this- gt getParamsArray  query- gt getParameters          fullSql         for  i 0  i lt strlen  sql   i             if  sql  i                      nameParam array shift  paramsList                if is string   paramsArr  nameParam                      fullSql       addslashes  paramsArr  nameParam                                   elseif is array  paramsArr  nameParam                      sqlArr                     foreach   paramsArr  nameParam  as  var                       if  empty  sqlArr                            sqlArr                            if is string  var                             sqlArr      addslashes  var                            else                          sqlArr   var                                     fullSql   sqlArr               elseif is object  paramsArr  nameParam                     switch get class  paramsArr  nameParam                         case  DateTime                                 fullSql        paramsArr  nameParam - gt format  Y-m-d H i s                                  break                      default                           fullSql    paramsArr  nameParam - gt getId                                                 else                                       fullSql    paramsArr  nameParam               else                fullSql   sql  i                       return  fullSql             Get query params list         author Yosef Kaminskyi  lt yosefk spotoption com gt      param  Doctrine ORM Query Parameter  paramObj     return int     protected function getParamsArray  paramObj         parameters array        foreach   paramObj as  val               var  val Doctrine ORM Query Parameter             parameters  val- gt getName     val- gt getValue               return  parameters     public function getListParamsByDql  dql         parsedDql   preg split         dql        length   count  parsedDql        parmeters   array        for  i 1  i lt  length  i             if ctype alpha  parsedDql  i  0                  param    preg split               parsedDql  i                  parmeters      param 0                        return  parmeters     Example of usage     query    this- gt  entityRepository- gt createQueryBuilder  item     query- gt leftJoin  item receptionUser   users     query- gt where  item customerid    customer  - gt setParameter  customer   customer  - gt andWhere  item paymentmethod    paymethod  - gt setParameter  paymethod   Bonus    echo  this- gt getFullSQL  query- gt getQuery

User · Answer

Solution 1                                                                                       function showQuery  query        return sprintf str replace        s    query- gt getSql      query- gt getParams           call function   echo showQuery  doctrineQuery    Solution 2                                                                                       function showQuery  query           define vars                    output      NULL       out query    query- gt getSql         out param    query- gt getParams            replace params    for  i 0   i lt strlen  out query    i              output      strpos  out query  i            FALSE          str replace      array shift  out param    out query  i           out query  i               output    return sprintf   s    output         call function   echo showQuery  doctrineQueryObject

User · Answer

There is no other real query  this is how prepared statements work  The values are bound in the database server  not in the application layer  See my answer to this question  In PHP with PDO  how to check the final SQL parametrized query   Repeated here for convenience    Using prepared statements with parametrised values is not simply another way to dynamically create a string of SQL  You create a prepared statement at the database  and then send the parameter values alone  So what is probably sent to the database will be a PREPARE      then SET     and finally EXECUTE      You won t be able to get some SQL string like SELECT   FROM      even if it would produce equivalent results  because no such query was ever actually sent to the database

User · Answer

I made some research for this topic  because i wanted to debug a generated SQL query and execute it in the sql editor  As seen in all the answers  it is a highly technical topic  When i assume that the initial question is base on dev-env  one very simple answer is missing at the moment  You can just use the build in Symfony profiler  Just click on the Doctrine Tab  Scroll to the query you want to inspect  Then click on  quot view runnable query quot  and you can paste your query directly in your SQL editor More UI base approach but very quick and without debugging code overhead

User · Answer

sql    query- gt getSQL      parameters           foreach   query- gt getParameters   as  parameter             parameters      parameter- gt getValue            result    connection- gt executeQuery  sql   parameters          - gt fetchAll

User · Answer

More clear solution           Get string query          param Doctrine Query  query     return string     public function getDqlWithParams Doctrine Query  query        vals    query- gt getFlattenedParams         sql    query- gt getDql         sql   str replace        s    sql       return vsprintf  sql   vals

User · Answer

A working example    qb    this- gt createQueryBuilder  a     query  qb- gt getQuery       SHOW SQL   echo  query- gt getSQL        Show Parameters   echo  query- gt getParameters

User · Answer

sql    query- gt getSQL     obj- gt mapDQLParametersNamesToSQL  query- gt getDQL     sql   echo  sql   to see parameters names in sql  obj- gt mapDQLParametersValuesToSQL  query- gt getParameters     sql   echo  sql   to see parameters values in sql  public function mapDQLParametersNamesToSQL  dql   amp  sql         matches            parameterNamePattern       w             Found parameter names in DQL        preg match all  parameterNamePattern   dql   matches       if  empty  matches 0              return             needle            foreach   matches 0  as  match             strPos   strpos  sql   needle           if   strPos     false                    Paste parameter names in SQL                 sql   substr replace  sql   match   strPos  strlen  needle                       public function mapDQLParametersValuesToSQL  parameters   amp  sql         matches            parameterNamePattern       w             Found parameter names in SQL        preg match all  parameterNamePattern   sql   matches       if  empty  matches 0              return            foreach   matches 0  as  parameterName             strPos   strpos  sql   parameterName           if   strPos     false                foreach   parameters as  parameter                         var  Doctrine ORM Query Parameter  parameter                    if   parameterName            parameter- gt getName                          continue                                     parameterValue    parameter- gt getValue                    if  is string  parameterValue                          parameterValue      parameterValue                                      if  is array  parameterValue                         foreach   parameterValue as  key   gt   value                            if  is string  value                                  parameterValue  key       value                                                                         parameterValue   implode        parameterValue                                         Paste parameter values in SQL                     sql   substr replace  sql   parameterValue   strPos  strlen  parameterName

User · Answer

You can use     query- gt getSQL      If you are using MySQL you can use Workbench to view running SQL statements  You can also use view the running query from mysql by using the following     SHOW FULL PROCESSLIST  G

User · Answer

You can check the query executed by your app if you log all the queries in mysql   http   dev mysql com doc refman 5 1 en query-log html  there will be more queries not only the one that you are looking for but you can grep for it   but usually - gt getSql    works  Edit   to view all the mysql queries I use   sudo vim  etc mysql my cnf    and add those 2 lines    general log   on general log file    tmp mysql log   and restart mysql

User · Answer

Modified  dsamblas function to work when parameters are date strings like this  2019-01-01  and when there is array passed using IN like    qb- gt expr  - gt in  ps code     activeCodes        So do everything what dsamblas wrote  but replace startQuery with this one or see the differences and add my code   in case he modified something in his function and my version does not have modifications     public function startQuery  sql  array  params   null  array  types   null         if  this- gt isLoggable  sql            if  empty  params                foreach   params as  key  gt  param                     try                        type Type  getType  types  key                         value  type- gt convertToDatabaseValue  param  this- gt dbPlatform                     catch  Exception  e                        if  is array  param                                connect arrays like   A    R    C   for SQL IN                          value         implode         param                               else                            value    param     case when there are date strings                                                           sql   join var export  value  true   explode       sql  2                                     echo  sql        PHP EOL            Did not test much

User · Answer

Doctrine is not sending a  real SQL query  to the database server   it is actually using prepared statements  which means     Sending the statement  for it to be prepared  this is what is returned by  query- gt getSql    And  then  sending the parameters  returned by  query- gt getParameters    and executing the prepared statements   This means there is never a  real  SQL query on the PHP side     so  Doctrine cannot display it

User · Answer

TL DR   qb          your query builder  query    qb- gt getQuery       temporarily enable logging for your query  will also work in prod env   conf    query- gt getEntityManager  - gt getConnection  - gt getConfiguration     backupLogger    conf- gt getSQLLogger     logger   new  Doctrine DBAL Logging DebugStack     conf- gt setSQLLogger  logger      execute query  res    query- gt getResult     conf- gt setSQLLogger  backupLogger     restore logger for other queries  params        query    gt  array pop  logger- gt queries    extract query log details     your other twig params here      return  params    send this to your twig template      in your twig files  use Doctrine s twig helpers filters      show raw query      query sql        doctrine replace query parameters query params     highlighted     query sql        doctrine replace query parameters query params  doctrine pretty query highlight only   true        highlighted and formatted  i e  with tabs and newlines      query sql        doctrine replace query parameters query params  doctrine pretty query      Explanation   The other answers mentioning that Prepared statement are actually  real queries  are right  but they don t answer the obvious asker s expectation    Every developer wants to display a  runnable query  for debugging  or to display it to the user     So  I looked into Symfony profiler s source to see how they do it  The Doctrine part is Doctrine s responsibility so they made a doctrine-bundle to integrate with Symfony  Having a look at the doctrine-bundle Resources views Collector db html twig file  you will find out how they do it  this might change across versions   Interestingly  they created twig filters that we can reuse  see above    For everything to work we need to enable Logging for our query  There are multiple ways to do this and here I use DebugStack which allows to log queries without actually printing them  This also ensure that this will work in production mode if this is what you need     If you need further formatting  you will see that they include some CSS in a style tag  so simply  steal  it       highlight pre   margin  0  white-space  pre-wrap     highlight  keyword     color   8959A8  font-weight  bold     highlight  word        color   222222     highlight  variable    color   916319     highlight  symbol      color   222222     highlight  comment     color   999999     highlight  backtick    color   718C00     highlight  string      color   718C00     highlight  number      color   F5871F  font-weight  bold     highlight  error       color   C82829      Hope  this will help  -

User · Answer

I wrote a simple logger  which can log query with inserted parameters  Installation   composer require cmyker doctrine-sql-logger dev-master   Usage    connection    this- gt getEntityManager  - gt getConnection      logger   new  Cmyker DoctrineSqlLogger Logger  connection    connection- gt getConfiguration  - gt setSQLLogger  logger     some query here echo  logger- gt lastQuery

User · Answer

getSqlQuery   does technically show the whole SQL command  but it s a lot more useful when you can see the parameters as well   echo  q- gt getSqlQuery    foreach   q- gt getFlattenedParams   as  index   gt   param    echo   index   gt   param     To make this pattern more reusable  there s a nice approach described in the comments at Raw SQL from Doctrine Query Object

[php] Doctrine - How to print out the real sql, not just the prepared statement?

Examples related to php

Examples related to mysql

Examples related to orm

Examples related to doctrine

Examples related to doctrine-query