Restriction builder

Database tables in TYPO3 that can be managed in the backend have TCA 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) 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 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

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

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 allows manipulation of those restrictions, while the simplified Connection 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 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.

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

\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

Deprecated since version 12.1: Use \TYPO3\CMS\Core\Database\Query\Restriction\WorkspaceRestriction instead.

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

Deprecated since version 12.1: Use \TYPO3\CMS\Core\Database\Query\Restriction\WorkspaceRestriction instead.

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 \TYPO3\CMS\Core\Database\Query\Restriction\EnforceableQueryRestrictionInterface. 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

\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\WorkspaceRestriction

  • \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 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 for more information. Enforced restrictions are treated equally to all other restrictions.

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

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
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->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, \PDO::PARAM_INT))
    );

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
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->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, \PDO::PARAM_INT))
    );

Which results in:

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

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.

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
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.

Attention

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
use MyVendor\MyExtension\Database\Query\Restriction\CustomRestriction;

$GLOBALS['TYPO3_CONF_VARS']['DB']['additionalQueryRestrictions'][CustomRestriction::class]['disabled'] = true;

Examples

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
// use TYPO3\CMS\Core\Utility\GeneralUtility;
// 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 = GeneralUtility::makeInstance(ConnectionPool::class)->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, \PDO::PARAM_INT)
    ))
    ->executeQuery()
    ->fetchAllAssociative(();

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
// 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 = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('tt_content');
$queryBuilder
    ->getRestrictions()
    ->removeByType(StartTimeRestriction::class)
    ->removeByType(EndTimeRestriction::class);

In the frontend it is often needed to swap the DefaultRestrictionContainer with the FrontendRestrictionContainer:

EXT:some_extension/Classes/SomeClass.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 code.