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 $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');

    @BenV: Why did you change the tag from `codeigniter` to `code-igniter`?

    Why is $db an array?

    @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.

    @Time Machine: `$db` is an array because every time you call `start_subquery` it makes a new database object. This allows subqueries inside subqueries.

    Can you give us an example usage ?

    @RobertPitt: I've added an example to the question.

    If anyone's curious, the latest version of this is here: https://github.com/NTICompass/CodeIgniter-Subqueries

    nice... anyway, can we do a subquery under the subquery? on the other word, can I have unlimited recursively subqueries?

    @zfm: Yes you can! `start_subquery` keeps track of how many times it was called, so when `end_subquery` is called, it knows where to put the subquery. http://pastebin.com/KxfrHb1J

    @Rocket: from what you wrote on pastebin, it was two subqueries on the same level. Is it possible to have something like `$sub2 = $sub->subquery...` so the result will be something like `SELECT * FROM A WHERE xxx IN (SELECT xxx FROM B WHERE yyy IN (SELECT ... ))`

    @zfm: The pastebin example will actually generate: `SELECT word, (SELECT number FROM (numbers) WHERE numberID = 2 AND ab IN (SELECT test FROM (testing) WHERE a = 12)) AS number FROM (words) WHERE wordID = 3` :-D

    @zfm: When you call `end_subquery` it nests it under the last opened `start_subquery`. So the example on pastebin *will* do what you want. :-D

    I got it... thank you very much! such a wonderful library this is :)

    @zfm: You're welcome, and thanks for using it :-)

  • RobertPitt

    RobertPitt Correct answer

    10 years ago

    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.


    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.

    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

    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.

    No problem, sorry for all the confusion above, hope you get a more stable class together :)

    Excellent. very useful and simply great....keep it up and god bless you. Allinonescript.com (http://allinonescript.com) All Over World's Developers Knowledge acquire knowledge.

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM