CodeIgniter Active Record Subqueries I use CodeIgniter at work, and one of our model files had a lot of subqueries in it.  I originally had to manually write each subquery, and wondered if I could use active records instead.
So, to make my life easier, I made a subquery library for CodeIgniter.
I put it on the CodeIgniter Wiki, but I never really had any one look over it.  So, can you tell me if there is anything I should improve in this, or anything I really shouldn't be doing?
P.S. Feel free to use this if you wish.
P.P.S. join_range is a helper method for use with the answer to this question.
P.P.P.S. The latest version can be found here.
class Subquery{
    var $CI;
    var $db;
    var $statement;
    var $join_type;
    var $join_on;

    function __construct(){
        $this-&gt;CI =&amp; get_instance();
        $this-&gt;db = array();
        $this-&gt;statement = array();
        $this-&gt;join_type = array();
        $this-&gt;join_on = array();
    }

    /**
     * start_subquery - Creates a new database object to be used for the subquery
     *
     * @param $statement - SQL statement to put subquery into (select, from, join, etc.)
     * @param $join_type - JOIN type (only for join statements)
     * @param $join_on - JOIN ON clause (only for join statements)
     *
     * @return A new database object to use for subqueries
     */
    function start_subquery($statement, $join_type='', $join_on=1){
        $db = $this-&gt;CI-&gt;load-&gt;database('', true);
        $this-&gt;db[] = $db;
        $this-&gt;statement[] = $statement;
        if(strtolower($statement) == 'join'){
            $this-&gt;join_type[] = $join_type;
            $this-&gt;join_on[] = $join_on;
        }
        return $db;
    }

    /**
     * end_subquery - Closes the database object and writes the subquery
     *
     * @param $alias - Alias to use in query
     *
     * @return none
     */
    function end_subquery($alias=''){
        $db = array_pop($this-&gt;db);
        $sql = &quot;({$db-&gt;_compile_select()})&quot;;
        $alias = $alias!='' ? &quot;AS $alias&quot; : $alias;
        $statement = array_pop($this-&gt;statement);
        $database = (count($this-&gt;db) == 0)
            ? $this-&gt;CI-&gt;db: $this-&gt;db[count($this-&gt;db)-1];
        if(strtolower($statement) == 'join'){
            $join_type = array_pop($this-&gt;join_type);
            $join_on = array_pop($this-&gt;join_on);
            $database-&gt;$statement(&quot;$sql $alias&quot;, $join_on, $join_type);
        }
        else{
            $database-&gt;$statement(&quot;$sql $alias&quot;);
        }
    }
    
    /**
     * join_range - Helper function to CROSS JOIN a list of numbers
     *
     * @param $start - Range start
     * @param $end - Range end
     * @param $alias - Alias for number list
     * @param $table_name - JOINed tables need an alias(Optional)
     */
    function join_range($start, $end, $alias, $table_name='q'){
        $range = array();
        foreach(range($start, $end) AS $r){
            $range[] = &quot;SELECT $r AS $alias&quot;;
        }
        $range[0] = substr($range[0], 7);
        $range = implode(' UNION ALL ', $range);
        
        $sub = $this-&gt;start_subquery('join', 'inner');
        $sub-&gt;select($range, false);
        $this-&gt;end_subquery($table_name);
    }
}

Example Usage
This query:
SELECT `word`, (SELECT `number` FROM (`numbers`) WHERE `numberID` = 2) AS number
FROM (`words`) WHERE `wordID` = 3 

would become:
$this-&gt;db-&gt;select('word')-&gt;from('words')-&gt;where('wordID', 3);
$sub = $this-&gt;subquery-&gt;start_subquery('select');
$sub-&gt;select('number')-&gt;from('numbers')-&gt;where('numberID', 2);
$this-&gt;subquery-&gt;end_subquery('number');
Personally I think your going the wrong way about things, you can easily pass in a query string into the select method and set the 2nd param to true to bypass backticks.
So the output would place the sub query string within the main query select.
I would do something along the lines of:
class MyModel extends Model
{
    public function getRows()
    {
        //Create a subquery and render it to a stirng
        $sub = $this-&gt;db-&gt;select('number')-&gt;from('numbers')-&gt;where('numberID', 2)-&gt;_compile_select();

        //Clear the data from the CI Arrays
        $this-&gt;db-&gt;_reset_select();

        //Build the main query passing in the sub-query and disabling backticks
        $this-&gt;db-&gt;select(&quot;word,(&quot; . $sub . &quot;)&quot;, false)-&gt;where('wordID', 3);

        //Get the results
        $result = $this-&gt;get(&quot;words&quot;);
    }
}

Sources:

@ _compile_select()
@ _reset_select()

Firstly let me just state that the code above may not be fully working as i have not test machine a.t.m, but I do know that this is possible and you do not need all the extra logic specified.
It seems pretty simple to me without creating new $db's.
I also would recommend you encapsulate the logic above into a class so you can pass the object's around and make life simpler as the above is a POC

Concept:
class InnerQuery extends CI_DB_active_record
{
    public function __construct()
    {   
    }

    public function __call($method,$params = array())
    {
        //Remove methods that modify the database
        switch(strtolower($method))
        {
             case 'get':
             case 'count_all_results':
             case 'get_where':
                 trigger_error(&quot;Cannot use {$method} in InnerQuery&quot;);
             break;
        }
        return $this;
    }
    
    public function compile()
    {
        return &quot;(&quot; . $this-&gt;_compile_select() . &quot;)&quot;;
    }

    public function __tostring()
    {
        return $this-&gt;compile();
    }
}

Ok so the above class extends the same object as $this-&gt;db in your controller, so you can use all the methods to build a query such as
$this-&gt;InnerQuery-&gt;select(&quot;item as item_key&quot;)-&gt;from(&quot;inner_table&quot;)-&gt;where(&quot;foo&quot;,&quot;zed&quot;);

You should disable the parent methods that change the database or run any queries as this is only used to build a select string.
so you should in thoery be able to do:
$this-&gt;db-&gt;select(&quot;word&quot;)-&gt;where('wordID', 3);
$this-&gt;db-&gt;select($this-&gt;InnerQuery,false);

which would use the DB class to build your query and can just be passed into the outer select and the __tostring will return the (SELECT ...) with braces and pass it into the main query.