How to filter by unknown attributes?

Question

MySQL

A filter is made for products in the category, there are three tables - products(id, name), attributes(id, value), products_attributes(product_id, attribute_id, value).
Since I don't know which products have which attributes, I get them in the following way:

// Продукты:
$products = Products::find()
            ->leftJoin('product_category', 'product_category.id=category_id')
            ->with(['productAttributeValues'])->where(['product_category.slug' => $slug, 'products.active' => 1]);
// Атрибуты:
$attrs = [];
        foreach($products->all() as $product){
            foreach($product->productAttributeValues as $item){
                $attrs[$item['attribute_id']][$item['value']] = $item['value'];
            }
        }

Array with received attributes:

array (size=3)
  1 => 
    array (size=2)
      10 => string '10' (length=2)
      50 => string '50' (length=2)
  2 => 
    array (size=2)
      15 => string '15' (length=2)
      12 => string '12' (length=2)
  3 => 
    array (size=2)
      20 => string '20' (length=2)
      600 => string '600' (length=3)

In the view, I display drop-down lists in a loop:

foreach($attrs as $name =>  $attr){
        echo '<label class="control-label">'.\common\models\entities\ProductAttribute::getNameById($name).'</label>';
        echo Html::dropDownList('FrontProductsSearch[ProductAttributeValues]['.$name.']', null, $attr, ['prompt' => '']);
    }

In the search model I make a request:

if($params['FrontProductsSearch']['ProductAttributeValues']){
            foreach($params['FrontProductsSearch']['ProductAttributeValues'] as $n => $productAttributeValue){
                if(!empty($productAttributeValue)){
                    $query->andWhere(['and',
                        ['product_attribute_value.attribute_id' => $n],
                        ['product_attribute_value.value' => $productAttributeValue]
                    ]);
                }

            }
        }

If you select the value of only one drop-down list in the filter, the search works, the request code:

SELECT `products`.* FROM `products`
LEFT JOIN `product_category` ON `products`.`category_id` = `product_category`.`id`
LEFT JOIN `product_attribute_value` ON `products`.`id` = `product_attribute_value`.`product_id`
WHERE ((`active`=1)
       AND (`product_category`.`slug`='phones'))
       AND ((`product_attribute_value`.`attribute_id`=1)
       AND (`product_attribute_value`.`value`='10'))

But if you select the value of two or more drop-down lists in the filter, the search returns nothing, the request code:

SELECT `products`.* FROM `products`
LEFT JOIN `product_category` ON `products`.`category_id` = `product_category`.`id`
LEFT JOIN `product_attribute_value` ON `products`.`id` = `product_attribute_value`.`product_id`
WHERE ((`active`=1)
    AND (`product_category`.`slug`='phones'))
    AND ((`product_attribute_value`.`attribute_id`=1)
    AND (`product_attribute_value`.`value`='10'))
    AND ((`product_attribute_value`.`attribute_id`=2)
    AND (`product_attribute_value`.`value`='15'))

What could be wrong?

Answer 1

In the last query, you are trying to find a row whose `attribute_id` is both 1 and 2.
You need to find different rows in product_attribute_value with different conditions, the simplest thing that comes to mind is subqueries with exists:

select * from products where active = 1
and exists (select from product_attribute_value where product_id = products.id and attribute_id = 1 and value = 10)
and exists (select from product_attribute_value where product_id = products.id and attribute_id = 2 and value = 15)