28
\$\begingroup\$

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);
        }
        else{
            $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);
        $this->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->db->select('word')->from('words')->where('wordID', 3);
$sub = $this->subquery->start_subquery('select');
$sub->select('number')->from('numbers')->where('numberID', 2);
$this->subquery->end_subquery('number'); 
\$\endgroup\$
  • \$\begingroup\$ @BenV: Why did you change the tag from codeigniter to code-igniter? \$\endgroup\$ – Rocket Hazmat Jan 19 '11 at 21:46
  • 1
    \$\begingroup\$ Why is $db an array? \$\endgroup\$ – rightfold Jan 19 '11 at 21:50
  • \$\begingroup\$ @Rocket: I was thinking the proper name included a space which would make a hyphen appropriate, but I just checked and it's all one word which makes your tag correct. Feel free to roll back. \$\endgroup\$ – BenV Jan 19 '11 at 21:51
  • \$\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\$ – Rocket Hazmat Jan 19 '11 at 21:55
  • \$\begingroup\$ Can you give us an example usage ? \$\endgroup\$ – RobertPitt Jan 20 '11 at 11:45
15
\$\begingroup\$

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:

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.

\$\endgroup\$
  • \$\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\$ – Rocket Hazmat Jan 27 '11 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 '11 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\$ – Rocket Hazmat Jan 28 '11 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 '11 at 12:03
  • \$\begingroup\$ Excellent. very useful and simply great....keep it up and god bless you. Allinonescript.com (allinonescript.com) All Over World's Developers Knowledge acquire knowledge. \$\endgroup\$ – Vadivel S Apr 19 '18 at 10:40
4
\$\begingroup\$

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?

\$\endgroup\$
  • \$\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\$ – Rocket Hazmat Jan 20 '11 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 '11 at 9:13
  • \$\begingroup\$ I posted it on the CodeIgniter wiki. \$\endgroup\$ – Rocket Hazmat Jan 20 '11 at 14:28
3
\$\begingroup\$

use this

$this->db->where('`store_id` NOT IN (SELECT `store_id` FROM `user_store`)', NULL, FALSE);

Check this

\$\endgroup\$
  • 1
    \$\begingroup\$ Ummmm thanks, but I already know that. This question is from Jan 2011, I actually added support for WHERE into my code in July 2011. Check the newest version of my code here: github.com/NTICompass/CodeIgniter-Subqueries \$\endgroup\$ – Rocket Hazmat Apr 11 '12 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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