CODEWITHSUNDEEP
typo3-9.x
mcyg
mcyg

Reputation: 317

Raw SQL queries in TYPO3 9

Is there a way to perform raw SQL queries in TYPO3 9?

Something equivalent to $GLOBALS['TYPO3_DB']->sql_query($sql); in previous versions.

Upvotes: 2

Views: 4448

Answers (3)

padina
padina

Reputation: 137

THE ORM-concept seems to makes it difficult using raw SQL.

    /**
     * @param string $tableName
     * @return bool
     */
    public function createMySplitTable($newTableName = self::TABLENAME)
    {
        if ($newTableName !== self::TABLENAME) {
            $baseTable = self::TABLENAME;
            // make a structure-copy of the main table
            $sql ="CREATE TABLE $newTableName SELECT * FROM $baseTable AS main LIMIT 0;";
            // looky-looky at 20200609: https://www.strangebuzz.com/en/snippets/running-raw-sql-queries-with-doctrine
            // seems to work
            /** @var Connection $connection */
            $connection = GeneralUtility::makeInstance(ConnectionPool::class)
                ->getConnectionForTable(self::TABLENAME);
            /** @var DriverStatement $statement */
            $statement = $connection->prepare($sql);
            $statement->execute();
//            // --- don't work for me :-( 
//            /** @var QueryBuilder $queryBuilder */
//            $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
//                ->getQueryBuilderForTable($baseTable);
//            $queryBuilder->resetRestrictions();
//            $queryBuilder->resetQueryParts();
//            $queryBuilder->add($sql,'select');  // Tried some variations
//            $queryBuilder->execute();
//            // --- don't work for me :-(
//            /** @var Query $query */ // Extbase won't work for this query
//            $query = $this->createQuery();
//            $query->statement($sql);
//            $query->execute(true);
//            // --- work only for TYPO3 8 and lower
//            $GLOBALS['TYPO3_DB']->sql_query($sql);  /// < TYPO3 8
//  
        }
    }

Thanks to https://www.strangebuzz.com/en/snippets/running-raw-sql-queries-with-doctrine

Upvotes: 2

chris
chris

Reputation: 2243

In your repository, you can use statement() for this.

Example:

$query = $this->createQuery();

$sql = '
    SELECT fieldA, fieldB
    FROM table
    WHERE 
        pid = '.$pid.'
        AND someField = 'something'
';

$query->statement($sql)->execute();

Make sure, that you take care of sanitizing the input!

Upvotes: 1

Bernd Wilke πφ
Bernd Wilke πφ

Reputation: 10791

you could use the querybuilder with it's method
TYPO3\CMS\Core\Database\Query\QueryBuilder::selectLiteral(string ... $selects).

Be aware:

Specifies items that are to be returned in the query result. Replaces any previously specified selections, if any. This should only be used for literal SQL expressions as no quoting/escaping of any kind will be performed on the items.

There also is
TYPO3\CMS\Core\Database\Query\QueryBuilder::addSelectLiteral(string ... $selects)

Adds an item that is to be returned in the query result. This should only be used for literal SQL expressions as no quoting/escaping of any kind will be performed on the items.

Upvotes: 0

Related Questions