Raw SQL from Doctrine Query Object – Revised

December 7, 2009 at 7:47 pm Christopher Shennan 3 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

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; } }

Comments

Leave a comment Trackback

Written by Stoyan about 4 weeks 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.

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();

Written by Christopher Shennan about 3 weeks 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).

Comment Pages:

Christopher Shennan’s Blog

Raw SQL from Doctrine Query Object – Revised

Comments

Leave a Comment

Archives

Categories

Friends

My Websites