Database tables in
TYPO3 CMS that can be administrated in the backend come with
TCA definitions that
specify how single fields and rows of the table should be handled and displayed
by the framework.
ctrl section of a tables
TCA array specifies optional framework internal
handling of soft deletes and language overlays: For instance, if a row in the backend
is deleted using the page or list module, many tables are configured to not
entirely drop that row from the table, instead a field (often
deleted) is set
from zero to one for that row. Similar mechanics kick in for start- and endtime as
well as language and workspace overlays. See the Table properties (ctrl) chapter in the
TCA reference for details on this topic.
These mechanics however come with a price tag attached to it: Extension developers dealing with low-level query stuff must take care overlayed or deleted rows are not in the result set of a casual query.
This is where this “automatic restriction” stuff kicks in: The construct is created
on top of native
TYPO3 CMS specific extension. It automatically
WHERE expressions that suppress rows which are marked as deleted or exceeded
their “active” life cycle. All that is based on the
TCA configuration of the affected table.
A developer may ask why she has to go through all this and why this additional stuff is added on a low-level query layer, when “just a simple query” should be fired. The construct implements some important design goals:
Query creation should be easy to use without forcing a developer thinking
too much about those nasty
- Cope with developer laziness: If the framework would force a developer to always add casual restrictions for each and every query, this is easy to forget. We’re all lazy, are we?
- Security: If in doubt, it is better to show a little too less than too much. It is much better to deal with a customer who complains some records are not shown than to show too many records. The former is “just a bug” while the latter can easily escalate to a serious privilege escalation security issue.
- Automatic query upgrades:
If a table was designed without soft-delete in the first
place and later a deleted flag is added and registered in
TCA, queries executed on that table will automatically upgrade and add the according
deleted = 0restriction.
- Handing over restriction details to the framework: Having the restriction expressions done by the framework gives it the opportunity to change details without breaking extension code. This may very well happen in the future and having a happy little upgrade path for such cases in place may become very handy later.
- Flexibility: The class construct is created in a way that allows developers to extend or substitute it with own restrictions if that is useful to model the domain in question.
The restriction builder is called whenever a
COUNT query is executed through
QueryBuilder allows manipulation of those
restrictions while the simplified
Connection class does not. If a query deals with multiple
tables in a join, restrictions for all affected tables are added.
Each single restriction like a
DeletedRestriction or a
StartTimeRestriction is modeled as
a single class implementing the
QueryRestrictionInterface. Each restriction looks up in
TCA if it should kick in. If so, it adds according expressions to the
WHERE clause when
the final statement is compiled.
Multiple restrictions can be grouped in containers which implement the
DefaultRestrictionContainer is always added by … uuhm … default: It adds the
StartTimeRestriction and the
EndTimeRestriction. Note this is true for all contexts a query is executed in: It does not
matter whether a query is created from within a frontend, a backend or a cli call, they all add
DefaultRestrictionContainer if not explicitly told otherwise by an extension developer.
DefaultRestrictionContainer used everywhere is the second iteration of
that code construct:
The first variant automatically added restrictions based on context. For instance, a
query fired by a call that is executed in the backend did not add the hidden flag, while
a query fired from within a frontend call did so. We quickly figured this ends up in a
huge mess: The distinction between frontend, backend and cli is not that sharp in
as example the frontend behaves much more like a backend call if the admin panel is used.
The currently active variant is much easier: It always adds sane defaults everywhere, a developer only has to deal with details if they don’t fit. The core team hopes this approach is a good balance between hidden magic, security, transparency and convenience.
DeletedRestriction: (default) Evaluates
['ctrl']['delete'], adds for instance
AND deleted = 0if
TCA['aTable']['ctrl']['delete'] = 'deleted'is specified.
HiddenRestriction: (default) Evaluates
AND hidden = 0if
hiddenis specified as field name.
StartTimeRestriction: (default) Evaluates
['ctrl']['enablecolumns']['starttime'], typically adds something like
AND (`tt_content`.`starttime` <= 1475580240).
EndTimeRestriction: (default) Evaluates
RootlevelRestriction: Match records on root level, adds
AND (`pid` = 0)
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 workspaced records.
FrontendWorkspaceRestriction: Restriction to filter records for fronted workspaces preview.
WorkspaceRestrictionhas been added to overcome downsides of
BackendWorkspaceRestriction. The workspace restriction limits a SQL query to only select records which are “online” and in live or current workspace.
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
If a restriction needs to be enforced, a restriction could implement the interface
If a restriction implements
EnforceableQueryRestrictionInterface, the following applies:
->removeAll()will remove all restrictions except the ones that implement the interface
->removeByType()will remove a restriction completely, also restrictions that implement the interface
EndTimeRestriction. This container is always added if not told otherwise.
FrontendGroupRestriction. This container should be be added by a developer to a query if creating query statements in frontend context or if handling frontend stuff from within cli calls.
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.
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 starttime and endtime 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\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))) ->execute() ->fetchAll();
DeletedRestriction should be kept in almost all cases. Usually, the only extension that dismiss
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
->add()) is to kick specific restrictions with a call to
// 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
// use TYPO3\CMS\Core\Database\Query\Restriction\FrontendRestrictionContainer // Kick default restrictions and add list of default frontend restrictions $queryBuilder->setRestrictions(GeneralUtility::makeInstance(FrontendRestrictionContainer::class));
->setRestrictions() resets any previously specified restrictions. Any class instance implementing
QueryRestrictionContainerInterface can be given to
->setRestrictions(). This allows extensions to
deliver and use an own set of restrictions for own query statements if needed.
It can be very helpful to debug the final statements created by the
debug($queryBuilder->getSQL()) right before the final call to
take care these calls do not end up in production code.
If you want to apply one or more restriction/s to only one table, that is possible as follows. Let’s say,
that you have content in table
tt_content with a relation to categories. Now you would like to get all records with their categories, except those that are hidden. The hidden restriction in this case should only apply to
tt_content table, not to the
$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 this example the
HiddenRestriction is only applied to
tt table alias of
Furthermore it is possible to restrict the complete set of restrictions of a query builder to a given set of table aliases.
$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 will result 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))