Skip to content
This repository

Add compile_and_query and compile_sql function to CI_DB_query_builder #2093

Closed
ragboyjr opened this Issue · 5 comments

2 participants

ragboyjr Andrey Andreev
ragboyjr

LAST EDITED: 12-12-23 09:54:00
Below are two function that I think would be a great benefit to CI 3.0's query builder class. I've modified CI 2.0 + to use these functions, and it has worked great for me, and I think it should standard in 3.0.

Usage:
In my models and their functions, I've found that I usually only write one complex select/join statement per function. I almost never need to dynamically add select columns or add join clauses on the fly. However, when calling my model functions to execute such queries, I'd need to have different order by clauses, different where clauses, different limit clauses, and so on.

So I started to build my model functions to take in parameters that would dynamically add orderby clauses , where clauses, etc..., but then I realized that I'm reinventing the wheel because CI's querybuilder class already compiles sql queries like that. So I created to extra functions that fixed my problem.

Code Example

// Model code
class A_model extends CI_Model
{
    public function get_stuff()
    {
        $sql = <<<sql_query
A lot of sql code
goes here
sql_query;

        return $this->db->compile_and_query($sql);
    }
}

// Controller Code
$this->load->model('A_model', 'm');
$this->db->where('col', 4)->orderby('other_col', 'DESC')->limit(10);
$res = $this-m->get_stuff();

// this code can be even sexier if you add this function to the A_model
    /**
     * 
     * @param unknown_type $method
     * @param unknown_type $args
     * @return mixed
     */
    public function __call($method, $args)
    {
        $ret_val = call_user_func_array(array($this->db, $method), $args);

        // if db returned itself for method chaining then return $this also
        // else just return the result
        if ($ret_val == $this->db)
        {
            return $this;
        }
        else
        {
            return $ret_val;
        }
    }

// Back to controller code, but with sexy syntax
$this->load->model('A_model', 'm');
$res = $this->m->where('col', 4)->orderby('other_col', 'DESC')->limit(10)->get_stuff();

Below are the two functions I added at line 1301 in CI_DB_query_builder.php to make the above code example work. I tried to make this code match the coding style of CI the best I can

    /**
     * Compile_and_Query
     *
     * Takes a fully functional (if queried by itself, it would work) sql string. Sql string
     * must not have any where, orderby, having, or limit parts. It can contian join fields
     * Sql string will be compiled with where, order_by, having, limit, groupby, or any other Query Builder
     * function that doesn't affect that actual "getting' of the sql statement. By "getting" I mean the data
     * selecting part of the statement which includes the select, from, table_name, and join portions.
     *
     * e.g. with mysql
     *
     * // valid sql to be passed to this function
     * $sql = <<<sql_str
     * SELECT foo, bar, SOME_SQL_FUNC(stuff, yo) #and any more complex sql select
     * FROM main_table AS mt
     * LEFT JOIN other_table USING (column)
     * #many more really complicated join clauses and whatnot
     * sql_str;
     *
     * $res = $this->db->where('col', 3)
     *      ->where('o_col', 4)
     *      ->order_by('some_col', 'DESC')
     *      ->limit(10)
     *      ->compile_and_query($sql);
     *
     * Generally, you only write complicated sql code once. It's rare when you need to change
     * the "getting" portion of the query, but you are constantly needing to change where,
     * orderby, limit, having, etc... clauses. This function allows you to do that.
     *
     * @param   string
     * @return  object
     */

    public function compile_and_query($sql)
    {
        $result = $this->query($this->compile_sql($sql));
        $this->_reset_select();
        return $result;
    }

    // --------------------------------------------------------------------

    /**
     * Compile_Sql
     *
     * Does the same exact thing as compile_and_query, except it doens't execute the sql;
     * it just returns the compiled sql string. This function is NOT the same as get_compiled_select.
     * get_compiled_select requires you to use $this->db->select(), $this->db->from, etc..., which
     * defeats the purpose of using compile_sql. You don't want to run a humungous and complicated
     * select statement through $this->db->select because you'll have to make quite a few func calls
     * to $this->db->select(), $this->db->from(), $this->db->join(), and set the table. There would
     * be a lot of overhead.
     *
     * @param   string
     * @return  string
     */

    public function compile_sql($sql)
    {
        $sql .= $this->_compile_wh('qb_where')
            .$this->_compile_group_by()
            .$this->_compile_wh('qb_having')
            .$this->_compile_order_by(); // ORDER BY

        // LIMIT
        if ($this->qb_limit)
        {
            return $this->_limit($sql."\n");
        }

        return $sql;
    }
ragboyjr

On a mildly unrelated note, I'd like to start doing some development for CI 3.0, but I don't quite know where to get started. I've started working on the "Parser becomes a driver" ticket by phil sturgeon, but I don't what to do with it (I don't quite know how to share with the rest of ya'll). Sorry for my ignorance, I'm obviously new to github, so if what I'm asking is basic github stuff, then please just point me to the help page. Thanks a Ton!

RJ

Andrey Andreev
Collaborator

$this->db->get()

ragboyjr

Hmmm, I don't quite understand what your comment means. Could you please offer some clarification to why you closed this ticket?

Andrey Andreev
Collaborator

Your first method doesn't use the SQL that's passed to it and is the same as the get() method - compiles the parameters passed to Query Builder and executes them.
The second is pointless as you can just use select() for the passed SQL and limit() without an actual limit does nothing. You can use get_compiled_select() for the rest.

ragboyjr

LOL!! So sorry bro, typo!! I'll re edit the ticket haha, yeah this pointless. Compile_and_query is supposed to call compile sql.
Now compile_sql is not the same as get compiled select because you need to add select, from, and table names through qb that defeats the purpose compile_and_query. Compile SQL just returns the string that compile_and_query executes. I made it public because figured people may want to do something with their SQL string. I'll edit the code and make some more comments. So sorry for wasting your time. Thanks so much though!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.