• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
does Active Record support subqueries?

#1
[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";

#2
[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);

#3
[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");

#4
[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/


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.