How to debug request in extbase?

Question

$query = $this->createQuery(); return $query->matching($query->like('linker', "$linkerKey=$linkerValue")) ->setOrderings(array('crdate' => $ordering)) ->execute(); 

How can I debug such a generated request in extbase? When I re-create the same request (but without execute ()) and try to display it using var_dump or internal t3lib_div :: debug, I just get a blank page.

Answer 1

 $query = $this->createQuery(); $result = $query->matching($query->like('linker', "$linkerKey=$linkerValue")) ->setOrderings(array('crdate' => $ordering)) ->execute(); $GLOBALS['TYPO3_DB']->debugOutput = true; return $result; 

Answer 2

In version 8.7 LTS , another path must be followed:

  $queryParser = $this->objectManager->get(\TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser::class); \TYPO3\CMS\Extbase\Utility\DebuggerUtility::var_dump($queryParser->convertQueryToDoctrineQueryBuilder($query)->getSQL()); \TYPO3\CMS\Extbase\Utility\DebuggerUtility::var_dump($queryParser->convertQueryToDoctrineQueryBuilder($query)->getParameters()); 

Answer 3

This information is deprecated and deprecated in TYPO3 8.7. See @pgampe answer below for a more accurate way to debug extbase requests.

Extbase now has QueryParser. In your repository method, before returning the completed request, insert:

  $parser = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance('TYPO3\\CMS\\Extbase\\Persistence\\Generic\\Storage\\Typo3DbQueryParser'); $queryParts = $parser->parseQuery($query); \TYPO3\CMS\Core\Utility\DebugUtility::debug($queryParts, 'query'); 

The result is a table view of the parts of the query, separated by SQL keywords, for example:

Keep in mind that the QueryResult returned by the repository may still be different from the result of the SQL query. Extbase uses PropertyMapper to try to convert each row of results into an ExtbaseObject. If the PropertyMapper is not configured correctly or the row contains data that cannot be converted to data types according to the configuration, these objects will be silently skipped.

Answer 4

This hack for extbase is dirty but useful:

In typo3 / sysext / extbase / Classes / Persistence / Storage / Typo3DbBackend.php edit the buildQuery method ($ sql array) before the return statement, add:

 t3lib_div::debug($statement, 'SQL Query Extbase'); 

Delete after use, and do not forget that this will affect everything that works on extbase, so use only in the dev environment

Source: http://sancer-media.net/2011//extbase-schneller-mysql-debug.html

Answer 5

In v6.2x or later, you can debug the result object in extBase, for example:

In the repository:

return $ query-> execute (true); // "true" will return the result of the array

Or you can debug the result object in the controller:

$ resultObject = $ this-> yourRepository-> findAll ();
\ TYPO3 \ CMS \ Extbase \ Utility \ DebuggerUtility :: var_dump ($ resultObject);

Answer 6

This works as long as $ GLOBALS ['TYPO3_DB'] is supported. It will show you the full SQL build request.

 /** * @param \TYPO3\CMS\Extbase\Persistence\QueryResultInterface $queryResult * @param bool $explainOutput * @return void */ public function debugQuery( \TYPO3\CMS\Extbase\Persistence\QueryResultInterface $queryResult, $explainOutput = false ) { $GLOBALS['TYPO3_DB']->debugOuput = 2; if ($explainOutput) { $GLOBALS['TYPO3_DB']->explainOutput = true; } $GLOBALS['TYPO3_DB']->store_lastBuiltQuery = true; $queryResult->toArray(); \TYPO3\CMS\Extbase\Utility\DebuggerUtility::var_dump( $GLOBALS['TYPO3_DB']->debug_lastBuiltQuery ); $GLOBALS['TYPO3_DB']->store_lastBuiltQuery = false; $GLOBALS['TYPO3_DB']->explainOutput = false; $GLOBALS['TYPO3_DB']->debugOuput = false; } 

So, with this function, you can do something like this in your controller:

 $all = $this->repository->findAll(); $this->repository->debugQuery($all); 

Answer 7

An easy way without changing the Typo3 kernel code and not mentioned in any forum still uses the php serialize () method:

 $result = $query->execute(); echo (serialize($result)); 

In the result object you will find the SQL query (find "statement;" ...)

Answer 8

Here I lay out a method that you can enter for debugging in any class, so its features will probably be possible. The authorship and source are mentioned in the commentary. Usage also:

 /** * Render the generated SQL of a query in TYPO3 8 * * @author wp_bube https://www.typo3.net/forum/user-profil/benutzer/zeige/benutzer/wp-bube/ * @src https://www.typo3.net/forum/thematik/zeige/thema/125747/ * * Usage: $this->debugQuery($query); * * @param \TYPO3\CMS\Extbase\Persistence\QueryInterface $query * @param bool $format * @param bool $exit */ private function debugQuery($query, $format = true, $exit = true) { function getFormattedSQL($sql_raw) { if (empty($sql_raw) || !is_string($sql_raw)) { return false; } $sql_reserved_all = array( 'ACCESSIBLE', 'ACTION', 'ADD', 'AFTER', 'AGAINST', 'AGGREGATE', 'ALGORITHM', 'ALL', 'ALTER', 'ANALYSE', 'ANALYZE', 'AND', 'AS', 'ASC', 'AUTOCOMMIT', 'AUTO_INCREMENT', 'AVG_ROW_LENGTH', 'BACKUP', 'BEGIN', 'BETWEEN', 'BINLOG', 'BOTH', 'BY', 'CASCADE', 'CASE', 'CHANGE', 'CHANGED', 'CHARSET', 'CHECK', 'CHECKSUM', 'COLLATE', 'COLLATION', 'COLUMN', 'COLUMNS', 'COMMENT', 'COMMIT', 'COMMITTED', 'COMPRESSED', 'CONCURRENT', 'CONSTRAINT', 'CONTAINS', 'CONVERT', 'CREATE', 'CROSS', 'CURRENT_TIMESTAMP', 'DATABASE', 'DATABASES', 'DAY', 'DAY_HOUR', 'DAY_MINUTE', 'DAY_SECOND', 'DEFINER', 'DELAYED', 'DELAY_KEY_WRITE', 'DELETE', 'DESC', 'DESCRIBE', 'DETERMINISTIC', 'DISTINCT', 'DISTINCTROW', 'DIV', 'DO', 'DROP', 'DUMPFILE', 'DUPLICATE', 'DYNAMIC', 'ELSE', 'ENCLOSED', 'END', 'ENGINE', 'ENGINES', 'ESCAPE', 'ESCAPED', 'EVENTS', 'EXECUTE', 'EXISTS', 'EXPLAIN', 'EXTENDED', 'FAST', 'FIELDS', 'FILE', 'FIRST', 'FIXED', 'FLUSH', 'FOR', 'FORCE', 'FOREIGN', 'FROM', 'FULL', 'FULLTEXT', 'FUNCTION', 'GEMINI', 'GEMINI_SPIN_RETRIES', 'GLOBAL', 'GRANT', 'GRANTS', 'GROUP', 'HAVING', 'HEAP', 'HIGH_PRIORITY', 'HOSTS', 'HOUR', 'HOUR_MINUTE', 'HOUR_SECOND', 'IDENTIFIED', 'IF', 'IGNORE', 'IN', 'INDEX', 'INDEXES', 'INFILE', 'INNER', 'INSERT', 'INSERT_ID', 'INSERT_METHOD', 'INTERVAL', 'INTO', 'INVOKER', 'IS', 'ISOLATION', 'JOIN', 'KEY', 'KEYS', 'KILL', 'LAST_INSERT_ID', 'LEADING', 'LEFT', 'LEVEL', 'LIKE', 'LIMIT', 'LINEAR', 'LINES', 'LOAD', 'LOCAL', 'LOCK', 'LOCKS', 'LOGS', 'LOW_PRIORITY', 'MARIA', 'MASTER', 'MASTER_CONNECT_RETRY', 'MASTER_HOST', 'MASTER_LOG_FILE', 'MASTER_LOG_POS', 'MASTER_PASSWORD', 'MASTER_PORT', 'MASTER_USER', 'MATCH', 'MAX_CONNECTIONS_PER_HOUR', 'MAX_QUERIES_PER_HOUR', 'MAX_ROWS', 'MAX_UPDATES_PER_HOUR', 'MAX_USER_CONNECTIONS', 'MEDIUM', 'MERGE', 'MINUTE', 'MINUTE_SECOND', 'MIN_ROWS', 'MODE', 'MODIFY', 'MONTH', 'MRG_MYISAM', 'MYISAM', 'NAMES', 'NATURAL', 'NOT', 'NULL', 'OFFSET', 'ON', 'OPEN', 'OPTIMIZE', 'OPTION', 'OPTIONALLY', 'OR', 'ORDER', 'OUTER', 'OUTFILE', 'PACK_KEYS', 'PAGE', 'PARTIAL', 'PARTITION', 'PARTITIONS', 'PASSWORD', 'PRIMARY', 'PRIVILEGES', 'PROCEDURE', 'PROCESS', 'PROCESSLIST', 'PURGE', 'QUICK', 'RAID0', 'RAID_CHUNKS', 'RAID_CHUNKSIZE', 'RAID_TYPE', 'RANGE', 'READ', 'READ_ONLY', 'READ_WRITE', 'REFERENCES', 'REGEXP', 'RELOAD', 'RENAME', 'REPAIR', 'REPEATABLE', 'REPLACE', 'REPLICATION', 'RESET', 'RESTORE', 'RESTRICT', 'RETURN', 'RETURNS', 'REVOKE', 'RIGHT', 'RLIKE', 'ROLLBACK', 'ROW', 'ROWS', 'ROW_FORMAT', 'SECOND', 'SECURITY', 'SELECT', 'SEPARATOR', 'SERIALIZABLE', 'SESSION', 'SET', 'SHARE', 'SHOW', 'SHUTDOWN', 'SLAVE', 'SONAME', 'SOUNDS', 'SQL', 'SQL_AUTO_IS_NULL', 'SQL_BIG_RESULT', 'SQL_BIG_SELECTS', 'SQL_BIG_TABLES', 'SQL_BUFFER_RESULT', 'SQL_CACHE', 'SQL_CALC_FOUND_ROWS', 'SQL_LOG_BIN', 'SQL_LOG_OFF', 'SQL_LOG_UPDATE', 'SQL_LOW_PRIORITY_UPDATES', 'SQL_MAX_JOIN_SIZE', 'SQL_NO_CACHE', 'SQL_QUOTE_SHOW_CREATE', 'SQL_SAFE_UPDATES', 'SQL_SELECT_LIMIT', 'SQL_SLAVE_SKIP_COUNTER', 'SQL_SMALL_RESULT', 'SQL_WARNINGS', 'START', 'STARTING', 'STATUS', 'STOP', 'STORAGE', 'STRAIGHT_JOIN', 'STRING', 'STRIPED', 'SUPER', 'TABLE', 'TABLES', 'TEMPORARY', 'TERMINATED', 'THEN', 'TO', 'TRAILING', 'TRANSACTIONAL', 'TRUNCATE', 'TYPE', 'TYPES', 'UNCOMMITTED', 'UNION', 'UNIQUE', 'UNLOCK', 'UPDATE', 'USAGE', 'USE', 'USING', 'VALUES', 'VARIABLES', 'VIEW', 'WHEN', 'WHERE', 'WITH', 'WORK', 'WRITE', 'XOR', 'YEAR_MONTH' ); $sql_skip_reserved_words = array('AS', 'ON', 'USING'); $sql_special_reserved_words = array('(', ')'); $sql_raw = str_replace("\n", " ", $sql_raw); $sql_formatted = ""; $prev_word = ""; $word = ""; for ($i = 0, $j = strlen($sql_raw); $i < $j; $i++) { $word .= $sql_raw[$i]; $word_trimmed = trim($word); if ($sql_raw[$i] == " " || in_array($sql_raw[$i], $sql_special_reserved_words)) { $word_trimmed = trim($word); $trimmed_special = false; if (in_array($sql_raw[$i], $sql_special_reserved_words)) { $word_trimmed = substr($word_trimmed, 0, -1); $trimmed_special = true; } $word_trimmed = strtoupper($word_trimmed); if (in_array($word_trimmed, $sql_reserved_all) && !in_array($word_trimmed, $sql_skip_reserved_words)) { if (in_array($prev_word, $sql_reserved_all)) { $sql_formatted .= '<b>' . strtoupper(trim($word)) . '</b>' . '&nbsp;'; } else { $sql_formatted .= '<br/>&nbsp;'; $sql_formatted .= '<b>' . strtoupper(trim($word)) . '</b>' . '&nbsp;'; } $prev_word = $word_trimmed; $word = ""; } else { $sql_formatted .= trim($word) . '&nbsp;'; $prev_word = $word_trimmed; $word = ""; } } } $sql_formatted .= trim($word); return $sql_formatted; } $queryParser = $this->objectManager->get(\TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser::class); $doctrineQueryBuilder = $queryParser->convertQueryToDoctrineQueryBuilder($query); $preparedStatement = $doctrineQueryBuilder->getSQL(); $parameters = $doctrineQueryBuilder->getParameters(); $stringParams = []; foreach ($parameters as $key => $parameter) { $stringParams[':' . $key] = $parameter; } $statement = strtr($preparedStatement, $stringParams); if ($format) { echo '<code>' . getFormattedSQL($statement) . '</code>'; } else { echo $statement; } if ($exit) { exit; } } SQL_LOG_UPDATE ',' SQL_LOW_PRIORITY_UPDATES ',' SQL_MAX_JOIN_SIZE ',' SQL_NO_CACHE ',' SQL_QUOTE_SHOW_CREATE ',' SQL_SAFE_UPDATES ',' SQL_SELECT_LIMIT ',' SQL_SLAVE_SKIP_COUNTER ',' SQL_SMALL_RESULT ',' SQL_WARNINGS ',' START ' /** * Render the generated SQL of a query in TYPO3 8 * * @author wp_bube https://www.typo3.net/forum/user-profil/benutzer/zeige/benutzer/wp-bube/ * @src https://www.typo3.net/forum/thematik/zeige/thema/125747/ * * Usage: $this->debugQuery($query); * * @param \TYPO3\CMS\Extbase\Persistence\QueryInterface $query * @param bool $format * @param bool $exit */ private function debugQuery($query, $format = true, $exit = true) { function getFormattedSQL($sql_raw) { if (empty($sql_raw) || !is_string($sql_raw)) { return false; } $sql_reserved_all = array( 'ACCESSIBLE', 'ACTION', 'ADD', 'AFTER', 'AGAINST', 'AGGREGATE', 'ALGORITHM', 'ALL', 'ALTER', 'ANALYSE', 'ANALYZE', 'AND', 'AS', 'ASC', 'AUTOCOMMIT', 'AUTO_INCREMENT', 'AVG_ROW_LENGTH', 'BACKUP', 'BEGIN', 'BETWEEN', 'BINLOG', 'BOTH', 'BY', 'CASCADE', 'CASE', 'CHANGE', 'CHANGED', 'CHARSET', 'CHECK', 'CHECKSUM', 'COLLATE', 'COLLATION', 'COLUMN', 'COLUMNS', 'COMMENT', 'COMMIT', 'COMMITTED', 'COMPRESSED', 'CONCURRENT', 'CONSTRAINT', 'CONTAINS', 'CONVERT', 'CREATE', 'CROSS', 'CURRENT_TIMESTAMP', 'DATABASE', 'DATABASES', 'DAY', 'DAY_HOUR', 'DAY_MINUTE', 'DAY_SECOND', 'DEFINER', 'DELAYED', 'DELAY_KEY_WRITE', 'DELETE', 'DESC', 'DESCRIBE', 'DETERMINISTIC', 'DISTINCT', 'DISTINCTROW', 'DIV', 'DO', 'DROP', 'DUMPFILE', 'DUPLICATE', 'DYNAMIC', 'ELSE', 'ENCLOSED', 'END', 'ENGINE', 'ENGINES', 'ESCAPE', 'ESCAPED', 'EVENTS', 'EXECUTE', 'EXISTS', 'EXPLAIN', 'EXTENDED', 'FAST', 'FIELDS', 'FILE', 'FIRST', 'FIXED', 'FLUSH', 'FOR', 'FORCE', 'FOREIGN', 'FROM', 'FULL', 'FULLTEXT', 'FUNCTION', 'GEMINI', 'GEMINI_SPIN_RETRIES', 'GLOBAL', 'GRANT', 'GRANTS', 'GROUP', 'HAVING', 'HEAP', 'HIGH_PRIORITY', 'HOSTS', 'HOUR', 'HOUR_MINUTE', 'HOUR_SECOND', 'IDENTIFIED', 'IF', 'IGNORE', 'IN', 'INDEX', 'INDEXES', 'INFILE', 'INNER', 'INSERT', 'INSERT_ID', 'INSERT_METHOD', 'INTERVAL', 'INTO', 'INVOKER', 'IS', 'ISOLATION', 'JOIN', 'KEY', 'KEYS', 'KILL', 'LAST_INSERT_ID', 'LEADING', 'LEFT', 'LEVEL', 'LIKE', 'LIMIT', 'LINEAR', 'LINES', 'LOAD', 'LOCAL', 'LOCK', 'LOCKS', 'LOGS', 'LOW_PRIORITY', 'MARIA', 'MASTER', 'MASTER_CONNECT_RETRY', 'MASTER_HOST', 'MASTER_LOG_FILE', 'MASTER_LOG_POS', 'MASTER_PASSWORD', 'MASTER_PORT', 'MASTER_USER', 'MATCH', 'MAX_CONNECTIONS_PER_HOUR', 'MAX_QUERIES_PER_HOUR', 'MAX_ROWS', 'MAX_UPDATES_PER_HOUR', 'MAX_USER_CONNECTIONS', 'MEDIUM', 'MERGE', 'MINUTE', 'MINUTE_SECOND', 'MIN_ROWS', 'MODE', 'MODIFY', 'MONTH', 'MRG_MYISAM', 'MYISAM', 'NAMES', 'NATURAL', 'NOT', 'NULL', 'OFFSET', 'ON', 'OPEN', 'OPTIMIZE', 'OPTION', 'OPTIONALLY', 'OR', 'ORDER', 'OUTER', 'OUTFILE', 'PACK_KEYS', 'PAGE', 'PARTIAL', 'PARTITION', 'PARTITIONS', 'PASSWORD', 'PRIMARY', 'PRIVILEGES', 'PROCEDURE', 'PROCESS', 'PROCESSLIST', 'PURGE', 'QUICK', 'RAID0', 'RAID_CHUNKS', 'RAID_CHUNKSIZE', 'RAID_TYPE', 'RANGE', 'READ', 'READ_ONLY', 'READ_WRITE', 'REFERENCES', 'REGEXP', 'RELOAD', 'RENAME', 'REPAIR', 'REPEATABLE', 'REPLACE', 'REPLICATION', 'RESET', 'RESTORE', 'RESTRICT', 'RETURN', 'RETURNS', 'REVOKE', 'RIGHT', 'RLIKE', 'ROLLBACK', 'ROW', 'ROWS', 'ROW_FORMAT', 'SECOND', 'SECURITY', 'SELECT', 'SEPARATOR', 'SERIALIZABLE', 'SESSION', 'SET', 'SHARE', 'SHOW', 'SHUTDOWN', 'SLAVE', 'SONAME', 'SOUNDS', 'SQL', 'SQL_AUTO_IS_NULL', 'SQL_BIG_RESULT', 'SQL_BIG_SELECTS', 'SQL_BIG_TABLES', 'SQL_BUFFER_RESULT', 'SQL_CACHE', 'SQL_CALC_FOUND_ROWS', 'SQL_LOG_BIN', 'SQL_LOG_OFF', 'SQL_LOG_UPDATE', 'SQL_LOW_PRIORITY_UPDATES', 'SQL_MAX_JOIN_SIZE', 'SQL_NO_CACHE', 'SQL_QUOTE_SHOW_CREATE', 'SQL_SAFE_UPDATES', 'SQL_SELECT_LIMIT', 'SQL_SLAVE_SKIP_COUNTER', 'SQL_SMALL_RESULT', 'SQL_WARNINGS', 'START', 'STARTING', 'STATUS', 'STOP', 'STORAGE', 'STRAIGHT_JOIN', 'STRING', 'STRIPED', 'SUPER', 'TABLE', 'TABLES', 'TEMPORARY', 'TERMINATED', 'THEN', 'TO', 'TRAILING', 'TRANSACTIONAL', 'TRUNCATE', 'TYPE', 'TYPES', 'UNCOMMITTED', 'UNION', 'UNIQUE', 'UNLOCK', 'UPDATE', 'USAGE', 'USE', 'USING', 'VALUES', 'VARIABLES', 'VIEW', 'WHEN', 'WHERE', 'WITH', 'WORK', 'WRITE', 'XOR', 'YEAR_MONTH' ); $sql_skip_reserved_words = array('AS', 'ON', 'USING'); $sql_special_reserved_words = array('(', ')'); $sql_raw = str_replace("\n", " ", $sql_raw); $sql_formatted = ""; $prev_word = ""; $word = ""; for ($i = 0, $j = strlen($sql_raw); $i < $j; $i++) { $word .= $sql_raw[$i]; $word_trimmed = trim($word); if ($sql_raw[$i] == " " || in_array($sql_raw[$i], $sql_special_reserved_words)) { $word_trimmed = trim($word); $trimmed_special = false; if (in_array($sql_raw[$i], $sql_special_reserved_words)) { $word_trimmed = substr($word_trimmed, 0, -1); $trimmed_special = true; } $word_trimmed = strtoupper($word_trimmed); if (in_array($word_trimmed, $sql_reserved_all) && !in_array($word_trimmed, $sql_skip_reserved_words)) { if (in_array($prev_word, $sql_reserved_all)) { $sql_formatted .= '<b>' . strtoupper(trim($word)) . '</b>' . '&nbsp;'; } else { $sql_formatted .= '<br/>&nbsp;'; $sql_formatted .= '<b>' . strtoupper(trim($word)) . '</b>' . '&nbsp;'; } $prev_word = $word_trimmed; $word = ""; } else { $sql_formatted .= trim($word) . '&nbsp;'; $prev_word = $word_trimmed; $word = ""; } } } $sql_formatted .= trim($word); return $sql_formatted; } $queryParser = $this->objectManager->get(\TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser::class); $doctrineQueryBuilder = $queryParser->convertQueryToDoctrineQueryBuilder($query); $preparedStatement = $doctrineQueryBuilder->getSQL(); $parameters = $doctrineQueryBuilder->getParameters(); $stringParams = []; foreach ($parameters as $key => $parameter) { $stringParams[':' . $key] = $parameter; } $statement = strtr($preparedStatement, $stringParams); if ($format) { echo '<code>' . getFormattedSQL($statement) . '</code>'; } else { echo $statement; } if ($exit) { exit; } }