How to correctly compose a query in the yii2 search model?

Question

MySQL

Good morning.
There is such a task:

The user can select up to 5 ingredients for preparing a dish, while
:
1. If dishes are found with a complete match of ingredients, display
only them.
2. If dishes with a partial match of ingredients are found, display
in order of decreasing the match of ingredients up to 2x.
3. If dishes are found with a match of less than 2 ingredients or not
found at all, display “Nothing found”.
4. If less than 2 ingredients are selected, do not search, display
a message: “Please select more ingredients”.

Created three tables:
1) Recipes // recipe table
2) Ingredients // ingredient table
3) RecipesIngredients // link table
Here are the table migrations:

Migrations

class m170818_200201_ingredient_table extends Migration
{
    public function safeUp()
    {
        $tableOptions = null;

        if($this->db->driverName == 'mysql'){
            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB';
        }

        $this->createTable('{{%ingredients}}',[
            'id' => $this->primaryKey(),
            'name' => $this->string()->notNull(),
            'created_at' => $this->integer()->notNull(),
            'updated_at' => $this->integer()->notNull(),
            'status' => $this->smallInteger()->notNull()->defaultValue(0)
        ], $tableOptions);

        $this->createIndex('idx-ingred-id', '{{%ingredients}}', 'id');
        $this->createIndex('idx-ingred-name', '{{%ingredients}}', 'name');
        $this->createIndex('idx-ingred-status', '{{%ingredients}}', 'status');


    }

    public function safeDown()
    {
        $this->dropTable('{{%ingredients}}');
    }
}

class m170818_200136_recipes_table extends Migration
{
    public function safeUp()
    {
        $tableOptions = null;
        if($this->db->driverName == 'mysql'){
            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB';
        }

        $this->createTable('{{%recipes}}',[
            'id' => $this->primaryKey(),
            'name' => $this->string(100),
            'created_at' => $this->integer()->notNull(),
            'updated_at' => $this->integer()->notNull(),
            'status' => $this->smallInteger()->notNull()->defaultValue(0)
        ], $tableOptions);

        $this->createIndex('idx-recipe-id', '{{%recipes}}', 'id');
        $this->createIndex('idx-recipe-name', '{{%recipes}}', 'name');
        $this->createIndex('idx-recipe-status', '{{%recipes}}', 'status');
    }

    public function safeDown()
    {
        $this->dropTable('{{%recipes}}');
    }
}

class m170819_172758_recipes_ingredients_table extends Migration
{
    public function safeUp()
    {
        $this->createTable('{{%recipes_ingredients}}', [
            'recipe_id' => $this->integer()->notNull(),
            'ingredient_id' => $this->integer()->notNull()
        ]);

       $this->addPrimaryKey('pk-recipes-ingredients', '{{%recipes_ingredients}}', ['recipe_id', 'ingredient_id']);

        $this->createIndex('idx-recipe', '{{%recipes_ingredients}}', 'recipe_id');
        $this->createIndex('idx-inged', '{{%recipes_ingredients}}', 'ingredient_id');

        $this->addForeignKey('fk-recipe_id', '{{%recipes_ingredients}}', 'recipe_id', '{{%recipes}}', 'id', 'CASCADE', 'RESTRICT');
        $this->addForeignKey('fk-ingred_id', '{{%recipes_ingredients}}', 'ingredient_id', '{{%ingredients}}', 'id', 'CASCADE', 'RESTRICT');

    }

    public function safeDown()
    {
        $this->dropTable('{{%recipes_ingredients}}');
    }
}

My attempts to solve this problem on my own ended with the following search model:

Model

<?php
namespace app\modules\recipes\models\search;

use app\modules\recipes\models\Ingredients;
use app\modules\recipes\models\RecipesIngredients;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use app\modules\recipes\models\Recipe;

class RecipesIngredientsSearch extends RecipesIngredients
{
    private $_id = [];
    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['recipe_id', 'ingredient_id'], 'integer'],
        ];
    }

    /**
     * @inheritdoc
     */
    public function scenarios()
    {
        // bypass scenarios() implementation in the parent class
        return Model::scenarios();
    }

    /**
     * Creates data provider instance with search query applied
     *
     * @param array $params
     *
     * @return ActiveDataProvider
     */
    public function search($params)
    {

        foreach($params['Ingredients'] as $key => $value){

            if(!empty($value['name'])){
                $this->_id[] = $value['name'];
            }
        }

        $query = RecipesIngredients::find();
        $query->joinWith(['ingredient', 'recipe'])->where(['ingredients.status' => Ingredients::STATUS_ACTIVE, 'recipes.status' => Recipe::STATUS_ACTIVE]);
        $query->andWhere(['in', 'recipes_ingredients.ingredient_id', $this->_id]);
        $query->orderBy(['recipes.name' => SORT_ASC]);
        $query->addOrderBy(['recipes.id' => SORT_DESC]);

        // add conditions that should always apply here

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        $this->load($params);

        if (!$this->validate()) {
            // uncomment the following line if you do not want to return any records when validation fails
            // $query->where('0=1');
            return $dataProvider;
        }

        return $dataProvider;
    }
}

PS Also in the models there are connections
Recipe

public function getIngred()
    {
        return $this->hasMany(Ingredients::className(), ['id' => 'ingredient_id'])->viaTable('{{%recipes_ingredients}}', ['recipe_id' => 'id']);
    }

Ingredients

public function getRecipe()
    {
       return $this->hasMany(Recipe::className(), ['id' => 'recipe_id'])->viaTable('{{%recipes_ingredients}}', ['ingredient_id' => 'id']);
    }

RecipesIngredients

public function getRecipe()
    {
        return $this->hasOne(Recipe::className(), ['id' => 'recipe_id']);
    }

    public function getIngredient()
    {
        return $this->hasOne(Ingredients::className(), ['id' => 'ingredient_id']);
    }

Tell me how to solve this problem?

Answer 1

I think I should have gone the easy way. Since the number of ingredients in your task is limited, you can not make such a complex structure. If there were an unknown number of them, then yes, but you certainly know that there will be no more than 5 of them. So it's better to do this:
Recipes: id | name | ingredients_0| ingredients_1| ingredients_2| ingredients_3| ingrigient_4
ingrigients: id | name
And store in ingrigient_* the id of the ingredient
. The code will be simpler and queries will be easier, etc.
As an option, it would be possible to have one field with json, in which the names of the ingredients, the last mysql has the corresponding field type, but so far the hands have not reached to deal with this, so read before making a decision.

Answer 2

SELECT r.id, COUNT(ri.recipe_id) AS count_ingredients
FROM recipes AS r
LEFT JOIN recipes_ingredients AS ri ON r.id = ri.recipe_id
GROUP BY r.id

There will be a recipe output with the number of ingredients. You can add HAVING count_ingredients > _number_ to sample. It's just that if you need it through models, then the solution is the same as through CDbCommand, only using Model:: find ()-> select ('.....').