Codeigniter: Combining activeRecord with manual queries?

Question

Hi everybody,

I've though a bit about the activerecord vs. manual queries in Codeigniter. ActiveRecord is awesome when it's all about standard queries and holds development time really low.

However, when there's a need to add some complexity to the queries, the ActiveRecord gets quite complicated to work with. Sub queries or complex joins gives atleast me a lot of headache.

Since the current "$this->db->query" -call immediately executes the set query, it can't be combined with normal activeRecord calls.

So, what can I do to combine the two methods?

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

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