+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: does Active Record support subqueries? (/showthread.php?tid=22279)
does Active Record support subqueries? - El Forum - 09-03-2009
[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";
does Active Record support subqueries? - El Forum - 09-03-2009
[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);does Active Record support subqueries? - El Forum - 09-03-2009
[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");does Active Record support subqueries? - El Forum - 10-02-2009
[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-code-igniters-active-record-class-to-create-subqueries/