Raw SQL from Doctrine Query Object – Revised

December 7, 2009 at 7:47 pm Christopher Shennan 8 comments

A few months ago I posted an article taking about how to get the raw SQL from a Doctrine Query Object but with the release of Symfony 1.3 and Symfony 1.4 it would appear that the code no longer works. As a result I’ve updated the code to work with Symfony 1.2 – 1.4 and you can find the updated source below:-

function get_raw_sql($query) {
    if(!($query instanceof Doctrine_Query)) {
        throw new sfException('Not an instanse of a Doctrine Query');
    }
 
    $query->limit(0);
    
    if(is_callable(array($query, 'buildSqlQuery'))) {
        $queryString = $query->buildSqlQuery();
        $query_params = $query->getParams();
        $params = $query_params['where'];
    } else {
        $queryString = $query->getSql();
        $params = $query->getParams();
    }
 
    $queryStringParts = split('\?', $queryString);
    $iQC = 0;
 
    $queryString = "";
    
    foreach($params as $param) {
        if(is_numeric($param)) {
            $queryString .= $queryStringParts[$iQC] . $param;
        } elseif(is_bool($param)) {
            $queryString .= $queryStringParts[$iQC] . $param*1;
        } else {
            $queryString .= $queryStringParts[$iQC] . '\'' . $param . '\'';
        }
 
        $iQC++;
   }
   for($iQC;$iQC < count($queryStringParts);$iQC++) {
       $queryString .= $queryStringParts[$iQC];
   }
 
    echo $queryString;
}

I hope that it proves useful.

Symfony doctine, doctrine query object, raw sql, Symfony

About Christopher Shennan

I am a web developer specialising in web driven applications using PHP, MySQL, Symfony and Zend and I am currently working for Line Digital in Edinburgh, Scotland.

Most days I can be found frantically coding away with EuroDance in my ears and consuming what I hope to be a never ending supply of coffee... happy days!

Connect with me via Twitter, Google+ or LinkedIn

function getRawSqlQuery(Doctrine_Query $q) { $queryString = $query->getSqlQuery(); foreach ($query->getFlattenedParams() as $param) { $queryString = join(var_export(is_scalar($param) ? $param : (string) $param, true), explode('?', $queryString, 2)); } return $queryString; }

class MyQueryClass extends Doctrine_Query { public function getRawSql() { $query = $this->getSqlQuery(); foreach ($this->getFlattenedParams() as $param) { $query = join(var_export(is_scalar($param) ? $param : (string) $param, true), explode('?', $query, 2)); } return $query; } }

Leave a comment Trackback

#1 | Written by Stoyan about 3 years ago.

I’d propose the following solution:

This is more generic solution, as it respects all param sections (having, set, join, where). Your solutions works only with the “where” part.

#2 | Written by Stoyan about 3 years ago.

Also, for symfony:
1. create /yourproject/lib/MyQueryClass.php:

Then in yourproject/config/ProjectConfiguration.class.php add the following method:

  public function configureDoctrine(Doctrine_Manager $manager)
  {
    $manager->setAttribute(Doctrine_Core::ATTR_QUERY_CLASS, 'MyQueryClass');
  }

Now you can debug your queries everywhere in the project:

    $query->getRawSql();

#3 | Written by Christopher Shennan about 3 years ago.

Thanks for the update Stoyan.

I have now used both variations in my projects now as one was symfony based (so the MyQueryClass solution worked perfectly) and one was not (so the initial global function version worked well in this instance).
#4 | Written by Japes about 3 years ago.

um, unless I’ve misunderstood the question, Doctrine has that function:
$q->getSqlQuery();
It’s frequently in the docs to expose the underlying SQL generated by the DQL query object, and its output can be cut and pasted into sql admin. Hope this helps!
#5 | Written by Christopher Shennan about 3 years ago.
Hi Japes,

The example I’ve used already incorporates the $q->getSqlQuery().

getSqlQuery() works out the box with no problems for only the most basic queries, but as soon as you start parameterising your queries you have to substitute all the ? with the values you need. i.e. If you write

$query = Doctrine::getTable('News')->createQuery(); echo $query->getSqlQuery();

then you will get:

SELECT id, title, description FROM news

However if you write:-

$query = Doctrine::getTable('News') ->createQuery() ->addWhere('is_active = ?', true); echo $query->getSqlQuery();

Then you get:-

SELECT id, title, description FROM news WHERE (is_active = ?)

This not ideal as you cannot simply cut and paste the SQL to debug it and substitution is a pain especially if you have a lot of parameters and are repeatedly testing the query as you have to make the substitutions each time.

This article was written to give you an easy way to substitute all the ? with their appropriate values to allow you to copy and paste the SQL directly for easy debugging.

Hope this helps clear things up.

Chris
#6 | Written by Jorge Loria about 2 years ago.

Excellent!! works like a charm!!
thanks!!!
#7 | Written by Debra about 2 years ago.

At last, somoene comes up with the “right” answer!
#8 | Written by Jayden about 2 years ago.

Hi Christopher, thanks for sharing this. Helped me solved the cockups with my SQL.

No trackbacks yet.

Christopher Shennan's Blog

Chris Shennan's Tweets

Recent Posts

Archives

Categories

Sites of Interest

Raw SQL from Doctrine Query Object – Revised

About Christopher Shennan

Leave a Comment