MySQL many-to-many relationship: Help With Query

Question

My current code is this:

$select = $this->select()
               ->from(array('r' => 'recipes'), $this->getAdapter()
                                                    ->quoteInto("r.*, MATCH(title, directions) AGAINST(?) AS score", $searchText))
               ->where('MATCH(title, directions) AGAINST(?)', $searchText);

//SQL: SELECT r.*, MATCH(title, directions) AGAINST('chicken soup') AS `score` FROM `recipes` AS `r` WHERE (MATCH(title, directions) AGAINST('chicken soup')) 

I want to add an additional WHERE clause for finding only recipes that contain a certain ingredient. The problem I am having comes from the fact that the ingredients table and my recipe table have a many-to-many relationship, with a connecting table ingredient_recipes that has the columns 'id', 'recipe_id', and 'ingredient_id'.

How do I do this?

Answer 1

Here's the plain SQL to search recipes by ingredient. You shold be able to extrapolate the specifics from there.

SELECT r.*
FROM recipes r
LEFT OUTER JOIN recipe_ingredients ri ON r.recipe_id = re.recipe_id
LEFT OUTER JOIN ingredients i on ri.ingredient_id = i.ingredient_id
WHERE i.ingredient_title = "[Your Ingredient Variable]"
GROUP BY r.recipe_id;

For multiple Ingredients:

SELECT r.*
FROM recipes r
LEFT OUTER JOIN recipe_ingredients ri ON r.recipe_id = re.recipe_id
LEFT OUTER JOIN ingredients i on ri.ingredient_id = i.ingredient_id
WHERE i.ingredient_title = "[First Ingredient Var]"
OR i.ingredient_title = "[Second Ingredient Var]"
GROUP BY r.recipe_id;

Although if you're trying to find something that has both (not just one or the other), you may need to do two queries (or at least a sub-select), which can be a pretty slow query on a large database.

The best I can think of for a single query would be something like this:

SELECT r.*
FROM recipes r
LEFT OUTER JOIN recipe_ingredients ri ON r.recipe_id = re.recipe_id
LEFT OUTER JOIN ingredients i1 on ri.ingredient_id = i1.ingredient_id
LEFT OUTER JOIN ingredients i2 on ri.ingredient_id = i2.ingredient_id
WHERE i1.ingredient_title = "[First Ingredient Var]"
AND i2.ingredient_title = "[Second Ingredient Var]"
GROUP BY r.recipe_id;

Where you're creating another join for each additional ingredient. This query would not return recipes that had some - but not all - the ingredients, only recipes that have ALL the ingredients you're looking for.

Answer 2

I'm not very familiar with MySQL, but could something similar to this be done?

SELECT r.* 
FROM recipes AS r 
LEFT JOIN recipe_sites AS s ON r.site_id = s.id 
WHERE '%salt%' IN (SELECT i.name
                   FROM ingredients AS i 
                   LEFT JOIN recipe_ingredients AS ri ON i.id = ri.ingredient_id 
                   LEFT JOIN recipes AS r ON ri.recipe_id = r.id) 
GROUP BY r.id;

What would be the correct way to do this?