First of all, add() was a internal method of Doctrine DBAL and has been
removed by Doctrine DBAL. We decided to not restore that and following the
line Doctrine DBAL has drawn.
Since the very beginning, the TYPO3 QueryBuilder was an decorator around
the Doctrine QueryBuilder and accessibaly with getConcreteQueryBuilder()
which is still possible.
The argument for the dropped add() method was, that it is doable by the
generic methods, in case of order things by using oderBy() or addOrderBy().
The TYPO3 levele does automatic quoting and stuff, and in cases that is not
usefull, the underlying concrete builder instance needs to be used - and should
have be done so already earlier.
The raw doctrine QueryBuilder does not do any quoting, so you have to take care
completly on your own.
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
->getQueryBuilderForTable('pages');
$queryBuilder
->select('uid', 'pid', /* ... */ )
->from('pages')
->where(
$queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter(123, Connection::PARAM_INT)),
);
$concreteQueryBuilder = $queryBuilder->getConcreteQueryBuilder();
$concreteQueryBuilder
// proper quoting must be done manually on the concrete query builder
->orderBy('FIELD(' .$queryBuilder->quoteIdentifier('eventtype') . ', 0, 4, 1, 2, 3)');
$queryBuilder->addOrderBy('sorting'); // use correct identifier quoting
$result = $queryBuilder->executeQuery();
while ($row = $result->fetchAssociative()) {
// ..
}
Further, you can also revieve the Doctrine DBAL Expression Builder from the concrete QueryBuilder
instead of the advanced TYPO3 Expression Builder.
Note, since TYPO3 v13 the TYPO3 ExpressionBuilder has additional expression variants, not provided
by the underlying Doctrine DBAl ExpressionBuilder - see corresponding v13 ChangeLog for that.
In general, it is a good advice to build a helper class for custom expression which is tested against
all TYPO3 supported Database vendors (using the Platform class) etc. Can be done from the outside.
Or providing changes (FEATURE) to add missing expression (with full test coverage ) to the TYPO3
implementation or contribute directly to Doctrine DBAL.
From my point of view this is not a bug, just an oversight of the proper API to use. I hope I could
point out how to do the required stuff and going to close this issue in a couple of days if there is
not a real bug involved.
Please give feedback if the explanation helped you to build your needed query.