UNION query with codeigniter's active record pattern

Question

How to do UNION query with PHP CodeIgniter framework's active record query format?

Quacksalver

Answer 1

CodeIgniter's ActiveRecord doesn't support UNION, so you would just write your query and use the ActiveRecord's query method.

$this->db->query('SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2');

Greenfinch

To explain, CodeIgniter's ActiveRecord only supports SQL features that are compatible with all its supported SQL types (or implements them in its own way). The idea of ActiveRecord is to abstract the database type to be database independant and let people move from MySQL to MSSQL or whatever else without major issue. If they tried to add unison it would screw with other database types. – Arlon

database independant and let people move from MySQL to MSSQL or whatever else without major issue – Cubeb

Name one popular RDBMS that does not support UNION? Other ORMs (± ActiveRecord semantics), such as SQLAlchemy, offer excellent support for UNIONs and JOINs (of various kinds) across all database back-ends, including SQLite. For back-ends which do not support it directly (e.g. SQLite), the ORM makes it work by doing a little bit more behind-the-scenes while still maintaining portability. In this particular case, by performing the query manually you lose all semblance of portability in addition to the more advanced features of the ActiveRecord system itself (e.g. table filtering). – Spratt

This is not pure active record query. I was having same requirement. I got solution & given answer. – Nevarez

how can i add order by in this query? – Taskmaster

Answer 2

By doing union using last_query(), it may hamper performance of application. Because for single union it would require to execute 3 queries. i.e for "n" union "n+1" queries. It won't much affect for 1-2 query union. But it will give problem if union of many queries or tables having large data.

This link will help you a lot: active record subqueries

We can combine active record with manual queries. Example:

// #1 SubQueries no.1 -------------------------------------------

$this->db->select('title, content, date');
$this->db->from('mytable');
$query = $this->db->get();
$subQuery1 = $this->db->_compile_select();

$this->db->_reset_select();

// #2 SubQueries no.2 -------------------------------------------

$this->db->select('title, content, date');
$this->db->from('mytable2');
$query = $this->db->get();
$subQuery2 = $this->db->_compile_select();

$this->db->_reset_select();

// #3 Union with Simple Manual Queries --------------------------

$this->db->query("select * from ($subQuery1 UNION $subQuery2) as unionTable");

// #3 (alternative) Union with another Active Record ------------

$this->db->from("($subQuery1 UNION $subQuery2)");
$this->db->get();

Nevarez

what is the use of => $query = $this->db->get(); ? $query doesn't seems to use anywhere in the code. – Howes

Fantastic! Good idea. This is a real union. The other one runs 2 queries on the database. I sometimes wonder if developers care at all about database performance. – Cos

Answer 3

This is a quick and dirty method I once used

// Query #1

$this->db->select('title, content, date');
$this->db->from('mytable1');
$query1 = $this->db->get()->result();

// Query #2

$this->db->select('title, content, date');
$this->db->from('mytable2');
$query2 = $this->db->get()->result();

// Merge both query results

$query = array_merge($query1, $query2);

Not my finest work, but it solved my problem.

note: I didn't need to order the result.

Sharkskin

Bad idea if your data is changing via some asynchronous process, data may get added/deleted/changed in between the two queries. – Newport

Better way is to use subqueries like @Somnath Muluk did in the post above yours. Two queries in a separated processess are bad idea as user9645 wrote. – Fulmination

Answer 4

You may use the following method to get the SQL statement in the model:

$this->db->select('DISTINCT(user_id)');
$this->db->from('users_master');
$this->db->where('role_id', '1');

$subquery = $this->db->_compile_select();
$this->db->_reset_select();

This way the SQL statement will be in the $subquery variable, without actually executing it.

You have asked this question a long time ago, so maybe you have already got the answer. if not, this process may do the trick.

Brokaw

_compile_select() is a protected method in the latest version of CodeIgniter (the underscore indicates that it was intended as an internal method in the first place). See my answer for a workaround: https://mcmap.net/q/445665/-union-query-with-codeigniter-39-s-active-record-pattern – Brokerage

Answer 5

by modifying somnath huluks answer, i add these following variable and functions to DB_Active_rec class as follows:

class DB_Active_records extends CI_DB_Driver
{

   ....

   var $unions;

   ....

    public function union_push($table = '')
    {
        if ($table != '')
        {
            $this->_track_aliases($table);
            $this->from($table);
        }

        $sql = $this->_compile_select();

        array_push($this->unions, $sql);
        $this->_reset_select();
    }

    public function union_flush()
    {
        $this->unions = array();
    }

    public function union()
    {
        $sql = '('.implode(') union (', $this->unions).')';
        $result = $this->query($sql);
        $this->union_flush();
        return $result;
    }

    public function union_all()
    {
        $sql = '('.implode(') union all (', $this->unions).')';
        $result = $this->query($sql);
        $this->union_flush();
        return $result;
    }
}

therefore you can virtually use unions without dependencies to db_driver.

to use union with this method, you simply make regular active record queries, but calling union_push instead of get.

note: you have to ensure your queries have matching columns like regular unions

example:

    $this->db->select('l.tpid, l.lesson, l.lesson_type, l.content, l.file');
    $this->db->where(array('l.requirement' => 0));
    $this->db->union_push('lessons l');
    $this->db->select('l.tpid, l.lesson, l.lesson_type, l.content, l.file');
    $this->db->from('lessons l');
    $this->db->join('scores s', 'l.requirement = s.lid');
    $this->db->union_push();
    $query = $this->db->union_all();
    return $query->result_array();

would produce:

(SELECT `l`.`tpid`, `l`.`lesson`, `l`.`lesson_type`, `l`.`content`, `l`.`file`
FROM `lessons` l
WHERE `l`.`requirement`=0)
union all 
(SELECT `l`.`tpid`, `l`.`lesson`, `l`.`lesson_type`, `l`.`content`, `l`.`file`
FROM `lessons` l
JOIN `scores` s ON `l`.`requirement`=`s`.`lid`)

Umbra

Answer 6

I found this library, which worked nicely for me to add UNION in an ActiveRecord style:

https://github.com/NTICompass/CodeIgniter-Subqueries

BUT I had to grab the get_compiled_select() method from the dev branch of CodeIgniter first (available here: https://github.com/EllisLab/CodeIgniter/blob/develop/system/database/DB_query_builder.php -- DB_query_builder will be replacing DB_active_rec). Presumably this method will be available in a future production release of CodeIgniter.

Once I added that method to DB_active_rec.php in system/database it worked like a charm. (I didn't want to use the dev version of CodeIgniter as this is a production app.)

Brokerage

I am in a dilemma here and need clarification. Does adding the get_compiled_select method in DB_active_rec.php will make the library work properly ? And do we have to use the added method for combining the results ? I see no mention about this method on the GitHub repository of the library you mentioned for performing UNION. And will it perform UNION ALL ? – Gentlewoman

@RocketHazmat: See the above comment and clarify my doubt. I am not able to run my UNION ALL query according to your example shown. – Gentlewoman

@SilentAssassin: Adding get_compiled_select will make the library work. You don't need to call it yourself, it's called internally by the library. It does do UNION ALL. To use UNIONs you do this: $sub1 = $this->subquery->start_union(); $sub1->select('a')->from('b')->where('c', 'd'); $sub2 = $this->subquery->start_union(); $sub2->select('a2')->from('b2')->where('c2', 'd2'); $this->subquery->end_union(); $query = $this->db->get();. – Ligulate

@RocketHazmat See this link. This is what I am trying to do. I know the ORDER BY is creating issues. How to write it before start_union ? The $sub1 won't be initialized before that so I've to write it using db object ? – Gentlewoman

@SilentAssassin: The individual SELECTs in a UNION cannot have an ORDER BY. You can only have one ORDER BY for the complete result set. What I meant by "before start_union" was to write $this->db->order_by() before sub1 = $this->subquery->start_union();, because there may be syntax errors if you write it after. I'm working on that. – Ligulate

Answer 7

This is solution I am using:

$union_queries = array();
$tables = array('table1','table2'); //As much as you need
foreach($tables as $table){
    $this->db->select(" {$table}.row1, 
                        {$table}.row2,
                        {$table}.row3");
    $this->db->from($table);
    //I have additional join too (removed from this example)
    $this->db->where('row4',1);
    $union_queries[] = $this->db->get_compiled_select();
}
$union_query = join(' UNION ALL ',$union_queries); // I use UNION ALL
$union_query .= " ORDER BY row1 DESC LIMIT 0,10";
$query = $this->db->query($union_query);

Linked

Answer 8

try this one

function get_merged_result($ids){                   
    $this->db->select("column");
    $this->db->distinct();
    $this->db->from("table_name");
    $this->db->where_in("id",$model_ids);
    $this->db->get(); 
    $query1 = $this->db->last_query();

    $this->db->select("column2 as column");
    $this->db->distinct();
    $this->db->from("table_name");
    $this->db->where_in("id",$model_ids);

    $this->db->get(); 
    $query2 =  $this->db->last_query();
    $query = $this->db->query($query1." UNION ".$query2);

    return $query->result();
}

Uptake

Bad idea if your data is changing via some asynchronous process, data may get added/deleted/changed in between the two queries. – Newport

Calling last_query() requires you to pre-execute the query instead of merely compiling it. This means that your solution makes 3 trips to the database when only 1 trip is necessary. – Goodwill

Answer 9

bwisn's answer is better than all and will work but not good in performance because it will execute sub queries first. get_compiled_select does not run query; it just compiles it for later run so is faster try this one

$this->db->select('title, content, date');
$this->db->where('condition',value);
$query1= get_compiled_select("table1",FALSE);
$this->db->reset_query();

$this->db->select('title, content, date');
$this->db->where('condition',value);
$query2= get_compiled_select("table2",FALSE);
$this->db->reset_query();

$query = $this->db->query("$query1 UNION $query2");

Riot

Answer 10

I saw that someone asked how to use ORDER BY with UNION via query builder methods.

Just remember to encapsulate your subqueries so that your LIMIT and ORDER BY logic is executed as intended.

public function demoCompiledUnion(int $param1, int $param2): array
{
    $lows = $this->db
        ->select('pk, address')
        ->from('unit1')
        ->where('pk <', $param1)
        ->limit(5)
        ->get_compiled_select();

    $highs = $this->db
        ->select('pk, address')
        ->from('unit2')
        ->where('pk >', $param2)
        ->limit(5)
        ->get_compiled_select();

    return $this->db  // don't need select() if you want every column
        ->from("($lows) UNION ($highs)")
        ->order_by(2, 'DESC')  // order by address DESC
        ->get()
        ->result();  // return payload as an array of objects
}

I printed out $this->db->last_query() and added some tabbing to reveal this rendered SQL (quoting characters may vary based on your db driver/dialect):

SELECT *
FROM (
    SELECT "pk", "address"
    FROM "unit1"
    WHERE "pk" < 10000
    LIMIT 5
) UNION (
    SELECT "pk", "address"
    FROM "stl_unit"
    WHERE "stl_unit_pk" > 15000
    LIMIT 5
)
ORDER BY 2 DESC

The result set was exactly as expected -- 10 rows sorted by address column values in descending order.

Because all of the potentially vulnerable parts of this query build are done with active record methods, it is as secure as CodeIgniter allows.

---

A critical extra note: If you want to append ->where() or ->like() or some other clause to the unionized FROM clause, you'll need to wrap the dual-table expression in outer parentheses and assign a table alias. For example:

return $this->db
    ->from("(($lows) UNION ($highs)) united")
    ->like('address', '1')
    ->order_by(2, 'DESC')
    ->get()
    ->result();

Goodwill

Answer 11

Here's a solution I created:

$query1 = $this->db->get('Example_Table1');
$join1 = $this->db->last_query();
$query2 = $this->db->get('Example_Table2');
$join2 = $this->db->last_query();
$union_query = $this->db->query($join1.' UNION '.$join2.' ORDER BY column1,column2);

Dufresne

This is not really 'server' efficient since you are performing 2 unnecessary queries before the UNION. – Winfrid

I got solution efficient solution & given answer. – Nevarez