.. include:: /Includes.rst.txt

.. _feature-87776:

==============================================================
Feature: #87776 - Limit Restriction to table/s in QueryBuilder
==============================================================

See :issue:`87776`

Description
===========

In some cases it is needed to apply restrictions only to a certain table.
With the new :php:`\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
it can hold restrictions itself. The restrictions it holds can be limited to tables like this:


Example implementation:
-----------------------

.. code-block:: 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 this example the HiddenRestriction is only applied to :sql:`tt` table alias of :sql:`tt_content`.

Furthermore it is possible to restrict the complete set of restrictions of a query builder to a
given set of table aliases.

.. code-block:: 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 will result in:

.. code-block:: 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))

Impact
======

It is now easily possible to add restrictions that are only applied to certain tables/ table aliases,
by using :php:`\TYPO3\CMS\Core\Database\Query\Restriction\LimitToTablesRestrictionContainer`.

.. index:: Database, ext:core, PHP-API