1

I have Multiselect product attribute and i want to filter product by that attribute.

Currently i am using below code,

$productCollection = $this->getLayer()->getProductCollection();
$productCollection->addAttributeToFilter(
                    array(
                        array('attribute'=> 'fabriccolor','finset' => 16),
                        array('attribute'=> 'fabriccolor','finset' => 17),                  
                    )
                );
echo $productCollection->getSelect();

The query i get like

SELECT e.*, cat_index.position AS cat_index_position, price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price, at_fabriccolor.value AS fabriccolor FROM catalog_product_entity AS e INNER JOIN catalog_category_product_index_store1 AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=3 INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '1' INNER JOIN catalog_product_entity_varchar AS at_fabriccolor ON (at_fabriccolor.entity_id = e.entity_id) AND (at_fabriccolor.attribute_id = '155') AND (at_fabriccolor.store_id = 0) WHERE ((FIND_IN_SET(16, at_fabriccolor.value)) OR (FIND_IN_SET(17, at_fabriccolor.value)))

The products i get which has fabriccolor value 16 or 17. Please check this screenshot,

enter image description here

But i want to filter the product which have both fabriccolor value 16 and 17 by code. I want to update the query from WHERE ((FIND_IN_SET(16,> at_fabriccolor.value)) OR (FIND_IN_SET(17, at_fabriccolor.value))) to WHERE ((FIND_IN_SET(16,> at_fabriccolor.value)) AND (FIND_IN_SET(17, at_fabriccolor.value))).

If i put the AND in the query then it gives me correct products which i want. Please check below screenshot,

enter image description here

So, how can i filter products by AND insted of OR. Please help me if you have any solution.

Thanks in Advance!!!

1
  • For "AND", just repeat the $productCollection->addAttributeToFilter() for each value or pass value as array in single stament. – Divyarajsinh Dodiya Apr 2 at 7:26
0

How about you change your code like this:

$productCollection = $this->getLayer()->getProductCollection();
$productCollection->addAttributeToFilter('fabricolor', ['finset' => [16,17]]);
echo $productCollection->getSelect();

or

$productCollection = $this->getLayer()->getProductCollection();
$productCollection->addAttributeToFilter('fabricolor', ['finset' => 16]);
$productCollection->addAttributeToFilter('fabricolor', ['finset' => 17]);
echo $productCollection->getSelect();
1
  • updated my answer with another way to try – Diana Botean Apr 2 at 7:21
0

I have the code below working on my local:

public function afterGetProductCollection(
        \Magento\Catalog\Model\Layer $subject,
        \Magento\Catalog\Model\ResourceModel\Product\Collection $collection
    ) {
        $values = [117,136,124,125,127];
        foreach ($values as $value) {
            $filters[] = [
                'attribute'=> 'style_general',
                'finset' => $value
            ];
        }

        $collection->addAttributeToFilter($filters);

        return $collection;
    }

I believe with your snippet, it would look like:

$productCollection = $this->getLayer()->getProductCollection();
        $productCollection->addAttributeToFilter(
            ['attribute'=> 'fabricolor', 'finset' => 16],
            ['attribute'=> 'fabricolor', 'finset' => 17],
        );
      
   

Now, I appreciate this is (very) similar to what you have. So my next question may be to see what is the full details (in the eav-attribute) of your attribute?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.