MySQL 如何在 extbase 中调试查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5075296/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to debug a query in extbase?
提问by pduersteler
$query = $this->createQuery();
return $query->matching($query->like('linker', "$linkerKey=$linkerValue"))
->setOrderings(array('crdate' => $ordering))
->execute();
How can i debug such a generated query in extbase? When creating the same query again (but without the execute() ) and trying to display it with var_dump or the internal t3lib_div::debug i just receive a blank page.
如何在 extbase 中调试此类生成的查询？当再次创建相同的查询（但没有 execute() ）并尝试使用 var_dump 或内部 t3lib_div::debug 显示它时，我只会收到一个空白页面。
采纳答案by xandi
$query = $this->createQuery();
$result = $query->matching($query->like('linker', "$linkerKey=$linkerValue"))
->setOrderings(array('crdate' => $ordering))
->execute();
$GLOBALS['TYPO3_DB']->debugOutput = true;
return $result;
回答by pgampe
In version 8.7 LTS, another way needs to be taken:
在8.7 LTS版本中，需要采取另一种方式：
$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());
回答by j4k3
This information is outdated and deprecated in TYPO3 8.7. Refer to @pgampe 's answer below for a more current way to debug extbase queries.
此信息已过时并在 TYPO3 8.7 中弃用。有关调试 extbase 查询的最新方法，请参阅下面的 @pgampe 的答案。
Extbase now has a QueryParser for that. In your repository method, right before returning the executed query, insert:
Extbase 现在为此提供了一个 QueryParser。在您的存储库方法中，在返回执行的查询之前，插入：
$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 query parts, split by SQL keywords, e.g.:
结果是查询部分的表视图，按 SQL 关键字拆分，例如：
Keep in mind that the QueryResult that your Repository returns may still be different from the SQL query result. Extbase uses the PropertyMapper to try to convert every result row into an ExtbaseObject. If the PropertyMapper is misconfigured or the row contains data that cannot be converted to the data types according to the configuration, these Objects will silently be skipped.
请记住，您的 Repository 返回的 QueryResult 可能仍与 SQL 查询结果不同。Extbase 使用 PropertyMapper 尝试将每个结果行转换为 ExtbaseObject。如果 PropertyMapper 配置错误或行包含无法根据配置转换为数据类型的数据，这些对象将被静默跳过。
回答by Urs
This hack to extbase is dirty, but useful:
这个对 extbase 的 hack 很脏，但很有用：
In typo3/sysext/extbase/Classes/Persistence/Storage/Typo3DbBackend.php edit the method buildQuery(array $sql) before the return statement, add:
在typo3/sysext/extbase/Classes/Persistence/Storage/Typo3DbBackend.php 中，在return 语句之前编辑方法buildQuery(array $sql)，添加：
t3lib_div::debug($statement, 'SQL Query Extbase');
Remove after use, and don't forget this will affect everything that runs on extbase, so use in dev environment only
使用后删除，不要忘记这会影响在 extbase 上运行的所有内容，因此只能在开发环境中使用
Source: http://sancer-media.net/2011/extbase-schneller-mysql-debug.html
来源：http: //sancer-media.net/2011/extbase-schneller-mysql-debug.html
回答by simplychrislike
This works as long as $GLOBALS['TYPO3_DB'] is supported. It will show you the complete build SQL query.
只要 $GLOBALS['TYPO3_DB'] 受支持，它就可以工作。它将向您展示完整的构建 SQL 查询。
/**
* @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 that function you can do something like this in your controller:
因此，使用该功能，您可以在控制器中执行以下操作：
$all = $this->repository->findAll();
$this->repository->debugQuery($all);
回答by David
Here I post a method you can enter for debugging in any class, making a trait of it would be surely possible too. Authorship and source is mentioned in the comment, Usage too:
在这里，我发布了一个您可以在任何类中输入以进行调试的方法，当然也可以使用它的特征。评论中提到了作者和来源，用法也是：
/**
* 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>' . ' ';
} else {
$sql_formatted .= '<br/> ';
$sql_formatted .= '<b>' . strtoupper(trim($word)) . '</b>' . ' ';
}
$prev_word = $word_trimmed;
$word = "";
} else {
$sql_formatted .= trim($word) . ' ';
$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;
}
}
回答by Ghanshyam Gohel
In v6.2x or later, you can debug result object in extBase like:
在 v6.2x 或更高版本中，您可以在 extBase 中调试结果对象，例如：
In repository:
在存储库中：
return $query->execute(true); // "true" will return array result
返回 $query->execute(true); // "true" 将返回数组结果
Or also you can debug result object in Controller:
或者你也可以在 Controller 中调试结果对象：
$resultObject = $this->yourRepository->findAll();
\TYPO3\CMS\Extbase\Utility\DebuggerUtility::var_dump($resultObject);
$resultObject = $this->yourRepository->findAll();
\TYPO3\CMS\Extbase\Utility\DebuggerUtility::var_dump($resultObject);
回答by Benno
An easy way without changing any Typo3 core code and not mentioned in any forum so far is using the php "serialize()" method:
一种无需更改任何 Typo3 核心代码且迄今未在任何论坛中提及的简单方法是使用 php "serialize()" 方法：
$result = $query->execute();
echo (serialize($result));
In the result object you find the SQL query (look for "statement;" ...)
在结果对象中，您可以找到 SQL 查询（查找“statement;”...）
