does Active Record support subqueries?

[eluser]Unknown[/eluser]
In the documentation I can only find examples of using Active Record where a basic query is being used. How do you do a subquery? Here's my sql statement:

"SELECT `idobjectives`, `objnum`, `description`, `swot_ref`,
(SELECT name FROM divisions WHERE divisions.divisionid=objectives.divisionid) AS division, `corecomp`, `complete`, `update`, `due`, `responsible`, `parent`, `committee`, `budget` FROM objectives ORDER BY division,objnum";

[eluser]bretticus[/eluser]
[quote author="jso2001" date="1252007532"]In the documentation I can only find examples of using Active Record where a basic query is being used. How do you do a subquery? Here's my sql statement:

"SELECT `idobjectives`, `objnum`, `description`, `swot_ref`,
, `corecomp`, `complete`, `update`, `due`, `responsible`, `parent`, `committee`, `budget` FROM objectives ORDER BY division,objnum";[/quote]

Use the optional 2nd parameter and set to FALSE. This will instruct AR not to try to protect the query with backticks, etc.

Code:

$this->db->select('(SELECT name FROM divisions WHERE divisions.divisionid=objectives.divisionid) AS division', FALSE);

[eluser]Chad Fulton[/eluser]
In this particular case, I suggest you use a join rather than a subquery, as joins are faster:

Code:

$this->db->select(objectives.*, divisions.name)
         ->join("divisions", "divisions.divisionid = objectives.divisionid")
         ->order_by("divisions.name", "objectives.objnum")
         ->get("objectives");

[eluser]ShawnM[/eluser]
I have, what I consider to be, a mostly solid subquery solution utilizing the _compile_select() method of the active record database drivers.

http://shawnmccool.com/2009/09/18/using-...ubqueries/