Orm: Support UNION in QueryBuilder

[olegkrivtsov]

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.

[Ocramius]

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]

@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

[olegkrivtsov]

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?

[Ocramius]

@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.

[Raffaello]

@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