Are there any reasons not to support UNION in the Query Builder?
To use UNION in my query, currently I use the Query Builder to build two SELECT queries separately, then compile() each of them, and then combine them to one SQL string with ' UNION ' and ORDER BY clause string (which is built manually). The resulted SQL string is passed to DB::query($sql_string, DB::SELECT).
Though it works fine, I simply want to know why it cannot be done without the manual SQL string concatenating.
The documentation says "The query method returns a new Database_Query_Builder object". Actually it returns a Database_Query object, which has no order_by() method, so I have to build an ORDER BY clause string and concatenate it manually aside from the manual ' UNION ' concatenating.
Kohana and Laravel seem to have support for UNION in their Query Builders.
The query builder is a component that came from the Kohana framework, over three years ago, and little has changed since then. The underlying database layer is not very good, so we decided do dump it an rewrite if for Fuel v2.
Which is why not a lot of effort is put into the current code anymore.
This code works. The resulted SQL becomes like below.
"{$sql_from_query1} UNION {$sql_from_query2} {$order_by}"
This sorts the entire result of the UNION.
But I think it's not intuitive, though the result is what I expected in the previous code.
I have three choices.
1. Leave it as it is and send PR. Kohana is justice.
2. Put the UNION combining code before the code appending the ORDER BY and LIMIT clauses in the compile() method, so $query1->union($query2)->order_by($column, $direction) works as expected.
Donate
