Feature: #84115 - Doctrine DBAL - notInSet() for expressions¶
TYPO3's Database Abstraction Layer supports a wide range of cross-RDBMS-functionality to limit SELECT statements via the ExpressionBuilder.
for comma-separated lists, the call
inSet() can be used to detect database rows
which include a value in a comma-separated list, such as
pages.fe_group where the UIDs of allowed frontend user groups
notInSet() has been added to TYPO3's DBAL ExpressionBuilder,
which works as the opposite functionality:
"Get all rows where a certain value is NOT in the list of comma-separated values".
It is now possible to use
notInSet() via Doctrine DBAL
Expression Builder for SQLite, MySQL/MariaDB, PostgreSQL and MSSQL Backends.
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('fe_users'); $result = $queryBuilder ->select('*') ->from('fe_users') ->where( $queryBuilder->expr()->notInSet('usergroup', '5') ) ->execute();
This queries all frontend users which do not directly belong to usergroup of with uid "5".
Please note that this functionality is for extension authors
and their usage should be thought-through properly, as queries such as "Show me all results where the usergroup has NO access to" isn't a use-case for