Как правильно составить запрос в поисковой модели yii2?

Question

[Дмитрий]

Доброе утро.
Есть такая задача:

Пользователь может выбрать до 5­ти ингредиентов для приготовления блюда, при
этом:
1. Если найдены блюда с полным совпадением ингредиентов ­ вывести
только их.
2. Если найдены блюда с частичным совпадением ингредиентов ­ вывести
в порядке уменьшения совпадения ингредиентов вплоть до 2­х.
3. Если найдены блюда с совпадением менее чем 2 ингредиента или не
найдены вовсе ­ вывести “Ничего не найдено”.
4. Если выбрано менее 2­х ингредиентов ­ не производить поиск, выдать
сообщение: “Выберите больше ингредиентов”.

Создал три таблицы:
1) Recipes // таблица рецептов
2) Ingredients // таблица ингредиентов
3) RecipesIngredients // связующая таблица
Вот миграции таблиц:

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}}');
    }
}

Мои попытки самостоятельно решить данную задачу завершились на такой поисковой модели:

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;
    }
}

P.S Так же в моделях есть связи
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']);
    }

Подскажите, как решить данную задачу?

Comments

[Максим Кожин]

Это тестовая задача от работодателя?

[Максим Федоров]

1. Если найдены блюда с полным совпадением ингредиентов ­вывести только их.

Что считается полным совпадение ингредиентов? Если в блюде 5 ингредиентов а пользователь выбрал 4 которые есть в блюде это полное совпадение? или полное совпадение это если пользователь выбрал все 5 ингредиентов блюда (грубо говоря угадал на 100% его состав)?

[Дмитрий]

Максим Федоров: Не совсем. Выводить блюда надо от пяти ингредиентов в сторону уменьшения до двух составляющих.
Сначала вывести те, где совпадение на 100%, и если на 100% совпадения, то вывести только эти блюда. Если нет совпадений на 100%, то выводить все по количеству ингредиентов, от большего к меньшему.
А если совпадение меньше двух, то сообщать, что ничего не найдено.

[Максим Федоров]

slo_nik: это я понял... я имею ввиду что считать 100%? 100% этого когда пользователь полностью угадал состав продукта, или 100% это когда в продукте есть все ингредиенты которые выбрал пользователь?

Answer 1

[Максим Тимофеев]

Мне кажется надо было пойти простым путем. Так как у Вас в задаче количество ингредиентов ограничено, то можно не делать такую сложную структуру. Если бы их было неизвестное количество, то да, а так Вам заведомо известно, что их 5 и более не будет. Так что оптимальнее сделать так:
Recipes: id | name | ingrigient_0| ingrigient_1| ingrigient_2| ingrigient_3| ingrigient_4
ingrigients: id | name
И хранить в ingrigient_* id ингридиента
Будет и код проще и запросы легче и т.д.
Как вариант можно было бы и одно поле с json, в котором имена ингридиентов, последний mysql имеет соответствующий тип поля, но тут пока руки не дошли разобраться с этим, так что почитайте прежде чем принимать решение.

Comments

[Vlad]

А что потом, если будет 20 ингредиентов или доп поля в ингредиентах, типа количества граммов или прочую лабуду? Делается все запросом.

[Андрей Токмаков]

Вы видать не правильно поняли автора. Автор имел ввиду поиск по 5 ингредиентам, а в самом блюде их может быть и 10 и 15, на сколько я понял

[Максим Тимофеев]

Андрей Токмаков: Vlad: Я общался с автором по скайпу, поэтому знаю, что их будет именно 5 для каждого блюда. Если произвольное количество - конечно же будет другая песня.

[Дмитрий]

Максим Тимофеев: Я думал над таким вариантом, прописывать id каждого ингредиента в таблице рецептов, но в будущем это может вызвать сложности.

[Максим Тимофеев]

slo_nik: Если будет добавляться много - да. Но если ожидается через год расширить кол-во ингредиентов до 7 скажем, то нет проблем.

[Дмитрий]

Максим Тимофеев: Если бы я знал, что ожидать через год...)))

[Андрей Токмаков]

Максим Тимофеев: Ну тогда это не логично, что за рецепты из 5 ингредиентов!? Это только хлеб испечь если, бо'льшая часть рецептов будет содержать на порядок больше индредиентов

[Максим Тимофеев]

Андрей Токмаков: Согласен, но тз есть тз. Надо уточнять. Просто если только 5 - то смысла в трех таблицах нет. Будет сложно, будет медленнее, запросы трехэтажные, а так все плоско и просто.

Answer 2

[Vlad]

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

Будет вывод рецепта с количеством ингредиентов. Можете добавить HAVING count_ingredients > _число_ для выборки. Просто вам если через модели надо - то решение такое же как через CDbCommand, только используя Model::find()->select('.....').

Comments

[Дмитрий]

Благодарю за подсказку, буду пробовать