Magento 2 Filter Product Collection by Multiple Attributes

Question

In Magento 2.2.2, How Can Filter Product Collection by Multiple Attributes.

I have the attribute_code with array values.

attribute_code: my_attribute_code

attribute_code values:

Array
(
  [0] => 12
  [1] => 10
);

Answer 1

Try this Code :

class HelloWorld extends \Magento\Framework\View\Element\Template
{    
    protected $_productCollectionFactory;

    public function __construct(
        \Magento\Backend\Block\Template\Context $context,        
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory,        
        array $data = []
    )
    {    
        $this->_productCollectionFactory = $productCollectionFactory;    
        parent::__construct($context, $data);
    }

    public function getProductCollection()
    {
        $collection = $this->_productCollectionFactory->create();
        $collection->addAttributeToSelect('*');
        $collection->addFieldToFilter('my_attribute_code', array('in' => array(12,10)));
        return $collection;
    }
}

Answer 2

What you need to do is create a 'FilterBuilder' object for each criterion you are searching by, then assign them to the 'SearchCriteria' object. Finally, pass the Search Criteria object to the Repository you want to filter.

Note that the query is executed against the \CustomVendor\CustomModule\Api\PriceRepositoryInterface - this is a custom table I added to my local install of Magento 2. There is nothing stopping you from replacing it with \Magento\Catalog\Api\ProductRepositoryInterface (for example) if you wanted to get a list of products filtered by your attribute(s).

<?php

class CustomPrice
{
    /**
     * @var \Magento\Framework\Api\FilterBuilder
     */
    private $filterBuilder;

    /**
     * @var \Magento\Framework\Api\SearchCriteriaBuilder
     */
    private $searchCriteriaBuilder;

    /**
     * @var \CustomVendor\CustomModule\Api\PriceRepositoryInterface
     */
    private $priceRepository;


    /**
     * Constructor.
     *
     * @param \Magento\Framework\Api\FilterBuilder                           $filterBuilder
     * @param \Magento\Framework\Api\SearchCriteriaBuilder                   $searchCriteriaBuilder
     * @param \CustomVendor\CustomModule\Api\PriceRepositoryInterface        $priceRepository
     *
     * @throws \Magento\Framework\Exception\LocalizedException
     */
    public function __construct(
        FilterBuilder $filterBuilder,
        SearchCriteriaBuilder $searchCriteriaBuilder,
        PriceRepositoryInterface $priceRepository
    ) {
        $this->filterBuilder                    = $filterBuilder;
        $this->searchCriteriaBuilder            = $searchCriteriaBuilder;
        $this->priceRepository                  = $priceRepository;
    }

    /**
     * @param int    $customerId
     * @param int    $websiteId
     * @param int    $quantity
     *
     * @return float
     * @throws \Magento\Framework\Exception\LocalizedException
     */
    private function getCustomPrice(
        int $customerId,
        int $websiteId,
        int $quantity = 1
    ) {

        // Adding a single filter adds a 'WHERE' clause to underlying SQL statement used to query the database
        // Multiple filters will be added as an 'AND' clause to the 'WHERE' clause of the underlying SQL statement
        // E.g. 'WHERE (my_attribute_code IN(12, 10)) AND (customer_id = $customerId) AND ...' etc

        // WHERE my_attribute_code IN(12, 10)
        $myAttributeCodeFilter      = $this->filterBuilder->setField('my_attribute_code')
                                          ->setConditionType('in')
                                          ->setValue([12, 10])
                                          ->create();

        // WHERE customer_id = $customerId
        $customerIdFilter            = $this->filterBuilder->setField('customer_id')
                                                           ->setConditionType('=')
                                                           ->setValue($customerId)
                                                           ->create();

        // WHERE website_id = $websiteId
        $websiteIdFilter             = $this->filterBuilder->setField('website_id')
                                                           ->setConditionType('=')
                                                           ->setValue($websiteId)
                                                           ->create();

        // WHERE quantity <= $quantity
        $quantityFilter              = $this->filterBuilder->setField('quantity')
                                                           ->setConditionType('<=')
                                                           ->setValue($quantity)
                                                           ->create();

        $criteria                    = $this->searchCriteriaBuilder->addFilters([
            $myAttributeCodeFilter,
            $customerIdFilter,
            $websiteIdFilter,
            $quantityFilter,
        ])->create();

        $prices = $this->priceRepository->getList($criteria)->getItems();

        return $prices;
    }
}