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

Question

I have a MySQL query that I've gotten to work in PhpMyAdmin/MySQL Workbench.

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    
)

(The query gets all medfacts that are listed in the join table categories_medfacts with the given category IDs.)

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.

function list_by_category($cat, $module) {
    /* Get a list of the Medfacts entries that have this category.         *
     */
    $this->db->join('categories_medfacts', 'categories_medfacts.medfact_id = medfacts.id');
    $this->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 => $value) {
            $this->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->where('categories_medfacts.category_id', $cat);
    }
    $this->select('medfacts.title');
    return $this->get();
}

However, I can't get it to not escape the nested Select statements, and end up with this:

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

Notice the quotes around the nested select in the where in clause.

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?

I'm using CI 1.7.2 and Datamapper DMZ 1.7.1 (updating is currently out of the question) on PHP5 severs.

Edit: I am aware that a simple or where would return articles that match each where case. However, that's not what I need. I need only results that fall under all given categories. With my current setup, an or where returns too many results (includes ones that only fall under one of the listed categories), and and where doesn't return anything (nor would it ever, as the category_id can't both be "2" and "3").

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 where in above was the cleaner method).

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

WOW! I haven't read the question but you are doing multiple sub-queries AND joins just to match multiple category IDs categories_medfacts.category_id?! I suggest you ask how to optimize that query! — ifaour
Perhaps you should read the question, then? I did ask if there was a better way to achieve the same result. — Shauna
what would this query return: SELECT * FROM medfacts,categories_medfacts WHERE categories_medfacts.medfact_id = medfacts.id AND categories_medfacts.category_id IN (2,3) — ifaour
ifaour: Too many records. I only need records that are categorized under both category 2 and 3, not 2 or 3. Your query gets records matching 2 or 3. — Shauna

Zathrus Writer Zathrus Writer · Accepted Answer · 2011-01-25T18:50:59

there is only one way I found to perform subselects in CI - http://heybigname.com/2009/09/18/using-code-igniters-active-record-class-to-create-subqueries/

however, some time ago I have been warned by a very skilled programmer (my boss actually :D) that subselects are quite slow and performing multiple separate selects or trying to do a join instead (where possible) is much quicker... didn't have an opportunity to benchmark it, so take it just as a minor suggestion :)

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

1 Answers