Magento: How to filter product collection using 2 or more category filters?
Suppose, you have a product collection and you want to filter it by category. Suppose, you want to filter it by more than one category. You can use addCategoryFilter if you have only one category. But, what if you want to filter by more than one category?
Category ids are stored for product in a comma separated way. So, to filter product collection by more than one category, you have to use:
addAttributeToFilter(‘category_ids’,array(‘finset’=>$categoryIds));
$categoryIds can be a single category id or comma separated category ids.
The following code filters product collection by two category ids (36 and 37).
$_productCollection = Mage::getResourceModel('reports/product_collection')
->addAttributeToSelect('*')
->addAttributeToFilter('category_ids',array('finset'=>'36,37'));
finset does so… when we use finset, the mysql function find_in_set is used in the sql query by Magento.
mysql function find_in_set:
FIND_IN_SET() looks for the first occurrence of a string within another string containing comma-separated values.
SELECT FIND_IN_SET(‘b’,'a,b,c,d’); // result = 2
Cheers!
From Mukesh Chapagain's Blog | Post Magento: How to filter product collection using 2 or more category filters?
Thanks. Will the same method work for filtering based on certain attribute. I have created a multiple select attribute with pre assigned values and been trying to list the all products having any value option of said attribute. Having issues filtering and listing for that matter. Here’s my code.
$model = Mage::getModel(‘catalog/product’);
$collection = $model->getCollection();
$collection->addAttributeToSelect(‘my_attribute’);
$collection->addAttributeToFilter(‘my_attribute’,'finset’=>(‘value_name’));
$collection->load();
Thanks,
Mosses
yes, you should be able to do so.
It looks like this doesn’t work in 1.4.*
Seems the ‘category_ids’ field has been removed from the ‘catalog_product_entity’ table.
Trying to figure out a new workaround – anyone have any ideas??
Now the category ids are in catalog_category_product_index
For example, if you want return all product categories your query should like this:
SELECT * FROM catalog_category_product_index WHERE product_id = 123
As the filtering way has been changed in magento 1.4. So, can someone please give an example that how we can filter products by multiple categories in magento 1.4?
for version 1.4.x
create a local copy and edit Collection.php
e.g.
code\local\Mage\Catalog\Model\Resource\Eav\Mysql4\Product
create a new function using the guts of _applyProductLimitations()
e.g.
public function addCategoriesFilter(array $categories) {
…
$conditions[] = $this->getConnection()->quoteInto(‘cat_index.category_id IN(‘ . implode(“,”, $categories) . ‘)’, “”);
…
}
then make your call
e.g.
$this->_productCollection = Mage::getModel(‘catalog/resource_eav_mysql4_product_collection’)
->setStoreId($storeId)
->addCategoriesFilter(array(9,11,44));
use addCategoryFilter instead
Leave your response!
Donate
Help this blog grow!
Categories
Archives
Recent Tweets
Recommendations
Find me on Facebook
recent posts
most popular
tag cloud