Tengo tablas SQL R=recetas, I=ingredientes. (ejemplo mínimo):
R (id)RI (id, r_id, i_id)I (id, description) donde RI es una tabla intermedia que conecta R e I (de lo contrario, en relación m,n ). En HTML tengo una entrada para filtrar recetas por ingredientes. La entrada se envía a PHP con la API Fetch de JavaScript ( body: JSON.stringify({ input: filter.value.trim() }) ).
En PHP, lo limpio y lo exploto en una serie de palabras para que %&/(Oh/$#/?Danny;:¤ boy! se convierta en ['Oh', 'Danny', 'boy']
$filterParams = preg_replace('/[_\W]/', ' ', $data['input']); $filterParams = preg_replace('/\s\s+/', ' ', $filterParams); $filterParams = trim($filterParams); $filterParams = explode(' ', $filterParams);Necesito una consulta SQL para todas las ID de receta que requieran todos los ingredientes de la entrada. Considere estas dos recetas:
ID RECIPE INGREDIENTS 1 pancake egg, flour, milk 2 egg eggFiltrar por "eg, ilk" solo debería devolver 1 pero no 2.
Esto me da todas las recetas que requieren alguno de los ingredientes, por lo tanto, devuelve 1 y 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']}"; Reemplazar OR con AND en la quinta línea no devuelve ni 1 ni 2, porque ningún ingrediente tiene huevos y leche (es decir, pelaje) en su nombre.
... $recipeFilter = implode(' AND ', $recipeFilters); ...Sé que simplemente puedo consultar cada ingrediente por separado y luego, con algunas manipulaciones simples de matrices, obtengo lo que deseo.
¿Es posible hacerlo en una sola consulta, y cómo?
Podría usar una consulta con agrupación en combinación con HAVING COUNT() para obtener el resultado deseado.
receta
| identificación | nombre |
|---|---|
| 1 | panqueques |
| 2 | huevos |
ingrediente
| identificación | nombre |
|---|---|
| 1 | huevos |
| 2 | harina |
| 3 | Leche |
receta_ingrediente
| librar | iid |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
Considere esta consulta:
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; Esta consulta selecciona las recetas, une los ingredientes y agrupa por ID de receta, usando HAVING COUNT(i.name) para contar los ingredientes que coinciden con los filtros OR , lo que le brinda:
| identificación | nombre |
|---|---|
| 1 | panqueques |