Magento – Magento 2: Product collection with filters

magento2product-collection

Can anyone tell me how can i get product collection with filters?
I'm use magento 2 sample data now. So i'm trying to get in product collection like selected on picture
enter image description here
with attribure style=backpack and activity=yoga;
My code:

$productcollection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\Collection')
        ->addAttributeToSelect('style_bags','activity')
        ->addAttributeToFilter(
            array(
                array('attribute'=>'style_bags','eq'=>24),
                array('attribute'=>'activity','eq'=>8)
            )
        );

echo $productcollection->count();

But i'm get 4 products instead 2. But if i'll live only

array('attribute'=>'style_bags','eq'=>24)

without

array('attribute'=>'activity','eq'=>8)

it will works correct.

Can anyoune explain me how it works?
Thank you.

UPD: It's seems to me, that in that case products selects with OR condition. But i need AND. If i change my code to 

$productcollection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\Collection')
            ->addAttributeToSelect('style_bags','activity')
            ->addAttributeToFilter('style_bags',['eq'=>24])
            ->addAttributeToFilter('activity',['eq'=>8]);

I'ce got emty set.

Best Answer

1) We can print the sql with getSelect()

echo $productcollection->getSelect();

2) Add attribute filter to collection vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection::addAttributeToFilter():

If $attribute is an array, it will add OR condition with the following format:

 array(
     array('attribute'=>'firstname', 'like'=>'test%'),
     array('attribute'=>'lastname', 'like'=>'test%'),
  )

vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection::addAttributeToFilter()

    if (is_array($attribute)) {
        $sqlArr = [];
        foreach ($attribute as $condition) {
            $sqlArr[] = $this->_getAttributeConditionSql($condition['attribute'], $condition, $joinType);
        }
        $conditionSql = '(' . implode(') OR (', $sqlArr) . ')';

So, as you said, your fist case will add OR condition.

There is a note when adding Eav attribute filter to collection. Take a look eav_attribute table, we can select color attribute:

SELECT * FROM eav_attribute e WHERE e.attribute_code = 'color';
  • backend_type is int - We can find the values in catalog_product_entity_int table. We use the values in our collection.

  • frontend_input is select - The value of this field to set type of frontend field type.

    $collection = $objectManager->create('Magento\Catalog\Model\ResourceModel\Product\Collection');
$productcollection =
    $collection->addAttributeToSelect('*')
            ->addAttributeToFilter(
                [
                    //['attribute'=>'type_id','neq'=> 'simple'],
                    ['attribute'=>'color','eq'=> 4] // Color filter
                ]
            );


echo $productcollection->getSelect();

In your case, you should check the backend_type again.

3) You also can try with the Service Contracts Layer

Your construct:

 protected $_productRepository;

protected $_searchCriteriaBuilder;

public function __construct(
    \Magento\Catalog\Api\ProductRepositoryInterface $productRepository,
    \Magento\Framework\Api\SearchCriteriaBuilder $searchCriteriaBuilder
) {
    $this->_productRepository = $productRepository;
    $this->_searchCriteriaBuilder = $searchCriteriaBuilder;
}

In your custom method:

$searchCriteria = $this->_searchCriteriaBuilder->addFilter('color', 4, 'eq')->create();
$searchResults = $this->_productRepository->getList($searchCriteria);
$products = $searchResults->getItems();
Related Topic