UNION query with codeigniter's active record pattern

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

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

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.