Basic `UNION` support for the `QueryBuilder`

[sbuerk]

Feature Request

Q	A
New Feature	yes
RFC	no

Summary

Introduction / Description

The UNION operator is used to combine the result-set
of two or more 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:

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

Personally, I implemented helper, services
or builders on top/around the Doctrine DBAL
QueryBuilder in countless projects and companies.

Due to requirements for TYPO3 [1]
the need to implement something again raised,
and the whish raised to find a) a simple
way without additional helper or classes and
b) to add it in Doctrine DBAL directly.

Proposal

Following the current structure of the QueryBuilder,
two methods should be added:

• add QueryType::UNION to distinguish the new
  query type from the others internally.
• union(string|QueryBuilder ...$unionParts) as
  setter(reset).
• addUnion(string|QueryBuilder ...$unionParts)
  to add/append additional parts without reseting
  already set parts.
• Reuse distinct(bool $distinct) to differ between
  UNION (DISTINCT) (true) and UNION ALL (false).
• add internal UnionQuery as DTO
• getSQL() needs to be extended to dispatch a new
  method getSQLForUnion() for QueryType::UNION.
• add getSQLForUnion() to dispatch the creation
  of the SQL string to a dedicated SQL Builder using
  the added DTO as transport
• add DefaultUnionSQLBuilder as implementation of
  the added UnionSQLBuilder interface.
• add AbstractPlatform::createUnionSQLBuilder() to
  retrieve the union SQL Builder instance.

Possible usages of the proposed implementation

// ---------------------------------------------------------------------------------------
// plain sql parts to build a `UNION ALL` query
// ---------------------------------------------------------------------------------------
$platform     = $this->connection->getDatabasePlatform();
$qb           = $this->connection->createQueryBuilder();
$select1      = $platform->getDummySelectSQL('2 as field_one');
$select2      = $platform->getDummySelectSQL('1 as field_one');
$select3      = $platform->getDummySelectSQL('4 as field_one');
$select4      = $platform->getDummySelectSQL('3 as field_one');
$qb->union($select1, $select2)
   ->addUnion(...[$select3, $select4])
   ->setMaxResults(1)->setFirstResult(1)->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();
// ---------------------------------------------------------------------------------------

// ---------------------------------------------------------------------------------------
// using QueryBuilder sub instances to build a `UNION ALL` query
// ---------------------------------------------------------------------------------------
$unionQueryBuilder = $this->connection->createQueryBuilder();
$namedParameter1 = $unionQueryBuilder->createNamedParameter(2, ParameterType::INTEGER);
$namedParameter2 = $unionQueryBuilder->createNamedParameter(1, ParameterType::INTEGER);

// sub query 1
$sub1 = $this->connection->createQueryBuilder();
$sub1->select('id')->from('a_table')
  ->where($unionQueryBuilder->expr()->eq('id', $namedParameter1));

// sub query 2
$sub2 = $this->connection->createQueryBuilder();
$sub2->select('id')->from('a_table')
    ->where($unionQueryBuilder->expr()->eq('id', $namedParameter2));

// execute union
$rows = $unionQueryBuilder
  ->union($sub1)
  ->addUnion($sub2)
  ->orderBy('id', 'ASC')
  ->executeQuery()
  ->fetchAllAssociative();
// ---------------------------------------------------------------------------------------

// ---------------------------------------------------------------------------------------
// plain sql parts to build a `UNION <DISTINCT>` query
// ---------------------------------------------------------------------------------------
$platform     = $this->connection->getDatabasePlatform();
$qb           = $this->connection->createQueryBuilder();
$select1      = $platform->getDummySelectSQL('2 as field_one');
$select2      = $platform->getDummySelectSQL('1 as field_one');
$select3      = $platform->getDummySelectSQL('4 as field_one');
$select4      = $platform->getDummySelectSQL('3 as field_one');
$qb->union($select1, $select2)
   ->addUnion(...[$select3, $select4])
   ->distinct()
   ->setMaxResults(1)->setFirstResult(1)->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();
// ---------------------------------------------------------------------------------------

Links

• [1] TYPO3 Github Repository