--- title: "Restriction builder" manual: "TYPO3 Explained" version: "11.5" permalink: "https://docs.typo3.org/permalink/t3coreapi:database-restriction-builder@11.5" source: "ApiOverview/Database/RestrictionBuilder/Index.rst" rendered: "2026-10-21T15:30:00+00:00" --- > [!WARNING] > **Attention** > > TYPO3 v11 has reached end-of-life as of October 31th 2024 and > is no longer being maintained. Use the version switcher on the top > left of this page to select documentation for a supported version of TYPO3. > > Need more time before upgrading? > You can purchase Extended Long Term Support (ELTS) for TYPO3 v11 here: > [TYPO3 ELTS](https://typo3.com/services/extended-support-elts). # Restriction builder {#database-restriction-builder} **Table of Contents** - [Rationale](https://docs.typo3.org/permalink/t3coreapi:rationale@11.5) - [Main construct](https://docs.typo3.org/permalink/t3coreapi:main-construct@11.5) - [Restrictions](https://docs.typo3.org/permalink/t3coreapi:restrictions@11.5) - [QueryRestrictionContainer](https://docs.typo3.org/permalink/t3coreapi:queryrestrictioncontainer@11.5) - [Limit restrictions to tables](https://docs.typo3.org/permalink/t3coreapi:limit-restrictions-to-tables@11.5) - [Custom restrictions](https://docs.typo3.org/permalink/t3coreapi:custom-restrictions@11.5) - [Examples](https://docs.typo3.org/permalink/t3coreapi:examples-1@11.5) Database tables in TYPO3 that can be managed in the backend have [TCA](https://docs.typo3.org/m/typo3/reference-tca/11.5/en-us/Index.html) definitions that specify how single fields and rows of the table should be handled and displayed by the framework. The `ctrl` section of a table's TCA array specifies optional framework-internal handling of soft deletes and language overlays: For instance, when a row is deleted in the backend using the page or list module, many tables are configured to not drop that row entirely from the table, but to set a field (often `deleted`) for that row from `0` to `1`. Similar mechanisms apply for start and end times, and to language and workspace overlays as well. See the [Table properties (ctrl)](https://docs.typo3.org/m/typo3/reference-tca/11.5/en-us/Ctrl/Index.html#ctrl) chapter in the TCA reference for details on this topic. However, these mechanisms come at a price: developers of extensions dealing with low-level queries must take care that overlaid or deleted rows are not included in the result set of a simple query. This is where this "automatic restriction" enters the picture: The construct is created on top of native [Doctrine DBAL](https://docs.typo3.org/permalink/t3coreapi:database-introduction@11.5) as a TYPO3-specific extension. It automatically adds `WHERE` expressions that suppress rows which are marked as deleted or have exceeded their "active" lifecycle. All this is based on the TCA configuration of the affected table. ## Rationale {#rationale} A developer might ask why they need to do all this to themselves, and why this extra material is added on top of a low-level query layer when "just a simple query" should be fired. The construct implements some important design goals: - **Simple:** Query creation should be easy to handle without a developer having to deal too much with the tedious TCA details.. - **Cope with developer laziness:** If the framework would force a developer to always add casual restrictions for every single query, this is easy to forget. We are all lazy, aren't we? - **Security:** When in doubt, it is better to show a little too little than too much. It is much better to deal with a customer complaining that some records are *not* displayed than to show too many records. The former is "just a bug", while the latter can easily lead to a serious privilege escalation security issue. - **Automatic query upgrades:** If a table was originally designed without a soft delete and a delete flag is later added and registered in TCA, queries executed on that table will automatically upgrade and the according `deleted = 0` restriction will be added. - **Handing over restriction details to the framework:** Having the restriction expressions handled by the framework gives it the ability to change details without breaking the extension code. This may well happen in the future, and a happy little upgrade path for such cases may prove very useful later. - **Flexibility:** The class construct is designed in a way so that developers can extend or or substitute it with their own restrictions if that makes sense for modeling the domain in question. ## Main construct {#main-construct} The restriction builder is called whenever a `SELECT` or `COUNT` query is executed using either `\TYPO3\CMS\Core\Database\Query\QueryBuilder` or `\TYPO3\CMS\Core\Database\Connection`. The [QueryBuilder](https://docs.typo3.org/permalink/t3coreapi:database-query-builder@11.5) allows manipulation of those restrictions, while the simplified [Connection](https://docs.typo3.org/permalink/t3coreapi:database-connection@11.5) class does not. When a query deals with multiple tables in a join, restrictions are added for all affected tables. Each single restriction such as a `\TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction` or a `\TYPO3\CMS\Core\Database\Query\Restriction\StartTimeRestriction` is modeled as a single class that implements the `\TYPO3\CMS\Core\Database\Query\Restriction\QueryRestrictionInterface`. Each restriction looks up in TCA whether it should be applied. If so, the according expressions are added to the `WHERE` clause when compiling the final statement. Multiple restrictions can be grouped into containers which implement the `\TYPO3\CMS\Core\Database\Query\Restriction\QueryRestrictionContainerInterface`. The `\TYPO3\CMS\Core\Database\Query\Restriction\DefaultRestrictionContainer` is always added by default: It adds the - `\TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction` - `\TYPO3\CMS\Core\Database\Query\Restriction\HiddenRestriction`, - `\TYPO3\CMS\Core\Database\Query\Restriction\StartTimeRestriction` and the - `\TYPO3\CMS\Core\Database\Query\Restriction\EndTimeRestriction`. Note that this applies to all contexts in which a query is executed: It does not matter whether a query is created from a frontend, a backend, or a [CLI](https://docs.typo3.org/permalink/t3coreapi:symfony-console-commands@11.5) call, they all add the `DefaultRestrictionContainer` unless explicitly stated otherwise by an extension developer. > [!NOTE] > Having this `DefaultRestrictionContainer` used everywhere is the second > iteration of this code construct: > > The first variant automatically added contextual restrictions. For instance, > a query triggered by a call in the backend did not add the hidden flag, > while a query triggered in the frontend did. We quickly figured out that > this leads to a huge mess: The distinction between frontend, backend and CLI > is not *that* sharp in TYPO3, so for example the frontend behaves much more > like a backend call when using the [admin panel](https://docs.typo3.org/c/typo3/cms-adminpanel/11.5/en-us/Index.html). > > The currently active variant is much easier: It always adds sane defaults > everywhere, a developer only has to deal with details if they do not fit. > The Core Team hopes that this approach is a good balance between hidden > magic, security, transparency, and convenience. ## Restrictions {#restrictions} - **`\TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction` (default)** Evaluates `['ctrl']['delete']`, adds for instance `AND deleted = 0` if `TCA['aTable']['ctrl']['delete'] = 'deleted'` is specified. - **`\TYPO3\CMS\Core\Database\Query\Restriction\HiddenRestriction` (default)** Evaluates `['ctrl']['enablecolumns']['disabled']`, adds `AND hidden = 0` if `hidden` is specified as field name. - **`\TYPO3\CMS\Core\Database\Query\Restriction\StartTimeRestriction` (default)** Evaluates `['ctrl']['enablecolumns']['starttime']`, typically adds something like `AND (`tt_content`.`starttime` <= 1475580240)`. - **`\TYPO3\CMS\Core\Database\Query\Restriction\EndTimeRestriction` (default)** Evaluates `['ctrl']['enablecolumns']['endtime']`. - **`\TYPO3\CMS\Core\Database\Query\Restriction\FrontendGroupRestriction`** Evaluates `['enablecolumns']['fe_group']`. - **`\TYPO3\CMS\Core\Database\Query\Restriction\RootlevelRestriction`** Match records on root level, adds `AND (`pid` = 0)` - **`\TYPO3\CMS\Core\Database\Query\Restriction\BackendWorkspaceRestriction`** Determines the current workspace a backend user is working in and adds a couple of restrictions to select only records of that workspace if the table supports workspace-enabled records. - **`\TYPO3\CMS\Core\Database\Query\Restriction\FrontendWorkspaceRestriction`** Restriction to filter records for frontend workspaces preview. - **`\TYPO3\CMS\Core\Database\Query\Restriction\WorkspaceRestriction`** `WorkspaceRestriction` has been added to overcome downsides of `FrontendWorkspaceRestriction` and `BackendWorkspaceRestriction`. The workspace restriction limits an SQL query to only select records which are "online" and in live or current workspace. > [!NOTE] > As an important note and limitation of any workspace-related restrictions, > fetching the exact records need to be handled after the SQL results are > fetched, by overlaying the records with > `\TYPO3\CMS\Backend\Utility\BackendUtility::getRecordWSOL()`, > `\TYPO3\CMS\Core\Domain\Repository\PageRepository->versionOL()` or > `\TYPO3\CMS\Core\DataHandling\PlainDataResolver`. When a restriction needs to be enforced, a restriction could implement the interface `TYPO3CMSCoreDatabaseQueryRestrictionEnforceableQueryRestrictionInterface`. If a restriction implements `EnforceableQueryRestrictionInterface`, the following applies: - `->removeAll()` will remove all restrictions except the ones that implement the interface `EnforceableQueryRestrictionInterface`. - `->removeByType()` will remove a restriction completely, also restrictions that implement the interface `EnforceableQueryRestrictionInterface`. ## QueryRestrictionContainer {#queryrestrictioncontainer} - **`\TYPO3\CMS\Core\Database\Query\Restriction\DefaultRestrictionContainer`** Adds - `\TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction` - `\TYPO3\CMS\Core\Database\Query\Restriction\HiddenRestriction` - `\TYPO3\CMS\Core\Database\Query\Restriction\StartTimeRestriction` - `\TYPO3\CMS\Core\Database\Query\Restriction\EndTimeRestriction` This container is always added if not told otherwise. - **`\TYPO3\CMS\Core\Database\Query\Restriction\FrontendRestrictionContainer`** Adds - `\TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction` - `\TYPO3\CMS\Core\Database\Query\Restriction\HiddenRestriction` - `\TYPO3\CMS\Core\Database\Query\Restriction\StartTimeRestriction` - `\TYPO3\CMS\Core\Database\Query\Restriction\EndTimeRestriction` - `\TYPO3\CMS\Core\Database\Query\Restriction\FrontendWorkspaceRestriction` - `\TYPO3\CMS\Core\Database\Query\Restriction\FrontendGroupRestriction` This container should be added by a developer to a query when creating query statements in frontend context or when handling frontend stuff from within [CLI](https://docs.typo3.org/permalink/t3coreapi:symfony-console-commands@11.5) calls. - **`\TYPO3\CMS\Core\Database\Query\Restriction\LimitToTablesRestrictionContainer`** This restriction container applies added restrictions only to the given table aliases. See [Limit restrictions to tables](https://docs.typo3.org/permalink/t3coreapi:database-limit-restrictions-to-tables@11.5) for more information. Enforced restrictions are treated equally to all other restrictions. ## Limit restrictions to tables {#database-limit-restrictions-to-tables} With `\TYPO3\CMS\Core\Database\Query\Restriction\LimitToTablesRestrictionContainer` it is possible to apply restrictions to a query only for a given set of tables, or - to be precise - table aliases. Since it is a restriction container, it can be added to the restrictions of the query builder and can hold restrictions itself. ### Examples {#examples} If you want to apply one or more restrictions to only one table, that is possible as follows. Let us say you have content in the `tt_content` table with a relation to categories. Now you want to get all records with their categories except those that are hidden. In this case, the hidden restriction should apply only to the `tt_content` table, not to the `sys_category` or `sys_category_*_mm` table. **EXT:some_extension/Classes/Domain/Repository/ContentRepository.php** ```php // use TYPO3\CMS\Core\Database\Connection; $queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content'); $queryBuilder->getRestrictions() ->removeByType(HiddenRestriction::class) ->add( GeneralUtility::makeInstance(LimitToTablesRestrictionContainer::class) ->addForTables(GeneralUtility::makeInstance(HiddenRestriction::class), ['tt']) ); $queryBuilder->select('tt.uid', 'tt.header', 'sc.title') ->from('tt_content', 'tt') ->from('sys_category', 'sc') ->from('sys_category_record_mm', 'scmm') ->where( $queryBuilder->expr()->eq( 'scmm.uid_foreign', $queryBuilder->quoteIdentifier('tt.uid') ), $queryBuilder->expr()->eq( 'scmm.uid_local', $queryBuilder->quoteIdentifier('sc.uid') ), $queryBuilder->expr()->eq( 'tt.uid', $queryBuilder->createNamedParameter($id, Connection::PARAM_INT) ) ); ``` Read [how to correctly instantiate](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5) a query builder with the connection pool. In addition, it is possible to restrict the complete set of restrictions of a query builder to a given set of table aliases: **EXT:some_extension/Classes/Domain/Repository/ContentRepository.php** ```php // use TYPO3\CMS\Core\Database\Connection; $queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content'); $queryBuilder->getRestrictions() ->removeAll() ->add(GeneralUtility::makeInstance(HiddenRestriction::class)); $queryBuilder->getRestrictions()->limitRestrictionsToTables(['c2']); $queryBuilder ->select('c1.*') ->from('tt_content', 'c1') ->leftJoin('c1', 'tt_content', 'c2', 'c1.parent_field = c2.uid') ->orWhere( $queryBuilder->expr()->isNull('c2.uid'), $queryBuilder->expr()->eq( 'c2.pid', $queryBuilder->createNamedParameter(1, Connection::PARAM_INT) ) ); ``` Read [how to correctly instantiate](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5) a query builder with the connection pool. Which results in: ```sql SELECT "c1".* FROM "tt_content" "c1" LEFT JOIN "tt_content" "c2" ON c1.parent_field = c2.uid WHERE (("c2"."uid" IS NULL) OR ("c2"."pid" = 1)) AND ("c2"."hidden" = 0)) ``` ## Custom restrictions {#database-custom-restrictions} It is possible to add additional query restrictions by adding class names as key to `$GLOBALS['TYPO3_CONF_VARS']['DB']['additionalQueryRestrictions']`. These restriction objects will be added to any select query executed using the [QueryBuilder](https://docs.typo3.org/permalink/t3coreapi:database-query-builder@11.5). If these added restriction objects additionally implement `\TYPO3\CMS\Core\Database\Query\Restriction\EnforceableQueryRestrictionInterface` and return `true` in the to be implemented method `isEnforced()`, calling `$queryBuilder->getRestrictions()->removeAll()` such restrictions will **still** be applied to the query. If an enforced restriction must be removed, it can still be removed with `$queryBuilder->getRestrictions()->removeByType(SomeClass::class)`. Implementers of custom restrictions can therefore have their restrictions always enforced, or even not applied at all, by returning an empty expression in certain cases. To add a custom restriction class, use the following snippet: **EXT:my_extension/ext_localconf.php** ```php use MyVendor\MyExtension\Database\Query\Restriction\CustomRestriction; if (!isset($GLOBALS['TYPO3_CONF_VARS']['DB']['additionalQueryRestrictions'][CustomRestriction::class])) { $GLOBALS['TYPO3_CONF_VARS']['DB']['additionalQueryRestrictions'][CustomRestriction::class] = []; } ``` > [!NOTE] > The class name must be the array key and the value must always be an array, > which is reserved for options given to the restriction objects. > [!IMPORTANT] > Restrictions added by third-party extensions will impact the whole system. > Therefore this API does not allow removing restrictions added by the system > and adding restrictions should be handled with care. Removing third party restrictions is possible, by setting the option `disabled` for a restriction to `true` in global TYPO3 configuration or `ext_localconf.php` of an extension: **EXT:my_extension/ext_localconf.php** ```php use MyVendor\MyExtension\Database\Query\Restriction\CustomRestriction; $GLOBALS['TYPO3_CONF_VARS']['DB']['additionalQueryRestrictions'][CustomRestriction::class]['disabled'] = true; ``` ## Examples {#examples-1} Often the default restrictions are sufficient. Nothing needs to be done in those cases. However, many backend modules still want to show disabled records and remove the start time and end time restrictions to allow administration of those records for an editor. A typical setup from within a backend module: **EXT:some_extension/Classes/SomeClass.php** ```php // use TYPO3\CMS\Core\Utility\GeneralUtility; // use TYPO3\CMS\Core\Database\Connection; // use TYPO3\CMS\Core\Database\ConnectionPool; // use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction // SELECT `uid`, `bodytext` FROM `tt_content` WHERE (`pid` = 42) AND (`tt_content`.`deleted` = 0) $queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content'); // Remove all restrictions but add DeletedRestriction again $queryBuilder ->getRestrictions() ->removeAll() ->add(GeneralUtility::makeInstance(DeletedRestriction::class)); $result = $queryBuilder ->select('uid', 'bodytext') ->from('tt_content') ->where($queryBuilder->expr()->eq( 'pid', $queryBuilder->createNamedParameter($pid, Connection::PARAM_INT) )) ->executeQuery() ->fetchAllAssociative((); ``` Read [how to correctly instantiate](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5) a query builder with the connection pool. The `DeletedRestriction` should be kept in almost all cases. Usually, the only extension that dismisses that flag is the recycler module to list and resurrect deleted records. Any object implementing the `QueryRestrictionInterface` can be given to `->add()`. This allows extensions to deliver own restrictions. An alternative to the recommended way of first removing all restrictions and then adding needed ones again (using `->removeAll()`, then `->add()`) is to kick specific restrictions with a call to `->removeByType()`: **EXT:some_extension/Classes/SomeClass.php** ```php // use TYPO3\CMS\Core\Utility\GeneralUtility; // use TYPO3\CMS\Core\Database\ConnectionPool; // use TYPO3\CMS\Core\Database\Query\Restriction\StartTimeRestriction // use TYPO3\CMS\Core\Database\Query\Restriction\EndTimeRestriction // Remove starttime and endtime, but keep hidden and deleted $queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content'); $queryBuilder ->getRestrictions() ->removeByType(StartTimeRestriction::class) ->removeByType(EndTimeRestriction::class); ``` Read [how to correctly instantiate](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5) a query builder with the connection pool. In the frontend it is often needed to swap the `DefaultRestrictionContainer` with the `FrontendRestrictionContainer`: **EXT:some_extension/Classes/SomeClass.php** ```php // use TYPO3\CMS\Core\Database\Query\Restriction\FrontendRestrictionContainer // Remove default restrictions and add list of default frontend restrictions $queryBuilder->setRestrictions(GeneralUtility::makeInstance(FrontendRestrictionContainer::class)); ``` Note that `->setRestrictions()` resets any previously specified restrictions. Any class instance implementing `QueryRestrictionContainerInterface` can be given to `->setRestrictions()`. This allows extensions to deliver and use an custom set of restrictions for own query statements if needed. > [!TIP] > It can be very helpful to debug the final statements created by the > `RestrictionBuilder` using `debug($queryBuilder->getSQL())` right > before the final call to `$queryBuilder->executeQuery()`. Just take > care these calls **do not** > [end up in production](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-get-sql@11.5) code.