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)
[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/ |
Welcome Guest, Not a member yet? Register Sign In |