There is a recipe table (rec), a table of ingredients (ing), and a link table (rec_ing). The rec_ing table has the form:

id | rec_id | ing_id

Accordingly, the recipe can be associated with several ingredients, and each ingredient can be used in different recipes.

Question: Having a list of ingredients you need to get a list of recipes that can be prepared from these ingredients.

For example: A juice recipe can contain 2 ingredients - an apple and a pear, and a bread recipe - flour and milk. If we have 3 ingredients (apple, pear, flour) and unload recipes for these ingredients, we should get only a juice recipe, since for bread we do not have enough flour ingredient.

How to implement it in Yii2?

    2 answers 2

    This problem can be solved in many ways.
    Here is one of the options:

     $ing_names = ['яблоко', 'груша', 'мука']; $ings = Ing::find()->where(['name' => $ing_name])->indexBy('id')->all(); $ing_ids = array_keys($ings); $try_rec_ids = RecIng::find()->select('rec_id')->where(['ing_id' => $ing_ids])->column(); $try_recs = Rec::find()->where(['id' => $try_rec_ids])->with('rec_ings')->all(); $recs = []; foreach ($try_recs as $try_rec) { $ok = true; foreach ($try_rec->rec_ings as $rec_ing) { if (!in_array($rec_ing->ing_id, $ing_ids)) { $ok = false; break; } } if ($ok) { $recs[] = $try_rec; } }
    • The solution is working. I just think that all this should happen in the model. - coolrus

    To do this, you need to create functions similar to the documentation :

     class Data extends ActiveRecord { public $id; public $rec_id; public $ing_id; } class Ingredient extends ActiveRecord { public $id; public function getRecipes() { $entries = $this->hasMany(Data::className(), ['id' => 'ing_id']); $recipes = array(); foreach($entries as $entry) $recipes[] = Recipe::find()->where('id' => $entry); return $recipes; } } class Recipe extends ActiveRecord { public $id; public function getIngredients() { $entries = $this->hasMany(Data::className(), ['id' => 'rec_id']); $ingredients = array(); foreach($entries as $entry) $ingredients[] = Ingredient::find()->where('id' => $entry); return $ingredients; } } $recipe = Recipe:find()->where(['name' => 'Борщ'])->all(); $ingredients = $recipe->ingredients; // массив ингредиентов для борща $ingredient = Ingredient::find()->where(['name' => 'Лук'])->all(); $recipesWith = $ingredient->recipes; // массив рецептов, в которых есть лук
    • This is not exactly what you need. In this case, all recipes containing the specified ingredients are displayed. And you need to get the following: - coolrus
    • Having a list of ingredients, download only those recipes that use only this set of ingredients. For example, for eggs you need eggs and salt. If we have only eggs from ingredients, then we cannot cook scrambled eggs accordingly. So this recipe does not suit us. - coolrus
    • In this case, you need to add the following: $ myIngredients = array (Ingredient :: find () -> where ('name' => 'Egg') -> id, Ingredient :: find () -> where ('name' = > 'Salt') -> id); $ myRecipes = Recipe :: find () -> where (['and', ['ing_id' => $ myIngredients]]); - Daniel Protopopov