[FEATURE] Add UNION Clause support to the QueryBuilder

[TYPO3IncTeam]

ℹ️ View this commit on Github
👥 Authored by Stefan Bürk stefan@buerk.tech
✔️ Merged by Anja Leichsenring aleichsenring@ab-softlab.de

Commit message

[FEATURE] Add UNION Clause support to the QueryBuilder

The UNION Clause is used to combine the result-set of
two or more SELECT SQL queries suported by all database
vendors - at least on a shared basic level with the usual
special vendor enhancements.

There is a common shared subset working for all of them:

SELECT column_name(s) FROM table1
WHERE ...

UNION <ALL | DISTINCT>

SELECT column_name(s) FROM table2
WHERE ...

ORDER BY ...
LIMIT x OFFSET y

Instead of introducing the UNION Clause support into
the TYPO3 QueryBuilder, the challenge was taken to add
this directly into Doctrine DBAL [1]. The effort was
rewarded and with the release of Doctrine DBAL 4.1.0
UNION Clause [2] the support is included.

This change adopts the new feature into the extended
ConcreteQueryBuilder and QueryBuilder to support
extension authors with a simple and usable interface
to build UNION (DISTINCT) and UNION ALL queries.

[1] doctrine/dbal#6369
[2] https://github.com/doctrine/dbal/releases/tag/4.1.0

Resolves: #104631
Related: #104628
Releases: main
Change-Id: I443b762fdc6a9f1ed77b3d655d0ab2f371a56d50
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/83943
Reviewed-by: Anja Leichsenring aleichsenring@ab-softlab.de
Tested-by: core-ci typo3@b13.com
Tested-by: Christian Kuhn lolli@schwarzbu.ch
Tested-by: Anja Leichsenring aleichsenring@ab-softlab.de
Reviewed-by: Christian Kuhn lolli@schwarzbu.ch

➕ Added files

13.3/Feature-104631-AddUNIONClauseSupportToTheQueryBuilder.rst

.. include:: /Includes.rst.txt

.. _feature-104631-1723714985:

=================================================================
Feature: #104631 - Add `UNION Clause` support to the QueryBuilder
=================================================================

See :issue:`104631`

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

The :sql:`UNION` clause is used to combine the result-set of two or more
:sql:`SELECT` statements, which all database vendors supports with usual
specialities for each.

Still, there is a common shared subset which works for all of them:

..  code-block:: sql

    SELECT column_name(s) FROM table1
    WHERE ...

    UNION <ALL | DISTINCT>

    SELECT column_name(s) FROM table2
    WHERE ...

    ORDER BY ...
    LIMIT x OFFSET y

with shared requirements:

* Each SELECT must return the same fields in number, naming and order.
* Each SELECT must not have ORDER BY, expect MySQL allowing it to be used as sub
  query expression encapsulated in parenthesis.

Generic :sql:`UNION` clause support has been contributed to `Doctrine DBAL` and
is included since `Release 4.1.0 <https://github.com/doctrine/dbal/releases/tag/4.1.0>`__
which introduces two new API method on the QueryBuilder:

* :php:`union(string|QueryBuilder $part)` to create first UNION query part
* :php:`addUnion(string|QueryBuilder $part, UnionType $type = UnionType::DISTINCT)`
  to add addtional :sql:`UNION (ALL|DISTINCT)` query parts with the selected union
  query type.

TYPO3 decorates the Doctrine DBAL QueryBuilder to provide for most API methods automatic
quoting of identifiers and values **and**  to appliy database restrictions automatically
for :sql:`SELECT` queries.

The Doctrine DBAL API has been adopted now to provide the same surface for the
TYPO3 :php:`\TYPO3\CMS\Core\Database\Query\QueryBuilder` and the intermediate
:php:`\TYPO3\CMS\Core\Database\Query\ConcreteQueryBuilder` to make it easier to
create :sql:`UNION` clause queries. The API on both methods allows to provide
dedicated QueryBuilder instances or direct queries as strings in case it is needed.

..  note::

    Providing :sql:`UNION` parts as plain string requires the developer to take
    care of proper quoting and escaping within the query part.

Another point worth to mention is, that only `named placeholder` can be used
and registered on the most outer :php:`QueryBuilder` object instance, similar
to advanced query creation using for example :sql:`SUB QUERIES`.

..  warning::

    :php:`QueryBuilder` can be used create :sql:`UNION` clause queries not
    compatible with all database, for example using LIMIT/OFFSET in each
    part query or other stuff.

UnionType::DISTINCT and UnionType::ALL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Each subsequent part needs to be defined either as :sql:`UNION DISTINCT` or
:sql:`UNION ALL` which could have not so obvious effects.

For example, using :sql:`UNION ALL` for all parts in between except for the last
one would generate larger result sets first, but discards duplicates when adding
the last result set. On the other side, using :sql:`UNION ALL` tells the query
optimizer **not** to scan for duplicats and remove them at all which can be a
performance improvement - if you can deal with duplicates it can be ensured that
each part does not produce same outputs.

Example: Compose a :sql:`UNION` clause query
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

..  code-block:: php
    :caption: Custom service class using an UNION query to retrieve data.

    use TYPO3\CMS\Core\Database\Connection;
    use TYPO3\CMS\Core\Database\ConnectionPool;
    use TYPO3\CMS\Core\Database\Query\QueryBuilder;

    final readonly MyService {
      public function __construct(
        private ConnectionPool $connectionPool,
      ) {}

      public function executeUnionQuery(
        int $pageIdOne,
        int $pageIdTwo,
      ): ?array {
        $connection = $this->connectionPool->getConnectionForTable('pages');
        $unionQueryBuilder = $connection->createQueryBuilder();
        $firstPartQueryBuilder = $connection->createQueryBuilder();
        $firstPartQueryBuilder->getRestrictions()->removeAll();
        $secondPartQueryBuilder = $connection->createQueryBuilder();
        $secondPartQueryBuilder->getRestrictions()->removeAll();
        $expr = $unionQueryBuilder->expr();

        $firstPartQueryBuilder
          ->select('uid', 'pid', 'title')
          ->from('pages')
          ->where(
            $expr->eq(
              'pages.uid',
              $unionQueryBuilder->createNamedParameter($pageIdOne),
          );
        $secondPartQueryBuilder
          ->select('uid', 'pid', 'title')
          ->from('pages')
          ->where(
            $expr->eq(
              'pages.uid',
              $unionQueryBuilder->createNamedParameter($pageIdOne),
          );

          return $unionQueryBuilder
            ->union($firstPartQueryBuilder)
            ->addUnion($secondPartQueryBuilder, UnionType::DISTINCT)
            ->orderBy('uid', 'ASC')
            ->executeQuery()
            ->fetchAllAssociative();
      }
    }

which would create following query for MySQL with :php:`$pageIdOne = 100` and
:php:`$pageIdTwo = 10`:

..  code-block:: sql

        (SELECT `uid`, `pid`, `title` FROM pages WHERE `pages`.`uid` = 100)
    UNION
        (SELECT `uid`, `pid`, `title` FROM pages WHERE `pages`.`uid` = 10)
    ORDER BY `uid` ASC


Impact
======

Extension authors can use the new :php:`QueryBuilder` methods to build more
advanced queries.

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