title: How to get nested Selects in a where_in clause in CodeIgniter's datamapper?
body: <p>I have a MySQL query that I've gotten to work in PhpMyAdmin/MySQL Workbench. </p>
  
  <pre><code>SELECT *
  FROM (`medfacts`)    
  JOIN `categories_medfacts` ON `categories_medfacts`.`medfact_id` = `medfacts`.`id`    
  where title in (    
   select medfacts.title from medfacts    
  JOIN `categories_medfacts` ON `categories_medfacts`.`medfact_id` = `medfacts`.`id`    
   where categories_medfacts.category_id = 2    
  )     
  or title in (    
   select medfacts.title from medfacts    
  JOIN `categories_medfacts` ON `categories_medfacts`.`medfact_id` = `medfacts`.`id`    
   where categories_medfacts.category_id = 3    
  ) 
  </code></pre>
  
  <p>(The query gets all medfacts that are listed in the join table categories_medfacts with the given category IDs.)</p>
  
  <p>I now need to transfer it to my CodeIgniter project in a way that allows my program to add as many Having clauses as needed. What I have so far generates the query mostly right.</p>
  
  <pre><code>function list_by_category($cat, $module) {
      /* Get a list of the Medfacts entries that have this category.         *
       */
      $this-&gt;db-&gt;join('categories_medfacts', 'categories_medfacts.medfact_id = medfacts.id');
      $this-&gt;where('medfacts.module_id', $module);
      /* If the category is an array, just get the articles that fall under all categories.
       * This shouldn't be more than a handful deep.
       */
      if (is_array($cat)) {
          foreach ($cat as $field =&gt; $value) {
              $this-&gt;where_in('title', "select medfacts.title from medfacts JOIN `categories_medfacts` ON `categories_medfacts`.`medfact_id` = `medfacts`.`id` where categories_medfacts.category_id = {$value}");
          }
      } else {
          $this-&gt;where('categories_medfacts.category_id', $cat);
      }
      $this-&gt;select('medfacts.title');
      return $this-&gt;get();
  }
  </code></pre>
  
  <p>However, I can't get it to not escape the nested Select statements, and end up with this:</p>
  
  <pre><code>SELECT `medfacts`.`title`
  FROM (`medfacts`)
  JOIN `categories_medfacts` ON `categories_medfacts`.`medfact_id` = `medfacts`.`id`
  WHERE ( 
  `medfacts`.`module_id` = '2'
  AND `medfacts`.`title` IN ('select medfacts.title from medfacts JOIN `categories_medfacts` ON `categories_medfacts`.`medfact_id` = `medfacts`.`id` where categories_medfacts.category_id = 2') 
  AND `medfacts`.`title` IN ('select medfacts.title from medfacts JOIN `categories_medfacts` ON `categories_medfacts`.`medfact_id` = `medfacts`.`id` where categories_medfacts.category_id = 3') 
   )
  AND `medfacts`.`site_id` = 1  
  </code></pre>
  
  <p>Notice the quotes around the nested select in the where in clause.</p>
  
  <p>Is there a way to get Datamapper to not escape that nested select? Or, is there a better way to achieve the same goal? Or am I stuck writing the query myself?</p>
  
  <p>I'm using CI 1.7.2 and Datamapper DMZ 1.7.1 (updating is currently out of the question) on PHP5 severs.</p>
  
  <p><strong>Edit:</strong> I am aware that a simple <code>or where</code> would return articles that match each <code>where</code> case. However, that's not what I need. I need only results that fall under all given categories. With my current setup, an <code>or where</code> returns too many results (includes ones that only fall under one of the listed categories), and <code>and where</code> doesn't return anything (nor would it ever, as the category_id can't both be "2" and "3").</p>
  
  <p>Also, I found a JOIN, such as below also works in MySQL, but I can't translate it into CI, either (I didn't post it, because I thought the <code>where in</code> above was the cleaner method).</p>
  
  <pre><code>SELECT *    
  FROM (`medfacts`)    
  JOIN `categories_medfacts` ON `categories_medfacts`.`medfact_id` = `medfacts`.`id`    
  join (    
      select medfacts.title, medfacts.id from medfacts    
  JOIN `categories_medfacts` ON `categories_medfacts`.`medfact_id` = `medfacts`.`id`    
      where categories_medfacts.category_id = 2    
  )  m1    
  on m1.id = medfacts.id    
   join (    
      select medfacts.title, medfacts.id from medfacts    
  JOIN `categories_medfacts` ON `categories_medfacts`.`medfact_id` = `medfacts`.`id`    
      where categories_medfacts.category_id = 3    
  ) m2    
  on m2.id = medfacts.id    
  where `medfacts`.`module_id` = '2'    
  and medfacts.site_id = 1
  </code></pre>