Add union() to DB Query Builder

A possible now (CI3) usage of the query builder:

Code:

$sql = array();

$sql[] = '('.$this->db
    ->select("'XX' AS country_code, '-- Not specified --' AS country_name")
    ->get_compiled_select()
    .')';

$sql[] = '('.$this->db
    ->select("country_code, country_name")
    ->from('countries')
    ->where_in('country_code', array('US'))
    ->order_by('country_name', 'asc')
    ->get_compiled_select()
    .')';

$sql[] = '('.$this->db
    ->select("country_code, country_name")
    ->from('countries')
    ->where_not_in('country_code', array('US'))
    ->order_by('country_name', 'asc')
    ->get_compiled_select()
    .')';

$sql = implode('
UNION
', $sql);

echo $sql;

The good thing is that in this simple and plausible example I don't have to bother about the SQL dialect, here are some generated results:

MySQL:

Code:

(SELECT 'XX' AS country_code, '-- Not specified --' AS country_name)
UNION
(SELECT `country_code`, `country_name`
FROM `countries`
WHERE `country_code` IN('US')
ORDER BY `country_name` ASC)
UNION
(SELECT `country_code`, `country_name`
FROM `countries`
WHERE `country_code` NOT IN('US')
ORDER BY `country_name` ASC)

PostgreSQL:

Code:

(SELECT 'XX' AS country_code, '-- Not specified --' AS country_name)
UNION
(SELECT "country_code", "country_name"
FROM "countries"
WHERE "country_code" IN('US')
ORDER BY "country_name" ASC)
UNION
(SELECT "country_code", "country_name"
FROM "countries"
WHERE "country_code" NOT IN('US')
ORDER BY "country_name" ASC)

I don't know how tricky the implementation of UNION within the query builder might be. At least I can see that for every sub-query a separate instance of the builder object would be needed. But even without such an implementation, usage of the query builder is possible as it was shown.

Note: As far as I can remember PostgerSQL would ignore the ORDER BY clause within the sub-queries in this case, but this is not my problem. :-)