Breaking: #102875 - QueryBuilder changes

See forge#102875

Description

Doctrine DBAL 4 removed methods from the QueryBuilder which has been adopted to the extended \TYPO3\CMS\Core\Database\Query\QueryBuilder.

Removed methods:

  • QueryBuilder::add(): Use new reset methods and normal set methods instead.

  • QueryBuilder::getQueryPart($partName): No replacement, internal state.

  • QueryBuilder::getQueryParts(): No replacement, internal state.

  • QueryBuilder::resetQueryPart($partName): Replacement methods has been added, see list.

  • QueryBuilder::resetQueryParts(): Replacement methods has been added, see list.

  • QueryBuilder::execute(): Use QueryBuilder::executeQuery() or QueryBuilder::executeStatement() directly.

  • QueryBuilder::setMaxResults(): Using (int)0 as max result will no longer work and retrieve no records. Use NULL instead to allow all results.

Signature changes:

  • QueryBuilder::quote(string $value): Second argument has been dropped and the value must now be of type string.

Impact

Calling any of the mentioned removed methods will result in a PHP error. Also signature changes introducing type hint will result in a PHP error if called with an invalid type.

Affected installations

Only those installations that use the mentioned methods.

Migration

Extension author need to replace the removed methods with the alternatives which

QueryBuilder::add('query-part-name')

Use the direct set/select methods instead:

Replacements

before

after

->add('select', $array)

->select(...$array)

->add('where', $wheres)

->where(...$wheres)

->add('having', $havings)

->having(...$havings)

->add('orderBy', $orderBy)

->orderBy($orderByField, $orderByDirection)->addOrderBy($orderByField2)

->add('groupBy', $groupBy)

->groupBy($groupField)->addGroupBy($groupField2)

Note

This can be done already in TYPO3 v12 with at least Doctrine DBAL 3.8.

QueryBuilder::resetQueryParts() and QueryBuilder::resetQueryPart()

However, several replacements have been put in place depending on the $queryPartName parameter:

Replacements

before

after

'select'

Call ->select() with a new set of columns

'distinct'

->distinct(false)

'where'

->resetWhere()

'having'

->resetHaving()

'groupBy'

->resetGroupBy()

'orderBy

->resetOrderBy()

'values'

Call ->values() with a new set of values.

Note

This can be done already in TYPO3 v12 with at least Doctrine DBAL 3.8.

QueryBuilder::execute()

Doctrine DBAL 4 removed QueryBuilder::execute() in favour of the two methods QueryBuilder::executeQuery() for select/count and QueryBuilder::executeStatement() for insert, delete and update queries.

Before

use TYPO3\CMS\Core\Database\ConnectionPool;

// select query
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
  ->getQueryBuilderForTable('pages');
$rows = $queryBuilder
    ->select('*')
    ->from('pages')
    ->execute()
    ->fetchAllAssociative();

// delete query
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
  ->getQueryBuilderForTable('pages');
$deletedRows = (int)$queryBuilder
    ->delete('pages')
    ->where(
      $queryBuilder->expr()->eq('pid', $this->createNamedParameter(123),
    )
    ->execute();

After

use TYPO3\CMS\Core\Database\ConnectionPool;

// select query
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
  ->getQueryBuilderForTable('pages');
$rows = $queryBuilder
    ->select('*')
    ->from('pages')
    ->executeQuery()
    ->fetchAllAssociative();

// delete query
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
  ->getQueryBuilderForTable('pages');
$deletedRows = (int)$queryBuilder
    ->delete('pages')
    ->where(
      $queryBuilder->expr()->eq('pid', $this->createNamedParameter(123),
    )
    ->executeStatement();

QueryBuilder::quote(string $value)

quote() uses Connection::quote() and therefore adopts the changed signature and behaviour.

Before

use TYPO3\CMS\Core\Database\Connection as Typo3Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

// select query
$pageId = 123;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('pages');
$rows = $queryBuilder
    ->select('*')
    ->from('pages')
    ->where(
        $queryBuilder->expr()->eq(
            'uid',
            $queryBuilder->quote($pageId, Typo3Connection::PARAM_INT)
        ),
    )
    ->executeQuery()
    ->fetchAllAssociative();

After

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

// select query
$pageId = 123;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('pages');
$rows = $queryBuilder
    ->select('*')
    ->from('pages')
    ->where(
        $queryBuilder->expr()->eq(
            'uid',
            $queryBuilder->quote((string)$pageId)
        ),
    )
    ->executeQuery()
    ->fetchAllAssociative();

Tip

To provide TYPO3 v12 and v13 with one code base, ->quote((string)$value) can be used to ensure dual Core compatibility.

QueryBuilder::setMaxResults()

Using (int)0 as max result will no longer work and retrieve no records. Use NULL instead to allow all results.

Before

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

// select query
$pageId = 123;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('pages');
$rows = $queryBuilder
    ->select('*')
    ->from('pages')
    ->setFirstResult(0)
    ->setMaxResults(0)
    ->executeQuery()
    ->fetchAllAssociative();

After

use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;

// select query
$pageId = 123;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('pages');
$rows = $queryBuilder
    ->select('*')
    ->from('pages')
    ->setFirstResult(0)
    ->setMaxResults(null)
    ->executeQuery()
    ->fetchAllAssociative();