Article

Raw SQL from a Doctrine Query Object

This has been updated to work with Symfony 1.3/1.4 - Please go to the updated article: Raw SQL from Doctrine Query Object – Revised

As my use of Symfony is increasing I find myself frustrated that there doesn't appear to be an easy way to get the raw SQL from a doctrine query object so I can simply output it and paste it into phpMyAdmin to debug problems with the SQL. I may be wrong about this but as yet I have not found a built in function to perform this operation.

Sure I am able to output the query with the "?" indicating where the necessary values are to go and I can also easily output the array of query parameters so I can substitute these manually but this takes time and it would be much much simpler and quicker to just output the whole query with the substitution already done so it's a simply copy and paste of the raw SQL from the doctrine query object straight into phpMyAdmin.

I have wrote a quick function to do this and I had meant to post this for a while but I just have not had the chance to get round to it until now.

The code for this is can be found below:-

function get_raw_sql($query) {
    $query->limit(0);

    $queryStringParts = split('\?', $query->getSQL());
    $iQC = 0;
    $queryString = "";

    foreach($query->getParams() 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;
}

In my case this is a global function but it could very well be written as a static function in a debug class.

$query is a Doctrine Query object and this function iterates round the parameters for the query string and replaces each instance of a "?" with the corresponding variable. It has a crude (but fairly effective so far) method of determine whether it's a string, number or boolean when performing this substitution and so far this works well in all the cases I've used it.

The $query->limit(0); is very important as the $query->getSQL() function does not work properly in this context if a limit is specified on the query so as we are wanting to debug the SQL and a majority of the time we are not concerned with the limit I have opted to remove this from this routine.

Hopefully this will allow you to save some time and effort trying to get the raw SQL from your doctrine query objects and I would appreciated any comments, thoughts or ideas you may have for improvement.

me.jpeg

I am a web developer specialising in web driven applications using PHP, MySQL, Symfony and Zend and I am currently working for the The Drum in Glasgow, 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!

Want more like this?

Keep up-to-date with latest news from Inside the Agency using any of the following services

Advertisement

Digital Ocean

Chris's Reading List

www.thoughtworks.com

BFF @ SoundCloud

Our friends at SoundCloud embarked on a journey a few years ago that changed their system and moved it from a monolithic Rails application towards an ecosystem of microservices. Read more

The Drum

The Drum

Google'€™s AMP is bringing some much needed speed to mobile but the transition won'€™t be easy for publishers

A week after launching, the Google-led Accelerated Mobile Pages (AMP) initiative has raised more questions than answers for publishers... Read more

SitePoint

SitePoint

Quick Tip: How to Permanently Change SQL Mode in MySQL

MySQL 5.7 introduced some awkward changes for older codebases and tends to break apps. Here's how to temporarily (or permanently) lower the strictness level Read more

SitePoint

SitePoint

How to Build Your Own Dependency Injection Container

Andrew Carter builds a Dependency Injection container from scratch, showing you how that rocket science is really just fireworks strapped to lego figures! Read more

AuthorityLabs

AuthorityLabs

13 Reasons Your Organic Traffic is in Decline | It's Not a Penalty

The landscape of search engine optimization has changed drastically in the last 3-4 years. Even as it changes, a lot stays the same. It has been very challenging to see a multitude of friends and colleagues approach me with problems that often seem beyond repair with clients that they have worked with for years. Read more

TechCrunch

TechCrunch

WordPress Sites Now Support Google’s AMP To Make Mobile Pages Load Much Faster

Google has some big plans when it comes to making the web faster on your mobile phone. The company just added AMP-enabled pages in its mobile search results... Read more