Union Query With Codeigniter'S Active Record Pattern

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

How to write union in codeigniter?

there are two way for UNION first in below

$data['values']=$this->db->query(
'select ID,Name_of_Movie as Name_of_Moviee from moviestable       
 UNION  
select ID,New_Name from moviestable')->get()->result_array();

Second way to UNION in

$this->db->select("ID,Name_of_Movie as Name_of_Moviee");
$this->db->from("moviestable");
$query1 = $this->db->get_compiled_select(); // It resets the query just like a get()

$this->db->select("ID,New_Name");
$this->db->distinct();
$this->db->from("moviestable");
$query2 = $this->db->get_compiled_select(); 

$data['values'] = $this->db->query($query1." UNION ".$query2)->result();

You are implement any one for your query

How to Write an Active record Query for Count and Union

 $this->db->query(
    '(SELECT COUNT(*) as count_site FROM site) 
    UNION 
    (SELECT COUNT(*) as count_userdata FROM userdata)'
 );
 return $query->result();

Incorrect usage of UNION and ORDER BY in CODEIGNITER - Mysql

in model:

function result() {
    $query = $this->db->query("(SELECT * FROM videos where Autor = 'LittleBabyBum ® Español' order by RAND() LIMIT 10) UNION (SELECT * FROM videos WHERE Categoría = 'Música - Niños' AND Autor <>'LittleBabyBum ® Español' order by RAND() lIMIT 5)");
    return $query->result_array();
}

In controller:

$array = $this->MyModel->result();

Codeigniter: Combining activeRecord with manual queries?

maybe this link will help: active record subqueries

Update---

there were another discussion about Union with Codeigniter Active Record. And I agree with the answer there.

But with some 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();

nb: sorry I haven't tested this script, hope it's works and helpfull..

---

Related Topics