CODEWITHSUNDEEP
sqltypo3extbase
alphanyx
alphanyx

Reputation: 1687

Extbase - get created sql from query

i want to get some database tables from my typo3 extensions. The Extension is based on extbase.

The query always returns nothing but the data exists

I've tried this:

$query = $this->createQuery();
$query->statement('SELECT * FROM `my_table`
    WHERE field = ? ORDER BY date DESC LIMIT 1',
    array($condition));

$results = $query->execute();

and this:

$query = $this->createQuery();

$query->matching($query->equals('field', $condition));
$query->setOrderings(array('date' => Tx_Extbase_Persistence_QueryInterface::ORDER_DESCENDING));
$query->setLimit(1);

$results = $query->execute();

both returns null as result.

Is it possible to get the sql that the class creates to look where the bug is?

I've looked in some extbase persistent classes but didn't find a clue

EDIT: For those who are interested.. i found a "solution".

If you create the query with the statement() method, you can print the query with this function

echo $query->getStatement()->getStatement();

It doesn't replace the placeholder. But you can get the Variables with this method

var_dump($query->getStatement()->getBoundVariables());

Thats the best Solution that i found, without editing the extbase extenstions

Upvotes: 10

Views: 19700

Answers (8)

Webian.it
Webian.it

Reputation: 450

In TYPO3 6.2 you can use Extbase DebuggerUtility to debug the query.

Add this code before $query->execute():

$queryParser = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance('TYPO3\\CMS\\Extbase\\Persistence\\Generic\\Storage\\Typo3DbQueryParser');
\TYPO3\CMS\Extbase\Utility\DebuggerUtility::var_dump($queryParser->parseQuery($query));

For TYPO3 8.7+ use this code instead:

$queryParser = \TYPO3\CMS\Core\Utility\GeneralUtilityGeneralUtility::makeInstance(\TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser::class);
$doctrineQueryBuilder = $queryParser->convertQueryToDoctrineQueryBuilder($query);
$doctrineQueryBuilderSQL = $doctrineQueryBuilder->getSQL();
$doctrineQueryBuilderParameters = $doctrineQueryBuilder->getParameters();

Upvotes: 7

biesior
biesior

Reputation: 55798

Check this snippet, although it's not very comfortable in use it helps a lot:

in general you need this code at the end of the buildQuery(array $sql) method (*) - right before return $statement;

if (in_array("your_table_name", $sql['tables'])) {
    var_dump($statement);
    print_r($statement);
}

(*) Class file:

  • TYPO3 ver.: 4.x: typo3/sysext/extbase/Classes/Persistence/Storage/Typo3DbBackend.php
  • TYPO3 ver.: 6.x: typo3/sysext/extbase/Classes/Persistence/Generic/Storage/Typo3DbBackend.php

In 6.2.x ...

You can try within \TYPO3\CMS\Core\Database\DatabaseConnection::exec_SELECTquery method, just add the condition after fetching the $query, like (trim is important!):

public function exec_SELECTquery($select_fields, $from_table, $where_clause, $groupBy = '', $orderBy = '', $limit = '') {
    $query = $this->SELECTquery($select_fields, $from_table, $where_clause, $groupBy, $orderBy, $limit);

    if (trim($from_table) == 'fe_users') {
        DebuggerUtility::var_dump($query);
    }

// rest of method

Upvotes: 5

Ashish Patel
Ashish Patel

Reputation: 1031

$query = $this->createQuery();
$query->getQuerySettings()->setReturnRawQueryResult(TRUE);
$getHotelInfo = 'SELECT * FROM `my_table` WHERE field = ? ORDER BY date DESC LIMIT 1';
return $query->statement($getHotelInfo)->execute();

For executing query you have to write 'setReturnQueryResult' on your repository

Upvotes: 1

Bharat Parmar
Bharat Parmar

Reputation: 1880

i suggest set this in typo3conf/LocalConfiguration.php file under 'SYS' array

'SYS' => array(
......
'displayErrors' => 1,
'sqlDebug' => 1
.......
)

and then write wrong field name in query intentionally and then execute code. this will show last query execute with error.

Upvotes: 0

Benno
Benno

Reputation: 130

An easy way without changing any Typo3 core code and not mentioned in any forum so far is using the php "serialize()" method: 

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

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

Upvotes: 3

src
src

Reputation: 215

A cleaner way to debug your statements when using TYPO3 6.1 is to use the query parser of Typo3DbBackend.

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

\TYPO3\CMS\Core\Utility\GeneralUtility::devLog('query', 'my_extension', 1, array('query' => $queryParts, 'params' => $params));

The parser returns an array containing the different parts of the generated SQL statement.

With TYPO3 6.2 the parseQuery method was moved to Typo3DbQueryParser and lost its second parameter.

Upvotes: 0

ad_on_is
ad_on_is

Reputation: 1550

I just extended the above snippet, with a $_GET condition. for debugging, just append "?dbg_table=tx_some_of_my_tables" to your address, and you're ready to go ;-)

if (in_array($_GET['dbg_table'], $sql['tables'])) {
        echo('<div style="background: #ebebeb; border: 1px solid #999; margin-bottom: 20px; padding: 10px;"><pre style="white-space: normal">'.$statement.'</pre></div>');
    }

Upvotes: 0

Mateng
Mateng

Reputation: 3734

Improvement to biesiors answer:

As Extbase replaces some placeholders after calling buildQuery(), you might prefer to place the debug output into getObjectDataByQuery(), just after $this->replacePlaceholders($sql, $parameters, $tableName); 

if (strpos($sql, "your_table_name.")) {
    debug($sql, 'my debug output');
};

Also, better use debug() instead of var_dump().
[File: typo3\sysext\extbase\Classes\Persistence\Generic\Storage\Typo3DbBackend.php. Line 339 in version 6.1]:

Upvotes: 2

Related Questions