Add union() to DB Query Builder

It would be nice to have union() in CI's Query Builder 

I don't know. With a properly designed database, there's almost never a good reason to use UNION. There are no performance benefits to using it vs separate queries. It's rarely needed.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

@skunkbad

Code:

-- Filling a dropdown menu about citizenship
(SELECT 'XX' AS country_code, '-- Not specified' AS country_name)
UNION
-- The most probable option(s) is moved on top for user convenience
(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)

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. :-)

I don't know. With a properly designed database, there's almost never a good reason to use UNION. There are no performance benefits to using it vs separate queries. It's rarely needed.

I completely agree with skunkbad here. I've even logged in after a couple of months of lurking to comment.

Using UNION is a Dark Path and should be avoided as much as possible.

Codeigniter's strength is being lean. The introduction of db->union() would bloat CI and then promote bad practice.

BUT - I know the world is a messy place and occasionally we need to engineer nasty solutions, so perhaps:

1. Do what skunkbad has said and run 2 queries or
   
2. this type of function is best left to a Library that could be maintained outside CI.
   

UNION is just a tool and it is programmer's responsibility to decide where and why to use it.

db->union(): I can imagine something else (CI3-like syntax):

Code:

$result = $this->db
    ->union_start()
        ->select("'XX' AS country_code, '-- Not specified --' AS country_name")
    ->union_end()
    ->union_start()
        ->select("country_code, country_name")
        ->from('countries')
        ->where_in('country_code', array('US'))
        ->order_by('country_name', 'asc')
    ->union_end()
    ->union_start()
        ->select("country_code, country_name")
        ->from('countries')
        ->where_not_in('country_code', array('US'))
        ->order_by('country_name', 'asc')
    ->union_end()
    // For other cases: A place for clauses that affect the whole result: ORDER BY, LIMIT, etc.
    ->get()
    ->result_array();

The only problem is how hard the implementation of UNION would be.

@skunkbad

Code:

-- Filling a dropdown menu about citizenship
(SELECT 'XX' AS country_code, '-- Not specified' AS country_name)
UNION
-- The most probable option(s) is moved on top for user convenience
(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)

1) I know you know that you could just put that in $this->db->query().

2) I'd probably just pull them in in a single query and then process them through PHP.

3) That query could be simplified with an "ORDER BY `country_name` = 'United States', `country_name` ASC", and add the empty option with PHP.

4) I suppose if you want MySQL to do all the work, you're right.

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

The development effort would not be trivial, especially when you have to ensure it works across every platform we end up supporting. I wouldn't expect this to happen prior to release, if it happens at all.

And everyone seems to forget you can always bypass the query builder and simply use query() with query bindings to keep it safe. Simplifies things for everyone.

Support Development •  CodeIgniter 4 Foundations • Practical CodeIgniter 3  • CodeIgniter Tutorials

@zurtri

Why "Using UNION is a Dark Path and should be avoided as much as possible." ?

And everyone seems to forget you can always bypass the query builder and simply use query() with query bindings to keep it safe. Simplifies things for everyone.

This is what I do on very complex queries (or dynamic queries). And it works fine with the UNION command as I've used it for that (although in hindsight, I should have done two queries and just merged the results...)