Active Record Subqueries

[eluser]NTICompass[/eluser]
I couldn't create a wiki page, so I'll just post this here. I made a subquery library for active records. I hope people find this useful.

EDIT: I was able to post to the wiki (http://codeigniter.com/wiki/Subqueries/).

It lets you use active record methods to create subqueries in SQL queries.
It supports SELECT, JOIN, FROM (and other statements, I guess). It also supports subqueries inside subqueries.

Example 1 (SELECT):

Code:

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

Code:

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

Example 2 (FROM):

Code:

SELECT `test`, `test2` FROM ((SELECT 3 AS test) AS testing, (SELECT 4 AS test2) AS testing2)

Code:

$this->db->select('test');
$sub = $this->subquery->start_subquery('from');
$sub->select('3 AS test', false);
$this->subquery->end_subquery('testing');
$this->db->select('test2');
$sub = $this->subquery->start_subquery('from');
$sub->select('4 AS test2', false);
$this->subquery->end_subquery('testing2');

Methods:

start_subquery($statement, $join_type, $join_on) - Opens a subquery, and returns a DB object. Call all active record methods on this object.
Parameters:
$statement - SQL statement to put subquery into (select, from, join, etc.)
$join_type - JOIN type (only for join statements)
$join_on - JOIN ON clause (only for join statements)
Return:
database object

end_subquery($alias) - Closes a subquery.
Parameters:
$alias - Alias to use for subquery