Magento2 Product Attribute Collection – Filter Product Collection by SKU or Name

collection;filtermagento2product-attribute

I have a variable which containing value to filter both name or sku, i tried to filter the product collection using this variable like this:

$queryName = 'bat';
$category = $this->_categoryFactory->create()->load('2');
$collection = $this->_productCollectionFactory->create();
$collection->addAttributeToSelect('*');
$collection->addCategoryFilter($category);
$collection->addAttributeToFilter(['name', 'sku'],
          [
           ['like' => '%'.$queryName.'%'],
           ['like' => '%'.$queryName.'%']
          ]);
$collection->setPage(1,15)->load();
return $collection;

but i got an error like this:

Warning: Illegal string offset 'attribute' in
/../vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php
on line 362

Best Answer

The syntax for addAttributeToFilter() that you are using was possible in Magento 1, but in Magento 2 there is only one way to use the method to add multiple filters with OR, namely with a single argument:

$collection->addAttributeToFilter(
      [
       ['attribute' => 'name', 'like' => '%'.$queryName.'%'],
       ['attribute' => 'sku', 'like' => '%'.$queryName.'%']
      ]);

That's why you get this error message: Magento expects an array, where you pass 'name' and 'sku'. And while you can use square brackets to access characters in a string ('name'[0]), a non-numeric index as in 'name'['attribute'] throws an error.

Service contracts

You might wonder, if you can achieve the same using the product repository instead of collections, which is the recommended and more upgrade safe way. Repositories are part of the @api service contract of each module and are treated carefully regarding backwards compatibility, while collections are an implementation detail which will not stay the same in the long run.

If it's possible to achieve what you need with repositories, you should do it (see: When Should We Use a Repository and Factory in Magento 2?))

In this case, it's too complicated and imperformant (as of magento-2.2):

There's Magento\Catalog\Api\CategoryLinkManagementInterface::getAssignedProducts() to retrieve a sorted list of SKUs for a given category id, but it does not accept additional filters
You cannot use the filter argument of Magento\Catalog\Api\ProductRepositoryInterface::getList() for the category, because it is not an attribute, but an external n:m relation.

So it would require two steps:

load all skus for the category with CategoryLinkManagementInterface::getAssignedProducts().
use ProductRepositoryInterface::getList() to get a list of products with filter conditions:
- SKU in (previous result) AND
- (your additional filters)

Related Solutions

Magento – Create a sortable and filterable custom attribute to stand-alone entity

When adding the product attribute, set used_in_product_listing, used_for_sort_by and filterable to true.

You can do that with the following update script:

$installer = Mage::getResourceModel('catalog/setup', 'catalog_setup');
$installer->startSetup();
$installer->updateAttribute('catalog_product', 'product_type', 'used_in_product_listing', 1);
$installer->updateAttribute('catalog_product', 'product_type', 'used_for_sort_by', 1);
$installer->updateAttribute('catalog_product', 'product_type', 'is_filterable', 1);
$installer->endSetup();

Thats all, if if you are NOT using the flat catalog.

If you enable the flat catalog product feature, then you need to ensure that your custom source model implements the methods getFlatColums(), getFlatIndexes() and getFlatUpdateSelect().
If they are missing, your attribute won't be included in the generated flat tables, even if used_in_product_listing is enabled.
Have a look at the class Mage_Eav_Model_Entity_Attribute_Source_Boolean for an example. You can probably copy over the methods with minimal adjustments into your custom source model from there. Otherwise, please ask a new question.

Magento 1 – How to Filter Category Collection with Nested OR/AND Statements

Unfortunately you cannot nest conditions and combine AND and OR arbitrarily like this. I tried to explain the parameters for addFieldToFilter here: addFilter vs addFieldToFilter

addFieldToFilter() can take an array of fields with an array of conditions, or a single field with a single condition:

...

What we cannot do with this is the inner combinations with AND. Usually that would mean that you have to resort to Zend_Db_Expr or manipulating the Zend_Db_Select query directly.

But in your case, we can rearrange the query to make it fit:

(
    attribute_1 LIKE '%foo%'
    AND
    attribute_2 LIKE '%bar%'    
)
OR
(
    attribute_1 LIKE '%foo%'
    AND 
    attribute_2 is NULL
)

is equivalent to

attribute_1 LIKE '%foo%'
AND
(
    attribute_2 LIKE '%bar%'    
    OR 
    attribute_2 is NULL
)

And this works well with Magento, because separate calls to addAttributeToFilter are combined with AND, and conditions for a single attribute can be combined with OR:

$collection->addAttributeToFilter('attribute_1', ['like' => '%foo%']);
$collection->addAttributeToFilter('attribute_2', [['like' => '%bar%'], ['null' => true]]);

Best Answer

Service contracts

Related Solutions

Magento – Create a sortable and filterable custom attribute to stand-alone entity

Magento 1 – How to Filter Category Collection with Nested OR/AND Statements

Related Topic