Codeigniter - Joining derived tables

Question

I know how to join 2 basic tables. What I'm interested in knowing is how do I join the derived tables in Codeigniter like we do in mysql by giving aliases to the tables.

Example:

Table1->where(something)->group_by(something) as derived_table1

Table2->where(something AND something)->group_by(something) as derived_table2

How do I left join these 2 derieved tables?

Please note: I know I can left join first and then apply the where clauses. But that changes the query result. I need to left join the derived tables.

Answer 1

i'm not sure that this is the canonical approach, but this is example how i do it:

// Build subquery
$this->db->select('max(a) a', false);
$this->db->group_by('b');
// Save subquery
$subQuery =  $this->db->get_compiled_select('table-1');

// Main query
$this->db->select('t.a', false);
$this->db->from('table-2'); 
// Here insert subquery
$this->db->join("($subQuery) t", 'condition', 'left');
$query = $this->db->get();