YII2 Complex query sample?

Good day friends, long kick search filter recipes? and that it is already out of ideas how this thing Dopinat but here's the thing.
There is a plate recipes
id | title ...
There is a sign products
id | title ...
There is a plate of ingredients in the recipe is a linking table Recipes and Products
id | recept_id | product_id ...
1 | 1 | 3
2 | 1 | 6
3 | 1 | 10
4 | 2 | 13
5 | 2 | 65
6 | 2 | 104

so how best to write a query which excludes the prescription of a specified ingredient from the search
the option of joinWith not working as it should
public function search($params, $category = null)
{
 // $recept_ingr_query = ReceptIngredients::find();
 $ingr = false;
$this->load($params);

 if($this->i_add_id) {
 $model = Product::findOne($this->i_add_id);
 $this->_add_filter_text = $model->title2;

 // $recept_ingr_query->andWhere(['product_id' => $this->i_add_id]);
 $ingr = true;
}
 if($this->i_rem_id) {
 $model = Product::findOne($this->i_rem_id);
 $this->_rem_filter_text = $model->title3;

 // $recept_ingr_query->andWhere(['<>', 'product_id', $this->i_rem_id]);
 $ingr = true;
}

 $query = Recept::find()
->joinWith([
 'receptIngridientLists' => function (Query $q) {
 if($this->i_add_id) {
 $q->andWhere([ReceptIngredients::tableName().'.product_id' => $this->i_add_id]);
}
 if($this->i_rem_id) {
 $q->andWhere(['<>', ReceptIngredients::tableName().'.product_id', $this->i_rem_id]);
}
 if($this->i_add_id || $this->i_rem_id) {
 // $q->groupBy('recept_id');
}
}
])
->with(
'category.children
'author',
 // 'receptIngridientLists',
'receptDescriptionLists',
'cooking',
'counter'
)
 // ->groupBy(ReceptIngredients::tableName().'.recept_id')
->active();

 if($category) {
 $query->andWhere(['category_id' => $category->id]);
}

/* // - this zakomirny option not considered - the approach is not correct 
 if ($ingr) {
 $data = $recept_ingr_query
->asArray()
->groupBy('recept_id')
->indexBy('recept_id')
->all();
 $query->andWhere(['id' => array_keys($data)]); // in this scenario, there will be kilometre id is not good
}
*/
.......... 
// here is a useful piece of code )
}
July 8th 19 at 12:18
2 answers
July 8th 19 at 12:20
createCommand normal sql, like

SELECT * FROM recept WHERE recept_id IN (
 SELECT DISTINCT FROM WHERE recept_id recept_product
 product_id NOT IN (1,2,3)
)
July 8th 19 at 12:22
Maybe try relations Yii2?
In the product model can be associated with the communication (Relation) through a junction table:
// Set the model's link "Recipes" (Recipes) with the model "Products" (Products) through a linking table "recipes_products":
 public function getProducts(){
 // hasMany() sets the "multiple" relationship, i.e. one-to-many.
 return $this->hasMany(Products::className(), ['id' => 'product_id'])->viaTable('recipes_products', ['recipe_id' => 'id']);
 }


But in the model search just:
public function search($params)
{
 $query = Recipes::find();

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

$this->load($params);

 if (!$this->validate()) {
 // Don't return anything if the validation is not passed
 // $query->where('0=1');

 return $dataProvider;
}

 // Similar to LEFT JOIN, only a little different.
$query->joinWith('products');

 $query->andFilterWhere(['NOT IN', 'recipes_products.product_id'/* binding table */, $this->product_exclude/* an array of IDs to exclude products */]);

// Here add other desired search parameters, for example : $query->andFilterWhere(['products.id' => $this->product_id]);

 return $dataProvider;
 }


But, I don't fully understand

Find more questions by tags MySQLPHP