addAttributeToFilter for Multislect Attribute Update Query Magento 2

Question

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,

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,

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

Thanks in Advance!!!

Answer 1

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();

Answer 2

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?