Skip to content

Allow passing query to union() #189

Open
@RentecTravis

Description

@RentecTravis

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')
;

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions