How to do a search on a field in a linked table?

Question

Yii

With this code, I search for products by ingredient ID, if the ingredient is not visible, then the product should not be visible.

$subQuery = (new Query())
            ->select('product.id as id, product.name as name, ingredient.visible, count(product_ingredient.product_id) as cnt from product')
            ->innerJoin(ProductIngredient::tableName(), 'product.id = product_ingredient.product_id')
            ->innerJoin(Ingredient::tableName(), 'ingredient.id = product_ingredient.ingredient_id')
            ->where(['product_ingredient.ingredient_id' => $this->ids])
            ->andFilterWhere(['ingredient.visible' => 1])
            ->groupBy('product.id, product.name, ingredient.visible')
            ->orderBy('cnt');

But since A product can have multiple ingredients. The expression ->andFilterWhere(['ingredient.visible' => 1]) is invalidated if at least 1 ingredient is visible. How to fix?
ProductIngredient - intermediate table

Answer 1

In where we replace "ingredient.visible" with "MIN(ingredient.visible) as vis", andFilterWhere we change with having(['vis'=>1]).
Notes:
Why so many factions? "product.id" is more than enough.
Why is either ProductIngredient::tableName() or "product_ingredient" a string? Can at least bring everything to a common style in your piece of code?
Why andFilterWhere? It is used by the GridView + DataProvider to add conditions to the existing selection.