Codeigniter: Combining activeRecord with manual queries?

Question

I talk a little about activation and manual queries in Codeigniter. ActiveRecord is awesome when it comes to standard queries and takes very little development time.

However, when there is a need to add some complexity to queries, ActiveRecord gets complicated to work with. Sub-queries or complex joins give me more headaches.

Since the current query "$ this-> db-> query" immediately executes the given query, it cannot be combined with regular activeRecord calls.

So what can I do to combine the two methods?

An example of what I want to accomplish:

$this->db->select('title, content, date'); $this->db->from('mytable'); $this->db->manual('UNION'); // My own idea of db-call that appends UNION to the query $this->db->select('title, content, date'); $this->db->from('mytable2'); $query = $this->db->get(); 

Thanks!

Answer 1

perhaps this link will help: active post subqueries

Update ---

another discussion about the Union with the active Codeigniter record appeared . And I agree with the answer there.

But with some subqueries, we can combine the 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 have not tested this script, hope it works and is useful.