Subqueries

This is a subquery library for CodeIgniter's active record class. 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.

Try it out:[url=http://codeigniter.com/wiki/908d7de0b0dc02a12198044170d40ec8/]Subquery.zip[/url]

Example 1 (SELECT): [code] SELECT word, (SELECT number FROM (numbers) WHERE numberID = 2) AS number FROM (words) WHERE wordID = 3 [/code]

[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'); [/code]

Example 2 (FROM): [code] SELECT test, test2 FROM ((SELECT 3 AS test) AS testing, (SELECT 4 AS test2) AS testing2) [/code]

[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'); [/code]

Methods: [quote]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[/quote]