UNION query with active codeigniter entry template

Question

How to execute a UNION query with an active record format in PHP CodeIgniter format?

Answer 1

CodeIgniter ActiveRecord does not support UNION, so you just write your request and use the ActiveRecord request method.

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

Answer 2

This is a quick and dirty method that 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 best job, but it solved my problem.

Note: I did not need to order the result.

Answer 3

Performing a join using last_query () can interfere with the application. Since for a single union, you will need to complete 3 queries. those. for "n" joining "n + 1" queries. This will not affect 1-2 requests. But this will give a problem if combining many queries or tables with big data.

This link will help you a lot: active post subqueries

We can combine an 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(); 

Answer 4

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

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

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

Answer 5

by changing the somnath huluks answer, I add the following variables and functions to the 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 practically use unions without dependencies on db_driver.

to use a connection with this method, you simply make regular active write requests, but call union_push instead of get.

Note: you must ensure that your queries have the appropriate columns, for example, 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(); 

will create:

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

Answer 6

I found this library that worked great for me to add an ActiveRecord-style UNION:

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

BUT I had to first get the get_compiled_select() method from the dev branch of CodeIgniter (available here: https://github.com/EllisLab/CodeIgniter/blob/develop/system/database/DB_query_builder.php - DB_query_builder will replace DB_active_rec). This method is expected to be available in a future production version of CodeIgniter.

As soon as I added this method to DB_active_rec.php in the system / database, it worked like a charm. (I did not want to use the dev version of CodeIgniter, as this is a production application.)

Answer 7

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

Answer 8

This is the 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); 

Answer 9

Here is the 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);