Basic UNION support for the QueryBuilder
#6368
Comments
sbuerk
added a commit
to sbuerk/dbal
that referenced
this issue
Apr 25, 2024
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.
Taking the shared requirements and working behaviour,
it is possible to provide a generic support to the
QueryBuilder with a minimalistic surface addition by
adding `union(...)` and `addUnion(...)` methods following
the same logic as `select(...)` and `addSelect(...)` along
with introducing a new internal `QueryType::UNION`.
To avoid to much noise, the `distinct()` method and
property is reused to controll the distinctness of
the build union query. `UNION DISTICT` is the default
and the same `UNION`, which means that without using
`QueryBuilder::distinct()` duplicated rows may be
returned.
It has been consodered to reuse distinct over adding
a additional `union distict / unique rows) property
and method to controll that switch, albeit reverting
the default.
Additional consideration has been, to allow SQL strings
and QueryBuilder for `union()` and `addUnion()` and to
minimize the direct handling of miss-configuration to
the number (minimum of two union parts) and let other
issues like the field (order, count, naming) or not
allowed order by handling to the database itself. With
that, vendor specific implementation can be done if
required.
Technically, the SQL build process is dispatched to a
`DefaultUnionSQLBuilder` along with an `UnionSQLBuilder`
interface, which also allows application to implement
custom behaviour if required.
Example:
```php
$platform = $connection->getDatabasePlatform();
$qb = $>connection->createQueryBuilder();
$select10 = $platform->getDummySelectSQL('2 as field_one');
$select20 = $platform->getDummySelectSQL('1 as field_one');
$qb->union($select10, /*$select11, $select12, ... */)
->addUnion($select20, /*$select21, $select22, ... */)
->setMaxResults(1)
->setFirstResult(1)
->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();
```
This change ...
* adds internal `QueryType::UNION` enum case.
* adds `UnionSQLBuilder interface` and implement
`DefaultUnionSQLBuilder`.
* adds `AbstractPlatform::createUnionSQLBuilder()`.
* adds public QueryBuilder methods `QueryBuilder::union()`,
`QueryBuilder::addUnion()` and `QueryBuilder::uniqueRows()`.
* modifies `QueryBuilder::getSQL()` to build a UNION string,
handled with private method `QueryBuilder::getUnionSQLString()`.
* adds unit and functional tests.
* modifies existing QueryBuilder documentation file.
Resolves doctrine#6368
sbuerk
added a commit
to sbuerk/dbal
that referenced
this issue
Apr 25, 2024
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.
Taking the shared requirements and working behaviour,
it is possible to provide a generic support to the
QueryBuilder with a minimalistic surface addition by
adding `union(...)` and `addUnion(...)` methods following
the same logic as `select(...)` and `addSelect(...)` along
with introducing a new internal `QueryType::UNION`.
To avoid to much noise, the `distinct()` method and
property is reused to controll the distinctness of
the build union query. `UNION DISTICT` is the default
and the same `UNION`, which means that without using
`QueryBuilder::distinct()` duplicated rows may be
returned.
It has been consodered to reuse distinct over adding
a additional `union distict / unique rows) property
and method to controll that switch, albeit reverting
the default.
Additional consideration has been, to allow SQL strings
and QueryBuilder for `union()` and `addUnion()` and to
minimize the direct handling of miss-configuration to
the number (minimum of two union parts) and let other
issues like the field (order, count, naming) or not
allowed order by handling to the database itself. With
that, vendor specific implementation can be done if
required.
Technically, the SQL build process is dispatched to a
`DefaultUnionSQLBuilder` along with an `UnionSQLBuilder`
interface, which also allows application to implement
custom behaviour if required.
Example:
```php
$platform = $connection->getDatabasePlatform();
$qb = $>connection->createQueryBuilder();
$select10 = $platform->getDummySelectSQL('2 as field_one');
$select20 = $platform->getDummySelectSQL('1 as field_one');
$qb->union($select10, /*$select11, $select12, ... */)
->addUnion($select20, /*$select21, $select22, ... */)
->setMaxResults(1)
->setFirstResult(1)
->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();
```
This change ...
* adds internal `QueryType::UNION` enum case.
* adds `UnionSQLBuilder interface` and implement
`DefaultUnionSQLBuilder`.
* adds `AbstractPlatform::createUnionSQLBuilder()`.
* adds public QueryBuilder methods `QueryBuilder::union()`,
`QueryBuilder::addUnion()` and `QueryBuilder::uniqueRows()`.
* modifies `QueryBuilder::getSQL()` to build a UNION string,
handled with private method `QueryBuilder::getUnionSQLString()`.
* adds unit and functional tests.
* modifies existing QueryBuilder documentation file.
Resolves doctrine#6368
sbuerk
added a commit
to sbuerk/dbal
that referenced
this issue
Apr 25, 2024
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.
Taking the shared requirements and working behaviour,
it is possible to provide a generic support to the
QueryBuilder with a minimalistic surface addition by
adding `union(...)` and `addUnion(...)` methods following
the same logic as `select(...)` and `addSelect(...)` along
with introducing a new internal `QueryType::UNION`.
To avoid to much noise, the `distinct()` method and
property is reused to controll the distinctness of
the build union query. `UNION DISTICT` is the default
and the same `UNION`, which means that without using
`QueryBuilder::distinct()` duplicated rows may be
returned.
It has been consodered to reuse distinct over adding
a additional `union distict / unique rows) property
and method to controll that switch, albeit reverting
the default.
Additional consideration has been, to allow SQL strings
and QueryBuilder for `union()` and `addUnion()` and to
minimize the direct handling of miss-configuration to
the number (minimum of two union parts) and let other
issues like the field (order, count, naming) or not
allowed order by handling to the database itself. With
that, vendor specific implementation can be done if
required.
Technically, the SQL build process is dispatched to a
`DefaultUnionSQLBuilder` along with an `UnionSQLBuilder`
interface, which also allows application to implement
custom behaviour if required.
Example:
```php
$platform = $connection->getDatabasePlatform();
$qb = $>connection->createQueryBuilder();
$select10 = $platform->getDummySelectSQL('2 as field_one');
$select20 = $platform->getDummySelectSQL('1 as field_one');
$qb->union($select10, /*$select11, $select12, ... */)
->addUnion($select20, /*$select21, $select22, ... */)
->setMaxResults(1)
->setFirstResult(1)
->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();
```
This change ...
* adds internal `QueryType::UNION` enum case.
* adds `UnionSQLBuilder interface` and implement
`DefaultUnionSQLBuilder`.
* adds `AbstractPlatform::createUnionSQLBuilder()`.
* adds public QueryBuilder methods `QueryBuilder::union()`,
`QueryBuilder::addUnion()` and `QueryBuilder::uniqueRows()`.
* modifies `QueryBuilder::getSQL()` to build a UNION string,
handled with private method `QueryBuilder::getUnionSQLString()`.
* adds unit and functional tests.
* modifies existing QueryBuilder documentation file.
Resolves doctrine#6368
sbuerk
added a commit
to sbuerk/dbal
that referenced
this issue
Apr 25, 2024
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.
Taking the shared requirements and working behaviour,
it is possible to provide a generic support to the
QueryBuilder with a minimalistic surface addition by
adding `union(...)` and `addUnion(...)` methods following
the same logic as `select(...)` and `addSelect(...)` along
with introducing a new internal `QueryType::UNION`.
To avoid to much noise, the `distinct()` method and
property is reused to controll the distinctness of
the build union query. `UNION DISTICT` is the default
and the same `UNION`, which means that without using
`QueryBuilder::distinct()` duplicated rows may be
returned.
It has been consodered to reuse distinct over adding
a additional `union distict / unique rows) property
and method to controll that switch, albeit reverting
the default.
Additional consideration has been, to allow SQL strings
and QueryBuilder for `union()` and `addUnion()` and to
minimize the direct handling of miss-configuration to
the number (minimum of two union parts) and let other
issues like the field (order, count, naming) or not
allowed order by handling to the database itself. With
that, vendor specific implementation can be done if
required.
Technically, the SQL build process is dispatched to a
`DefaultUnionSQLBuilder` along with an `UnionSQLBuilder`
interface, which also allows application to implement
custom behaviour if required.
Example:
```php
$platform = $connection->getDatabasePlatform();
$qb = $>connection->createQueryBuilder();
$select10 = $platform->getDummySelectSQL('2 as field_one');
$select20 = $platform->getDummySelectSQL('1 as field_one');
$qb->union($select10, /*$select11, $select12, ... */)
->addUnion($select20, /*$select21, $select22, ... */)
->setMaxResults(1)
->setFirstResult(1)
->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();
```
This change ...
* adds internal `QueryType::UNION` enum case.
* adds `UnionSQLBuilder interface` and implement
`DefaultUnionSQLBuilder`.
* adds `AbstractPlatform::createUnionSQLBuilder()`.
* adds public QueryBuilder methods `QueryBuilder::union()`,
`QueryBuilder::addUnion()` and `QueryBuilder::uniqueRows()`.
* modifies `QueryBuilder::getSQL()` to build a UNION string,
handled with private method `QueryBuilder::getUnionSQLString()`.
* adds unit and functional tests.
* modifies existing QueryBuilder documentation file.
Resolves doctrine#6368
sbuerk
added a commit
to sbuerk/dbal
that referenced
this issue
Apr 25, 2024
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.
Taking the shared requirements and working behaviour,
it is possible to provide a generic support to the
QueryBuilder with a minimalistic surface addition now,
and following methods are added:
* `union(string|QueryBuilder ...$unionParts)` and
`addUnion(string|QueryBuilder ...$unionParts)` to
create a `UNION` query retrieving unique rows
* `unionAll(string|QueryBuilder ...$unionParts)` and
`addUnionAll(string|QueryBuilder ...$unionParts) to
create a `UNION ALL` query retrieving eventually
duplicated rows.
This follows the generic logic as `select(...)` and
`addSelect(...)` along with introducing new internal
`QueryType::UNION_DISTINCT` and `QueryType::UNION_ALL`
enum cases.
Additional to the consideration to allow SQL strings
and QueryBuilder for `union()`, `unionAll(), `addUnion()`
and `addUnionAll()` and minimize the direct handling
of miss-configuration to the number of provided parts
and let other issues like the field (order, count, naming)
or not allowed order by handling to the database itself.
With that, vendor specific implementation can be done if
required.
Technically, the SQL build process is dispatched to a
`DefaultUnionSQLBuilder` along with an `UnionSQLBuilder`
interface, which also allows application to implement
custom behaviour if required.
Example:
```php
$platform = $connection->getDatabasePlatform();
$qb = $>connection->createQueryBuilder();
$select10 = $platform->getDummySelectSQL('2 as field_one');
$select20 = $platform->getDummySelectSQL('1 as field_one');
$qb->union($select10, /*$select11, $select12, ... */)
->addUnion($select20, /*$select21, $select22, ... */)
->setMaxResults(1)
->setFirstResult(1)
->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();
```
Unit and functional tests are added to demonstrate the
implementation and cover it for future changes.
Resolves: doctrine#6368
sbuerk
added a commit
to sbuerk/dbal
that referenced
this issue
Apr 25, 2024
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.
Taking the shared requirements and working behaviour,
it is possible to provide a generic support to the
QueryBuilder with a minimalistic surface addition now,
and following methods are added:
* `union(string|QueryBuilder ...$unionParts)` and
`addUnion(string|QueryBuilder ...$unionParts)` to
create a `UNION` query retrieving unique rows
* `unionAll(string|QueryBuilder ...$unionParts)` and
`addUnionAll(string|QueryBuilder ...$unionParts) to
create a `UNION ALL` query retrieving eventually
duplicated rows.
This follows the generic logic as `select(...)` and
`addSelect(...)` along with introducing new internal
`QueryType::UNION_DISTINCT` and `QueryType::UNION_ALL`
enum cases.
Additional to the consideration to allow SQL strings
and QueryBuilder for `union()`, `unionAll(), `addUnion()`
and `addUnionAll()` and minimize the direct handling
of miss-configuration to the number of provided parts
and let other issues like the field (order, count, naming)
or not allowed order by handling to the database itself.
With that, vendor specific implementation can be done if
required.
Technically, the SQL build process is dispatched to a
`DefaultUnionSQLBuilder` along with an `UnionSQLBuilder`
interface, which also allows application to implement
custom behaviour if required.
Example:
```php
$platform = $connection->getDatabasePlatform();
$qb = $>connection->createQueryBuilder();
$select10 = $platform->getDummySelectSQL('2 as field_one');
$select20 = $platform->getDummySelectSQL('1 as field_one');
$qb->union($select10, /*$select11, $select12, ... */)
->addUnion($select20, /*$select21, $select22, ... */)
->setMaxResults(1)
->setFirstResult(1)
->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();
```
Unit and functional tests are added to demonstrate the
implementation and cover it for future changes.
Resolves: doctrine#6368
sbuerk
added a commit
to sbuerk/dbal
that referenced
this issue
Apr 25, 2024
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.
Taking the shared requirements and working behaviour,
it is possible to provide a generic support to the
QueryBuilder with a minimalistic surface addition now,
and following methods are added:
* `union(string|QueryBuilder ...$unionParts)` and
`addUnion(string|QueryBuilder ...$unionParts)` to
create a `UNION` query retrieving unique rows
* `unionAll(string|QueryBuilder ...$unionParts)` and
`addUnionAll(string|QueryBuilder ...$unionParts) to
create a `UNION ALL` query retrieving eventually
duplicated rows.
This follows the generic logic as `select(...)` and
`addSelect(...)` along with introducing new internal
`QueryType::UNION_DISTINCT` and `QueryType::UNION_ALL`
enum cases.
Additional to the consideration to allow SQL strings
and QueryBuilder for `union()`, `unionAll(), `addUnion()`
and `addUnionAll()` and minimize the direct handling
of miss-configuration to the number of provided parts
and let other issues like the field (order, count, naming)
or not allowed order by handling to the database itself.
With that, vendor specific implementation can be done if
required.
Technically, the SQL build process is dispatched to a
`DefaultUnionSQLBuilder` along with an `UnionSQLBuilder`
interface, which also allows application to implement
custom behaviour if required.
Example:
```php
$platform = $connection->getDatabasePlatform();
$qb = $>connection->createQueryBuilder();
$select10 = $platform->getDummySelectSQL('2 as field_one');
$select20 = $platform->getDummySelectSQL('1 as field_one');
$qb->union($select10, /*$select11, $select12, ... */)
->addUnion($select20, /*$select21, $select22, ... */)
->setMaxResults(1)
->setFirstResult(1)
->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();
```
Unit and functional tests are added to demonstrate the
implementation and cover it for future changes.
Resolves: doctrine#6368
sbuerk
added a commit
to sbuerk/dbal
that referenced
this issue
Apr 25, 2024
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.
Taking the shared requirements and working behaviour,
it is possible to provide a generic support to the
QueryBuilder with a minimalistic surface addition now,
and following methods are added:
* `union(string|QueryBuilder ...$unionParts)` and
`addUnion(string|QueryBuilder ...$unionParts)` to
create a `UNION` query retrieving unique rows
* `unionAll(string|QueryBuilder ...$unionParts)` and
`addUnionAll(string|QueryBuilder ...$unionParts) to
create a `UNION ALL` query retrieving eventually
duplicated rows.
This follows the generic logic as `select(...)` and
`addSelect(...)` along with introducing new internal
`QueryType::UNION_DISTINCT` and `QueryType::UNION_ALL`
enum cases.
Additional to the consideration to allow SQL strings
and QueryBuilder for `union()`, `unionAll(), `addUnion()`
and `addUnionAll()` and minimize the direct handling
of miss-configuration to the number of provided parts
and let other issues like the field (order, count, naming)
or not allowed order by handling to the database itself.
With that, vendor specific implementation can be done if
required.
Technically, the SQL build process is dispatched to a
`DefaultUnionSQLBuilder` along with an `UnionSQLBuilder`
interface, which also allows application to implement
custom behaviour if required.
Example:
```php
$platform = $connection->getDatabasePlatform();
$qb = $>connection->createQueryBuilder();
$select10 = $platform->getDummySelectSQL('2 as field_one');
$select20 = $platform->getDummySelectSQL('1 as field_one');
$qb->union($select10, /*$select11, $select12, ... */)
->addUnion($select20, /*$select21, $select22, ... */)
->setMaxResults(1)
->setFirstResult(1)
->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();
```
Unit and functional tests are added to demonstrate the
implementation and cover it for future changes.
Resolves: doctrine#6368
sbuerk
added a commit
to sbuerk/dbal
that referenced
this issue
Apr 25, 2024
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.
Taking the shared requirements and working behaviour,
it is possible to provide a generic support to the
QueryBuilder with a minimalistic surface addition now,
and following methods are added:
* `union(string|QueryBuilder ...$unionParts)` and
`addUnion(string|QueryBuilder ...$unionParts)` to
create a `UNION` query retrieving unique rows
* `unionAll(string|QueryBuilder ...$unionParts)` and
`addUnionAll(string|QueryBuilder ...$unionParts) to
create a `UNION ALL` query retrieving eventually
duplicated rows.
This follows the generic logic as `select(...)` and
`addSelect(...)` along with introducing new internal
`QueryType::UNION_DISTINCT` and `QueryType::UNION_ALL`
enum cases.
Additional to the consideration to allow SQL strings
and QueryBuilder for `union()`, `unionAll(), `addUnion()`
and `addUnionAll()` and minimize the direct handling
of miss-configuration to the number of provided parts
and let other issues like the field (order, count, naming)
or not allowed order by handling to the database itself.
With that, vendor specific implementation can be done if
required.
Technically, the SQL build process is dispatched to a
`DefaultUnionSQLBuilder` along with an `UnionSQLBuilder`
interface, which also allows application to implement
custom behaviour if required.
Example:
```php
$platform = $connection->getDatabasePlatform();
$qb = $>connection->createQueryBuilder();
$select10 = $platform->getDummySelectSQL('2 as field_one');
$select20 = $platform->getDummySelectSQL('1 as field_one');
$qb->union($select10, /*$select11, $select12, ... */)
->addUnion($select20, /*$select21, $select22, ... */)
->setMaxResults(1)
->setFirstResult(1)
->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();
```
Unit and functional tests are added to demonstrate the
implementation and cover it for future changes.
Resolves: doctrine#6368
sbuerk
added a commit
to sbuerk/dbal
that referenced
this issue
Apr 27, 2024
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.
Taking the shared requirements and working behaviour,
it is possible to provide a generic support to the
QueryBuilder with a minimalistic surface addition now,
and following methods are added:
* `union(string|QueryBuilder ...$unionParts)` and
`addUnion(string|QueryBuilder ...$unionParts)` to
create a `UNION` query retrieving unique rows
* `unionAll(string|QueryBuilder ...$unionParts)` and
`addUnionAll(string|QueryBuilder ...$unionParts) to
create a `UNION ALL` query retrieving eventually
duplicated rows.
This follows the generic logic as `select(...)` and
`addSelect(...)` along with introducing new internal
`QueryType::UNION_DISTINCT` and `QueryType::UNION_ALL`
enum cases.
Additional to the consideration to allow SQL strings
and QueryBuilder for `union()`, `unionAll(), `addUnion()`
and `addUnionAll()` and minimize the direct handling
of miss-configuration to the number of provided parts
and let other issues like the field (order, count, naming)
or not allowed order by handling to the database itself.
With that, vendor specific implementation can be done if
required.
Technically, the SQL build process is dispatched to a
`DefaultUnionSQLBuilder` along with an `UnionSQLBuilder`
interface, which also allows application to implement
custom behaviour if required.
Example:
```php
$platform = $connection->getDatabasePlatform();
$qb = $>connection->createQueryBuilder();
$select10 = $platform->getDummySelectSQL('2 as field_one');
$select20 = $platform->getDummySelectSQL('1 as field_one');
$qb->union($select10, /*$select11, $select12, ... */)
->addUnion($select20, /*$select21, $select22, ... */)
->setMaxResults(1)
->setFirstResult(1)
->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();
```
Unit and functional tests are added to demonstrate the
implementation and cover it for future changes.
Resolves: doctrine#6368
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Feature Request
Summary
Introduction / DescriptionThe
UNIONoperator is used to combine the result-setof two or more
SELECTstatements, which all databasevendors supports with usual specialities for each.
Still, there is a common shared subset which works for
all of them:
with shared common requirements:
Each
SELECTmust return the same fieldsin number, naming and order.
Each
SELECTmust not haveORDER BY,expect MySQL allowing it to be used as sub
query expression encapsulated in parentheses.
Personally, I implemented
helper,servicesor
builderson top/around the Doctrine DBALQueryBuilder 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 simpleway without additional helper or classes and
b)to add it in Doctrine DBAL directly.ProposalFollowing the current structure of the
QueryBuilder,two methods should be added:
QueryType::UNIONto distinguish the newquery type from the others internally.
union(string|QueryBuilder ...$unionParts)assetter(reset).
addUnion(string|QueryBuilder ...$unionParts)to add/append additional parts without reseting
already set parts.
distinct(bool $distinct)to differ betweenUNION (DISTINCT)(true) andUNION ALL(false).UnionQueryas DTOgetSQL()needs to be extended to dispatch a newmethod
getSQLForUnion()forQueryType::UNION.getSQLForUnion()to dispatch the creationof the SQL string to a dedicated SQL Builder using
the added DTO as transport
DefaultUnionSQLBuilderas implementation ofthe added
UnionSQLBuilderinterface.AbstractPlatform::createUnionSQLBuilder()toretrieve the union SQL Builder instance.
Possible usages of the proposed implementationLinksThe text was updated successfully, but these errors were encountered: