YII2 Complex query sample?

Question

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

nikita.Stracke asked July 8th 19 at 12:18

Related questions

More answers about "YII2 Complex query sample?"

2 answers

melany answered on · Answer 1 · 2019-07-08T12:20:21.231Z

melany answered on 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)
)

Brock_Nader answered on · Answer 2 · 2019-07-08T12:22:21.231Z

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