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

For Magento version 1.4 and higher

 $_productCollection = Mage::getModel('catalog/product')
                 ->getCollection()
                 ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
                 ->addAttributeToFilter('category_id', array('in' => array('finset' => '36,37')))
                 ->addAttributeToSelect('*')
                 ->setPageSize(5);

If joinField is not working then you can try using joinTable instead.

 $_productCollection = Mage::getModel('catalog/product')
                 ->getCollection()
                 ->joinTable('catalog_category_product', 'product_id=entity_id', array('category_id'=>'category_id'), null, 'left')
                 ->addAttributeToFilter('category_id', array('in' => array('finset' => '36,37')))
                 ->addAttributeToSelect('*')
                 ->setPageSize(5);

Hope it helps. Thanks.