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->CI =& get_instance();
$this->db = array();
$this->statement = array();
$this->join_type = array();
$this->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->CI->load->database('', true);
$this->db[] = $db;
$this->statement[] = $statement;
if(strtolower($statement) == 'join'){
$this->join_type[] = $join_type;
$this->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->db);
$sql = "({$db->_compile_select()})";
$alias = $alias!='' ? "AS

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->db->select('number')->from('numbers')->where('numberID', 2)->_compile_select();

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

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

        //Get the results
        $result = $this->get("words");
    }
}

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("Cannot use {$method} in InnerQuery");
             break;
        }
        return $this;
    }
    
    public function compile()
    {
        return "(" . $this->_compile_select() . ")";
    }

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

Ok so the above class extends the same object as $this->db in your controller, so you can use all the methods to build a query such as

$this->InnerQuery->select("item as item_key")->from("inner_table")->where("foo","zed");

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->db->select("word")->where('wordID', 3);
$this->db->select($this->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.

StackExchange Code Review Q#12, answer score: 16