Home » Magento, MySQL

Magento: How to filter product collection using 2 or more category filters?

31 December 2009 19,050 views Popularity: 38% Share/Bookmark

0
email

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!

Related posts:

  1. Magento: Get category name and url from product
  2. Magento: Get sub categories and product count
  3. Magento: How to search or filter by multiselect attribute in admin grid?
  4. Magento 1.4: No products displayed in category listing
  5. Magento: Get Product Collection by Type
  6. Magento: Very Useful Collection Functions
  7. Magento: Join, filter, select and sort attributes, fields and tables
  8. Magento: Get current and parent category
  9. Magento: Set Random Order in Collection using RAND()
  10. Magento: Adding category attributes
  • Mosses

    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

  • admin

    yes, you should be able to do so.

  • http://www.webr.co.uk Rik

    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??

  • http://www.option2c.com DCA

    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

  • http://www.activecodewar.com Shahid

    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?

  • Ben Incani

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

  • anshuman

    use addCategoryFilter instead

  • http://www.mastersallen.co.uk Hugh Wood

    As of writing addCategoryFilter only adds one filter, adding a second overwrites the 1st.

    Due to magento 1.4 moving the category column from its original table the top method does not work and at present Ben Incani’s solution is the only viable solution to use 1.4 with this feature.

    However doing this sort of patch is not recommended unless you have an upgrade path in place for when magento fixes this issue.

  • http://twozao.com Ela

    I need the same thing, how we can set the filter for category’s, Not working this one.

  • Manuel

    Hi guys, i need the same, how it hard are get some functionality in magento and how easy is to get others features, i would like the magento team had been worked a bit more with object paradigm, cause is incredible how to retrieve a simple thing cross for almost 15 different classes and between them get cross references that became impossible to follow it, i was trying to modify the simple search to get a filter by categories and it was so hard follow a line of thinking, crossing files for everywhere.

  • Thegreat Magento

    How to get product creation time ?

  • Jaishalini

    if i add this line
    ->addAttributeToFilter(‘category_ids’,array(‘finset’=>’36,37′));
    in my product collection
    that throw a error unknown column category_ids in where class .

    what to do.

  • Cosmin_purcarea

    Hello, you can help me? I want to sort Upsell products by category. I have Magento 1.6.x
    Thank you very much

  • jazkat

        Unfortunately your code didn’t work for me (Magento v.1.3.2.4), however it lead me to the right direction. I’m getting category ids in array, so this is my solution:
       
        —————
       
        $categoryIDs = $_GET['category'];   
        if(is_array($categoryIDs)){
            $multipleCategoriesSearch = array();
            foreach ($categoryIDs as $value) {
                // prepare array for addCategoryFilter() for searching through multiple categories (prepare for OR operation)
                $multipleCategoriesSearch[] = array(‘attribute’=>’category_ids’,'finset’=>$value); 
            }
            // perform search as OR operation  (ie: if category is 4 OR 11)
            $this->_productCollection->addFieldToFilter(‘category_ids’, array($multipleCategoriesSearch));  //  addFieldToFilter()  ‘OR’ operation   (http://stackoverflow.com/questions/3826474/magento-addfieldtofilter-two-fields-match-as-or-not-and/5988369#5988369)
        }
        else {
          if(is_numeric($categoryIDs)) {   //not an array, single category selected in drop-down box
            $this->_productCollection->addCategoryFilter(Mage::getModel(‘catalog/category’)->load($categoryIDs),true);
          }
        }

  • Rri Thuong Le

    Thank you!
    You save my time :)