php - How to get nested Selects in a where_in clause in CodeIgniter's datamapper? -

i have mysql query i've gotten work in phpmyadmin/mysql workbench.

select * (`medfacts`)     join `categories_medfacts` on `categories_medfacts`.`medfact_id` = `medfacts`.`id`     title in (      select medfacts.title medfacts     join `categories_medfacts` on `categories_medfacts`.`medfact_id` = `medfacts`.`id`      categories_medfacts.category_id = 2     )      or title in (      select medfacts.title medfacts     join `categories_medfacts` on `categories_medfacts`.`medfact_id` = `medfacts`.`id`      categories_medfacts.category_id = 3     )  

(the query gets medfacts listed in join table categories_medfacts given category ids.)

i need transfer codeigniter project in way allows program add many having clauses needed. have far generates query right.

function list_by_category($cat, $module) {     /* list of medfacts entries have category.         *      */     $this->db->join('categories_medfacts', 'categories_medfacts.medfact_id = medfacts.id');     $this->where('medfacts.module_id', $module);     /* if category array, articles fall under categories.      * shouldn't more handful deep.      */     if (is_array($cat)) {         foreach ($cat $field => $value) {             $this->where_in('title', "select medfacts.title medfacts join `categories_medfacts` on `categories_medfacts`.`medfact_id` = `medfacts`.`id` categories_medfacts.category_id = {$value}");         }     } else {         $this->where('categories_medfacts.category_id', $cat);     }     $this->select('medfacts.title');     return $this->get(); } 

however, can't not escape nested select statements, , end this:

select `medfacts`.`title` (`medfacts`) join `categories_medfacts` on `categories_medfacts`.`medfact_id` = `medfacts`.`id` (  `medfacts`.`module_id` = '2' , `medfacts`.`title` in ('select medfacts.title medfacts join `categories_medfacts` on `categories_medfacts`.`medfact_id` = `medfacts`.`id` categories_medfacts.category_id = 2')  , `medfacts`.`title` in ('select medfacts.title medfacts join `categories_medfacts` on `categories_medfacts`.`medfact_id` = `medfacts`.`id` categories_medfacts.category_id = 3')   ) , `medfacts`.`site_id` = 1   

notice quotes around nested select in in clause.

is there way datamapper not escape nested select? or, there better way achieve same goal? or stuck writing query myself?

i'm using ci 1.7.2 , datamapper dmz 1.7.1 (updating out of question) on php5 severs.

edit: aware simple or where return articles match each where case. however, that's not need. need results fall under given categories. current setup, or where returns many results (includes ones fall under 1 of listed categories), , and where doesn't return (nor ever, category_id can't both "2" , "3").

also, found join, such below works in mysql, can't translate ci, either (i didn't post it, because thought where in above cleaner method).

select *     (`medfacts`)     join `categories_medfacts` on `categories_medfacts`.`medfact_id` = `medfacts`.`id`     join (         select medfacts.title, medfacts.id medfacts     join `categories_medfacts` on `categories_medfacts`.`medfact_id` = `medfacts`.`id`         categories_medfacts.category_id = 2     )  m1     on m1.id = medfacts.id      join (         select medfacts.title, medfacts.id medfacts     join `categories_medfacts` on `categories_medfacts`.`medfact_id` = `medfacts`.`id`         categories_medfacts.category_id = 3     ) m2     on m2.id = medfacts.id     `medfacts`.`module_id` = '2'     , medfacts.site_id = 1 

there 1 way found perform subselects in ci - http://heybigname.com/2009/09/18/using-code-igniters-active-record-class-to-create-subqueries/

however, time ago have been warned skilled programmer (my boss :d) subselects quite slow , performing multiple separate selects or trying join instead (where possible) quicker... didn't have opportunity benchmark it, take minor suggestion :)