Stack Exchange
addAttributeToFilter for Multislect Attribute Update Query Magento 2
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!!!

For "AND", just repeat the $productCollection->addAttributeToFilter() for each value or pass value as array in single stament.

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


updated my answer with another way to try

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?
