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:

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)

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

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

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.

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();
Copied!

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();
Copied!

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();
Copied!

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();
Copied!

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();
Copied!

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();
Copied!