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).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);
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.
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']}";
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);
...
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?
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 |