Query with JOIN on a subquery using CodeIgniter query builder methods

Question

It is possible to generate the following query using CI's query builder methods?

SELECT name 
FROM table1 t1 
JOIN 
     (SELECT ID FROM table2 ORDER BY id LIMIT 5) t2 
     ON t2.id=t1.t2_id 
WHERE t1.id>5

Answer 1

Well there are a couple of ways of doing it. One way is here which is a hack.

How can I rewrite this SQL into CodeIgniter's Active Records?

This other way is very simple.

$this->db
        ->select('ID')
        ->from('table2')
        ->order_by('id')
        ->limit('5');   

$subquery = $this->db->_compile_select();

$this->db->_reset_select(); 

$query  =       $this->db
                    ->select('t1.name')
                    ->from('table1 t1 ')
                    ->join("($subquery)  t2","t2.id = t1.t2_id")
                    ->get('table1 t1');

Some point about it.
You are bound to use from clause in subqueries because get runs the query.
In codeigniter 2 _compile_select and _reset_select can not be accessed because they are protected methods.
You may have to remove the keyword before both methods in system/database/DB_active_rec.php

This article is useful too.