CODEWITHSUNDEEP
phpsqlyii2query-builderyii2-model
RBR
RBR

Reputation: 13

Yii2 filtering products by attributes filter

I have a database of products which I'd like to filter by Attributes. I have a section of products. Each product should have a collection of attributes e.g. color and weight. I have 3 products. First product has attribute: color = red, second has color = red and weight = 1000kg and last product has color = black; When I choose color red and black everything is ok but when I choose color red and weight 1000kg filter not showing products. This is part of my ProductSearch model.

public function search($cat, $params=[])
{
    $query = Product::find();
    $query->joinWith(['productDesc','productCategory','productAttributes']);
    $query->andFilterWhere([
        'product.id'                => $this->id,
        'product.quantity'          => $this->quantity,
        'product.stock_status_id'   => $this->stock_status_id,
        'product.product_status_id' => $this->product_status_id
    ]); // and many more

    // Getting data from url: 
    // category?categories_path=some_category&f=6-Red;7-1000kg;&sort=price_vat
    if(isset($params['f'])) { 
        $filters_raw = explode(';', $params['f']);
        $filters_raw = array_filter($filters_raw);
        $attr_ids    = [];
        $attr_values = [];
        foreach ($filters_raw as $filter_arr) {
            $filters = explode('-', $filter_arr);
            $filter_results[$filters[0]][] = $filters[1];

        }
    }
    if(isset($filter_results)) {
        foreach ($filter_results as $attr_id => $filter_res) {
            $query->andFilterWhere([
                'and', 
                ['product_attribute.attribute_id' => $attr_id],
                ['product_attribute.value'        => $filter_res]
            ]);
        }
    }
}

What is wrong?

Upvotes: 1

Views: 608

Answers (1)

rob006
rob006

Reputation: 22174

If one product can have multiple attributes, simple join will result multiple rows for each attribute (so one product may be repeated multiple times):

| product.id | product_attribute.attribute_id | product_attribute.value |
| ---------- | ------------------------------ | ----------------------- |
| 1          | 120                            | red                     |
| 1          | 121                            | 1000kg                  |

So if you create a query with conditions for two attributes, they will never be met, because there is no row which will match "color = red and weight = 1000kg" condition. You need to join multiple attributes in one row, to have something like this:

| product.id | pa1.attribute_id | pa1.value | pa2.attribute_id | pa2.value |
| ---------- | ---------------- | --------- | ---------------- | --------- |
| 1          | 120              | red       | 121              | 1000kg    |

For that you need to remove direct join with attributes:

$query->joinWith(['productDesc','productCategory']);

And join every attribute separately for every filter:

foreach ($filter_results as $attr_id => $filter_res) {
    $query->joinWith("productAttributes as productAttributes{$attr_id}");
    $query->andWhere([
        "productAttributes{$attr_id}.attribute_id" => $attr_id,
        "productAttributes{$attr_id}.value" => $filter_res,
    ]);
}

Upvotes: 1

Related Questions