UNION query with codeigniter's active record pattern

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

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

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

Solution 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.

Solution 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.