auraphp/Aura.SqlQuery

Allow passing query to `union()`

RentecTravis opened this issue · 0 comments

It would be convenient to be able to pass a query directly to union() instead of rerunning the same commands on the original query.

Here's what I thought I would be able to do. I've simplified the queries from my RL example to make them easier to read. The more complicated the similar union()ed queries are, the more appreciated the requested change would be.

$txns_by_owner = $this->queryBuilder->newSelect()
    ->cols($this->getBalanceFields())
    ->from('transactions AS t');

// the two subqueries are mostly the same. Clone them now, add differing elements after
$txns_by_property = clone $txns_by_owner;
$txns_by_owner->where('t.owner_id = :owner_id');

$txns_by_property
    ->join('INNER', 'properties AS p', 'p.property_id = t.property_id')
    ->where('p.owner_id = :owner_id')
;

return $this->queryBuilder->newSelect()
    ->cols(['SUM(amount) AS amount'])
    ->fromSubSelect($txns_by_owner->union($txns_by_property));

It seems what I actually have to do is this

$subquery = $this->queryBuilder->newSelect();

$subquery->cols($this->getBalanceFields())
    ->from('transactions AS t')
    ->where('t.owner_id = :owner_id')
;

$subquery->union()
    ->cols($this->getBalanceFields())
    ->from('transactions AS t')
    ->join('INNER', 'properties AS p', 'p.property_id = t.property_id')
    ->where('p.owner_id = :owner_id')
;

return $this->queryBuilder->newSelect()
    ->cols(['SUM(amount) AS amount'])
    ->fromSubSelect($subquery, 't1')
;