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 $alias" : $alias;
        $statement = array_pop($this->statement);
        $database = (count($this->db) == 0)
            ? $this->CI->db: $this->db[count($this->db)-1];
        if(strtolower($statement) == 'join'){
            $join_type = array_pop($this->join_type);
            $join_on = array_pop($this->join_on);
            $database->$statement("$sql $alias", $join_on, $join_type);
            $database->$statement("$sql $alias");
     * 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[] = "SELECT $r AS $alias";
        $range[0] = substr($range[0], 7);
        $range = implode(' UNION ALL ', $range);
        $sub = $this->start_subquery('join', 'inner');
        $sub->select($range, false);

Example Usage

This query:

SELECT `word`, (SELECT `number` FROM (`numbers`) WHERE `numberID` = 2) AS number
FROM (`words`) WHERE `wordID` = 3 

would become:

$this->db->select('word')->from('words')->where('wordID', 3);
$sub = $this->subquery->start_subquery('select');
$sub->select('number')->from('numbers')->where('numberID', 2);
  • 1
    \$\begingroup\$ Why is $db an array? \$\endgroup\$
    – user36
    Jan 19, 2011 at 21:50
  • \$\begingroup\$ @Time Machine: $db is an array because every time you call start_subquery it makes a new database object. This allows subqueries inside subqueries. \$\endgroup\$
    – gen_Eric
    Jan 19, 2011 at 21:55
  • \$\begingroup\$ Can you give us an example usage ? \$\endgroup\$
    – RobertPitt
    Jan 20, 2011 at 11:45
  • \$\begingroup\$ @RobertPitt: I've added an example to the question. \$\endgroup\$
    – gen_Eric
    Jan 20, 2011 at 14:29
  • \$\begingroup\$ If anyone's curious, the latest version of this is here: github.com/NTICompass/CodeIgniter-Subqueries \$\endgroup\$
    – gen_Eric
    Oct 7, 2011 at 16:57

2 Answers 2


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

        //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");


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


class InnerQuery extends CI_DB_active_record
    public function __construct()

    public function __call($method,$params = array())
        //Remove methods that modify the database
             case 'get':
             case 'count_all_results':
             case 'get_where':
                 trigger_error("Cannot use {$method} in InnerQuery");
        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);

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.

  • \$\begingroup\$ I'd rather not have to call _compile_select() and _reset_select() on the main DB object. That would mean I'd have to declare all subqueries before the rest of the query, and I don't want to have to do that. Also, the point of this library is to abstract this. \$\endgroup\$
    – gen_Eric
    Jan 27, 2011 at 15:05
  • \$\begingroup\$ But I also stated that I aso would recommend you encapsulate the logic above into a class so you can pass the object's around and make life simpler. this would resolve that issue \$\endgroup\$
    – RobertPitt
    Jan 27, 2011 at 15:08
  • \$\begingroup\$ I originally tried to extend the active record class, but that failed. I think I was doing it wrong. I really like your method, I'll probably do that when I get the time to update my library. \$\endgroup\$
    – gen_Eric
    Jan 28, 2011 at 5:09
  • 1
    \$\begingroup\$ No problem, sorry for all the confusion above, hope you get a more stable class together :) \$\endgroup\$
    – RobertPitt
    Jan 29, 2011 at 12:03

I may be missing something here, But to me it seems that you have a class that you pass a pre-built query into?

I am thinking would it not be beneficial to have a subquery built the same way as the top level queries?

  • \$\begingroup\$ You're not passing in a pre-built query per se. start_subquery returns you (a reference to) CodeIgniter's database object. You can then call active query methods on that object. end_subquery gets the query from the db object, wraps it in () then adds it to the main query. \$\endgroup\$
    – gen_Eric
    Jan 20, 2011 at 0:28
  • 1
    \$\begingroup\$ I see, I like this approach, Much better than Writing them yourself. reading through it properly now I have time, Im not sure there is much different you could do. A+ code, Have you considered putting the code up as an enhancement in the CI issue tracker? \$\endgroup\$
    – Hailwood
    Jan 20, 2011 at 9:13
  • \$\begingroup\$ I posted it on the CodeIgniter wiki. \$\endgroup\$
    – gen_Eric
    Jan 20, 2011 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.