How to query table results with m,n-relationships to a different table with (unnecessarily) LEFT JOINs?

Question

Please advise on a better formulation of the question.

Basis

I have SQL tables R=recipes, I=ingredients. (minimum example):

• R (id)
• RI (id, r_id, i_id)
• I (id, description) where RI is intermediate table connecting R and I (otherwise in m,n-relationship).

You may skip this

In HTML I have an input for filtering recipes by ingredients. Input is sent to PHP with JavaScript's Fetch API (body: JSON.stringify({ input: filter.value.trim() })).

In PHP I clean it up and explode it into array of words so %&/(Oh/$#/?Danny;:¤ boy! gets converted to ['Oh', 'Danny', 'boy']

$filterParams = preg_replace('/[_\W]/', ' ', $data['input']);
$filterParams = preg_replace('/\s\s+/', ' ', $filterParams);
$filterParams = trim($filterParams);
$filterParams = explode(' ', $filterParams);

What I want

I need an SQL query for all recipe IDs that require all of the ingredients from the input. Consider these two recipes:

ID   RECIPE    INGREDIENTS
 1   pancake   egg, flour, milk
 2   egg       egg

Filtering for "eg, ilk" should only return 1 but not 2.

What I have #1

This gives me all recipes that require any of the ingredients, therefore it returns 1 and 2.

$recipeFilters = array_map(function ($param) {
    return "ri.description LIKE '%{$param}%'";
}, $filterParams);

$recipeFilter = implode(' OR ', $recipeFilters);

$selRecipes = <<<SQL

    SELECT DISTINCT rr.id
    FROM
        recipe_ingredient ri LEFT JOIN
        recipe_intermediate_ingredient_recipe riir ON riir.ingredient_id = ri.id LEFT JOIN
        recipe_recipe rr ON rr.id = riir.recipe_id
    WHERE
        {$recipeFilter} AND
        rr.id IS NOT NULL

SQL;

$recipes = data_select($selRecipes); // Custom function that prepares statement, binds data (not in this case), and eventually after all error checking returns $statement->get_result()->fetch_all(MYSQLI_ASSOC)

$ids = [];
foreach ($recipes as $recipe)
    $ids[] = "{$recipe['id']}";

What I have #2

Replacing OR with AND in the fifth line returns neither 1 nor 2, because no ingredient has both eggs and milk (ie. eg, ilk) in it's name.

...
$recipeFilter = implode(' AND ', $recipeFilters);
...

Suboptimal solution

I know I can simply query for each ingredient separately and then with some simple array manipulations get what I desire.

Is it possible to do it in just one query, and how?

Answer 1

You could use a query with grouping in combination with HAVING COUNT() to get the desired result.

recipe

id	name
1	pancakes
2	eggs

---

ingredient

id	name
1	eggs
2	flour
3	milk

---

recipe_ingredient

rid	iid
1	1
1	2
1	3
2	1

---

Consider this query:

SELECT r.id, r.name FROM recipe r
  JOIN recipe_ingredient ri ON r.id = ri.rid
  JOIN ingredient i ON i.id = ri.iid
WHERE i.name LIKE '%ilk%' OR i.name LIKE '%eg%'
GROUP BY r.id
HAVING COUNT(i.name) = 2;

This query selects the recipes, joins the ingredients, and groups by recipe ID, using HAVING COUNT(i.name) to count the ingredients matching the OR filters, which gives you:

id	name
1	pancakes