Restriction builder

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.

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

The workspace restriction limits an SQL query to only select records which are "online" and in live or current workspace.

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.

// 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)
        )
    );

// 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)
        )
    );

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:

<?php

declare(strict_types=1);

use MyVendor\MyExtension\Database\Query\Restriction\CustomRestriction;

defined('TYPO3') or die();

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

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:

<?php

declare(strict_types=1);

use MyVendor\MyExtension\Database\Query\Restriction\CustomRestriction;

defined('TYPO3') or die();

$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:

// 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 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():

// 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 a query builder with the connection pool.

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

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