Support UNION in QueryBuilder #5014

New Issue

admin · 2026-01-22T14:56:33+01:00

admin commented

2026-01-22 14:56:33 +01:00

Originally created by @olegkrivtsov on GitHub (Feb 8, 2016).

Originally assigned to: @Ocramius on GitHub.

Hi,

Why the QueryBuilder doesn't allow to create UNION queries? In my concrete situation, I need a UNION query instead of JOIN because of performance reason.

Originally created by @olegkrivtsov on GitHub (Feb 8, 2016). Originally assigned to: @Ocramius on GitHub. Hi, Why the QueryBuilder doesn't allow to create UNION queries? In my concrete situation, I need a UNION query instead of JOIN because of performance reason.

admin added the Improvement Invalid labels 2026-01-22 14:56:33 +01:00

admin closed this issue

2026-01-22 14:56:33 +01:00

admin commented

2026-01-22 14:56:34 +01:00

@Ocramius commented on GitHub (Feb 8, 2016):

DQL doesn't support UNION, as UNION allows aggregating non-homogeneous datasets, which doesn't really work in a strictly typed DSL (in this case DQL).

@Ocramius commented on GitHub (Feb 8, 2016): DQL doesn't support `UNION`, as `UNION` allows aggregating non-homogeneous datasets, which doesn't really work in a strictly typed DSL (in this case DQL).

admin commented

2026-01-22 14:56:34 +01:00

@Ocramius commented on GitHub (Feb 8, 2016):

@olegkrivtsov this is one of those cases where you'd just really use raw SQL instead of a high level abstraction. Having an UNION that works in this context (and can't be efficiently reproduced with multiple queries) is really rare

@Ocramius commented on GitHub (Feb 8, 2016): @olegkrivtsov this is one of those cases where you'd just really use raw SQL instead of a high level abstraction. Having an `UNION` that works in this context (and can't be efficiently reproduced with multiple queries) is _really_ rare

admin commented

2026-01-22 14:56:34 +01:00

@olegkrivtsov commented on GitHub (Feb 8, 2016):

Hi @Ocramius I can replace the UNION with several simple DQL queries, but I'll have to merge and sort the results somehow. Does Doctrine have a way to merge/order the results of several DQL queries?

@olegkrivtsov commented on GitHub (Feb 8, 2016): Hi @Ocramius I can replace the UNION with several simple DQL queries, but I'll have to merge and sort the results somehow. Does Doctrine have a way to merge/order the results of several DQL queries?

admin commented

2026-01-22 14:56:35 +01:00

@Ocramius commented on GitHub (Feb 8, 2016):

@olegkrivtsov good point. No, there is no such thing in DQL itself: that would be an array merge operation, IMO.

Assuming you have a query (pseudo-code, silly, but serves the purpose of the example) like following:

SELECT f FROM
(SELECT f FROM Foo f WHERE f.id > 100)
UNION ALL (SELECT f FROM Foo f WHERE f.id < -100)
UNION ALL (SELECT f FROM Foo f WHERE f.id = 50)

You can probably re-write this query to be a single SQL (SQL, not DQL!) query that extracts just the identifiers:

SELECT id FROM
(SELECT id, someVal FROM foo WHERE id > 100)
UNION ALL (SELECT id, someVal FROM foo WHERE id < -100)
UNION ALL (SELECT id, someVal FROM foo WHERE id = 50)
ORDER BY someVal ASC

Then select the entities by identifier (careful: works only up to 1000 elements on some PDO wrappers):

$secondQueryResult = $em
    ->createQuery('SELECT f FROM Foo f INDEX BY f.id WHERE f.id IN (:identifiers)')
    ->setParameter('identifiers', $firstQueryResult)
    ->getResult();

Then loop over $firstQueryResult and fetch the data:

foreach ($firstQueryResult as $id) {
    $secondQueryResult[$id]->doSomething();
}

This is just an example, of course, but I hope it helps.

@Ocramius commented on GitHub (Feb 8, 2016): @olegkrivtsov good point. No, there is no such thing in DQL itself: that would be an array merge operation, IMO. Assuming you have a query (pseudo-code, silly, but serves the purpose of the example) like following: ``` sql SELECT f FROM (SELECT f FROM Foo f WHERE f.id > 100) UNION ALL (SELECT f FROM Foo f WHERE f.id < -100) UNION ALL (SELECT f FROM Foo f WHERE f.id = 50) ``` You can probably re-write this query to be a single SQL (SQL, not DQL!) query that extracts just the identifiers: ``` sql SELECT id FROM (SELECT id, someVal FROM foo WHERE id > 100) UNION ALL (SELECT id, someVal FROM foo WHERE id < -100) UNION ALL (SELECT id, someVal FROM foo WHERE id = 50) ORDER BY someVal ASC ``` Then select the entities by identifier (careful: works only up to 1000 elements on some PDO wrappers): ``` php $secondQueryResult = $em ->createQuery('SELECT f FROM Foo f INDEX BY f.id WHERE f.id IN (:identifiers)') ->setParameter('identifiers', $firstQueryResult) ->getResult(); ``` Then loop over `$firstQueryResult` and fetch the data: ``` php foreach ($firstQueryResult as $id) { $secondQueryResult[$id]->doSomething(); } ``` This is just an example, of course, but I hope it helps.

admin commented

2026-01-22 14:56:35 +01:00

@Raffaello commented on GitHub (Sep 8, 2016):

@Ocramius Would it be possible at least to return the parameter key names from a getSQL() method?
because they are all replaced by ? and in the case from a query builder retrieve the SQL, make the union, with another SQL and then run the native query, it is so quite annoying and error prone remap every single parameter without the key.

$qb =  $em->createQueryBuilder('a')
->where('id = :id')
->setParameter(':id', 1);  // this will be lost when getSQL()
$rsm = new ResultSetMapping();
        $native = $em->createNativeQuery(
'('
. $qb->getQuery()->getSQL()
. ") UNION ("
. $rqb->getQuery()->getSQL() 
. ")",
 $rsm
 );

foreach ($qb->getParameters() as $k => $p) {
           //$native->setParameter($k, $p->getValue(), $p->getType());
           $native->setParameter($p->getName(), $p->getValue(), $p->getType());
        }
$native->getResult();

Even more how to deal with HIDDEN DQL column when retrieving the SQL? if there is a way..

thanks

@Raffaello commented on GitHub (Sep 8, 2016): @Ocramius Would it be possible at least to return the parameter key names from a `getSQL()` method? because they are all replaced by `?` and in the case from a query builder retrieve the SQL, make the union, with another SQL and then run the native query, it is so quite annoying and error prone remap every single parameter without the key. ``` $qb = $em->createQueryBuilder('a') ->where('id = :id') ->setParameter(':id', 1); // this will be lost when getSQL() $rsm = new ResultSetMapping(); $native = $em->createNativeQuery( '(' . $qb->getQuery()->getSQL() . ") UNION (" . $rqb->getQuery()->getSQL() . ")", $rsm ); foreach ($qb->getParameters() as $k => $p) { //$native->setParameter($k, $p->getValue(), $p->getType()); $native->setParameter($p->getName(), $p->getValue(), $p->getType()); } $native->getResult(); ``` Even more how to deal with `HIDDEN` DQL column when retrieving the SQL? if there is a way.. thanks

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5014