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');
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
$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.
-
\$\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– gen_Eric2011年01月27日 15:05:01 +00:00Commented 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– RobertPitt2011年01月27日 15:08:36 +00:00Commented 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– gen_Eric2011年01月28日 05:09:20 +00:00Commented 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– RobertPitt2011年01月29日 12:03:59 +00:00Commented 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– gen_Eric2011年01月20日 00:28:14 +00:00Commented 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– Hailwood2011年01月20日 09:13:29 +00:00Commented Jan 20, 2011 at 9:13
-
\$\begingroup\$ I posted it on the CodeIgniter wiki. \$\endgroup\$gen_Eric– gen_Eric2011年01月20日 14:28:42 +00:00Commented Jan 20, 2011 at 14:28
$db
is an array because every time you callstart_subquery
it makes a new database object. This allows subqueries inside subqueries. \$\endgroup\$