Debugging Database Queries in Extbase in Typo3 6.2 CMS

If you are using extbase to develop typo3 extentions you can use the Query object to build queries without having to write SQL. The only problem is that if you want to see the actual SQL that is being executed it is hidden from view.

After searching the web I found this post: http://blog.undkonsorten.com/sql-queries-debuggen-in-typo3 which provides code for a function which outputs the SQL. I’ll repeat it here for reference.

NB: here I am using the cool component jdorn/sql-formatter, to format the SQL for output. You can remove this and it will just return the unformatted SQL.

Apparently, the $queryResult->toArray(); is the part that makes the SQL available.

/**
* Debugs a SQL query from a QueryResult
*
* @param TYPO3CMSExtbasePersistenceGenericQueryResult $queryResult
* @param boolean $explainOutput
* @return void
*/
public function debugQuery(TYPO3CMSExtbasePersistenceGenericQueryResult $queryResult, $explainOutput = FALSE){
    $GLOBALS['TYPO3_DB']->debugOuput = 2;
    if($explainOutput){
        $GLOBALS['TYPO3_DB']->explainOutput = true;
    }
    $GLOBALS['TYPO3_DB']->store_lastBuiltQuery = true;
    $queryResult->toArray();
    echo SqlFormatter::format($GLOBALS['TYPO3_DB']->debug_lastBuiltQuery, false);

    $GLOBALS['TYPO3_DB']->store_lastBuiltQuery = false;
    $GLOBALS['TYPO3_DB']->explainOutput = false;
    $GLOBALS['TYPO3_DB']->debugOuput = false;
}