Extbase - Get Created SQL from Query

Extbase - get created sql from query

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

Typo3, How can I get the MySQL query created from the query object

in extbase its very hard to display the last query.

you might try the normal TYPO3 way, but you have to execute the query before you can do that:

$GLOBALS['TYPO3_DB']->store_lastBuiltQuery = 1;

//query

// the complete SQL-Statement
echo $GLOBALS['TYPO3_DB']->debug_lastBuiltQuery;

Another way is to go into the buildQuery(array $sql) just before the return statement and add this snippet:

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

You can find the buildQuery method here:

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

Edit:
A very good method is to just misspell the column name. For example: Column is called test, call it testsdffq. The query will fail and it will show you the whole query.

How to debug a query in extbase?

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

$GLOBALS['TYPO3_DB']->debugOutput = true;

return $result;

Execute plain SQL query in Extbase repository

$Query = $this->createquery();
$Query->getQuerySettings()->setReturnRawQueryResult(TRUE);
$Query->getQuerySettings()->setRespectStoragePage(FALSE);
$Query->statement('your query'); 
return $Query->execute();

In your controller

class your_Controller_name extends \TYPO3\CMS\Extbase\Mvc\Controller\ActionController { 

    protected $myimagegalleryRepository;

    public function yourAction() {
        $myimagegalleries = $this->myimagegalleryRepository->getImages(
        $uidOfCE, $this->cObj->data['pid'] );
    }
}

setReturnRawQueryResult if true you will get result in array.

Try to avoid statement see warning here: http://docs.typo3.org/typo3cms/ExtbaseFluidBook/6-Persistence/3-implement-individual-database-queries.html

TYPO3 9.5 Extbase Query for UidArray

Did you try the in operator?

 public function yourFunctionName($uid) 
 {
    $query = $this->createQuery();
    $query->in('uid', $uidArray);
   return $query->execute();
 }

Assuming that your array looks like this:

$uidArray = [
  0 = '34',
  1 = '15',
  3 = '88'
]

EDIT

If you do not care about where your objects are stored then you can do the following.

public function yourFunctionName($uid) 
{
   $query = $this->createQuery();
   $query->getQuerySettings()->setRespectStoragePage(FALSE);
   $query->matching(
       $query->in('uid', $uidArray)
   );
  return $query->execute()];
 }

Which is going to ignore the pid in the query

TYPO3 Extbase query with CONCAT

With Doctrine DBAL you can use getConcatExpression from DatabasePlatform of your current Connection to the table:

/** @var \TYPO3\CMS\Core\Database\Query\QueryBuilder $queryBuilder */
$queryBuilder = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance(\TYPO3\CMS\Core\Database\ConnectionPool::class)->getQueryBuilderForTable('myTable');
/** @var \TYPO3\CMS\Core\Database\Connection $connection */
$connection = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance(\TYPO3\CMS\Core\Database\ConnectionPool::class)->getConnectionForTable('myTable');
$query = $queryBuilder
    ->select('*')
    ->from('myTable')
    ->where(
        $queryBuilder->expr()->orX(
            $queryBuilder->expr()->like(
                'street',
                $queryBuilder->createNamedParameter('%abc%', \PDO::PARAM_STR)
            ),
            $queryBuilder->expr()->like(
                'street_number',
                $queryBuilder->createNamedParameter('%abc%', \PDO::PARAM_STR)
            ),
            $queryBuilder->expr()->comparison(
                $connection->getDatabasePlatform()->getConcatExpression('street, \' \', street_number'),
                'LIKE',
                $queryBuilder->createNamedParameter('%abc%', \PDO::PARAM_STR)
            )
        )
    )
    ->execute();

---

Related Topics