Magento filter product collection by multiple categories

Question

Is there an easy way to filter a product collection by multiple categories? To get all items in any of the listed categories? addCategoryFilter doesn't seem to allow an array.

Is the only way to get the collections for each category of interest separately then merge them?

I understand it used to be possible with something like

addAttributeToFilter('category_ids',array('finset'=>array('1','2')))

or similar, but that this is no longer possible since 1.4.

Note: I am using 1.6, and in case it's of any use, I'm using something like this:

$product = Mage::getModel('catalog/product');
$_productCollection = $product->getCollection()
  ->addAttributeToSelect('*')
  ->addAttributeToFilter('status',1)
  ->addStoreFilter();

Politi

Unfortunetly there isn't any easy way to do it. – Illlooking

Answer 1

The way Magento works now, is to get the Store, and on the store, you can get the categories from the storecollection like $oStoreCollection->addCategoryFilter(array('1','2'));

I came across a solution that might help you, found here at:

http://www.magentocommerce.com/boards/&/viewthread/201114/#t329230

The code they use, looks like this: Override Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Collection, and add the following methods:

public function addCategoriesFilter($categories)
    {
        $this->_productLimitationFilters['category_ids'] = $categories;

        if ($this->getStoreId() == Mage_Core_Model_App::ADMIN_STORE_ID) {
            $this->_applyZeroStoreProductLimitations();
        } else {
            $this->_applyProductLimitations();
        }

        return $this;
    }

    protected function _applyProductLimitations()
    {
        $this->_prepareProductLimitationFilters();
        $this->_productLimitationJoinWebsite();
        $this->_productLimitationJoinPrice();
        $filters = $this->_productLimitationFilters;

        // Addition: support for filtering multiple categories.
        if (!isset($filters['category_id']) && !isset($filters['category_ids']) && !isset($filters['visibility'])) {
            return $this;
        }

        $conditions = array(
            'cat_index.product_id=e.entity_id',
            $this->getConnection()->quoteInto('cat_index.store_id=?', $filters['store_id'])
        );
        if (isset($filters['visibility']) && !isset($filters['store_table'])) {
            $conditions[] = $this->getConnection()
                ->quoteInto('cat_index.visibility IN(?)', $filters['visibility']);
        }

        // Addition: support for filtering multiple categories.
        if (!isset($filters['category_ids'])) {
             $conditions[] = $this->getConnection()
                ->quoteInto('cat_index.category_id=?', $filters['category_id']);
            if (isset($filters['category_is_anchor'])) {
                $conditions[] = $this->getConnection()
                    ->quoteInto('cat_index.is_parent=?', $filters['category_is_anchor']);
            }
        } else {
            $conditions[] = $this->getConnection()->quoteInto('cat_index.category_id IN(' . implode(',', $filters['category_ids']) . ')', "");
        }

        $joinCond = join(' AND ', $conditions);
        $fromPart = $this->getSelect()->getPart(Zend_Db_Select::FROM);
        if (isset($fromPart['cat_index'])) {
            $fromPart['cat_index']['joinCondition'] = $joinCond;
            $this->getSelect()->setPart(Zend_Db_Select::FROM, $fromPart);
        }
        else {
            $this->getSelect()->join(
                array('cat_index' => $this->getTable('catalog/category_product_index')),
                $joinCond,
                array('cat_index_position' => 'position')
            );
        }

        $this->_productLimitationJoinStore();

        Mage::dispatchEvent('catalog_product_collection_apply_limitations_after', array(
            'collection'    => $this
        ));

        return $this;
    }

    protected function _applyZeroStoreProductLimitations()
    {
        $filters = $this->_productLimitationFilters;

        // Addition: supprot for filtering multiple categories.
        $categoryCondition = null;
        if (!isset($filters['category_ids'])) {
            $categoryCondition = $this->getConnection()->quoteInto('cat_pro.category_id=?', $filters['category_id']);
        } else {
            $categoryCondition = $this->getConnection()->quoteInto('cat_pro.category_id IN(' . implode(',', $filters['category_ids']) . ')', "");
        }

        $conditions = array(
            'cat_pro.product_id=e.entity_id',
            $categoryCondition
        );
        $joinCond = join(' AND ', $conditions);

        $fromPart = $this->getSelect()->getPart(Zend_Db_Select::FROM);
        if (isset($fromPart['cat_pro'])) {
            $fromPart['cat_pro']['joinCondition'] = $joinCond;
            $this->getSelect()->setPart(Zend_Db_Select::FROM, $fromPart);
        }
        else {
            $this->getSelect()->join(
                array('cat_pro' => $this->getTable('catalog/category_product')),
                $joinCond,
                array('cat_index_position' => 'position')
            );
        }

        return $this;
    }

It then gets called like this:

$collection = Mage::getModel('catalog/product')->getCollection()
                        ->addAttributeToSelect('*')
                        ->distinct(true) // THIS IS WHAT YOU NEED TO ADD
                        ->addCategoriesFilter($category->getAllChildren(true)); // Make sure you don't forget to retrieve your category here.

HTH

Gorham

Thanks for that. I've added both, and now get an unknown method error. For some reason it's not recognising what's been added, any thoughts? – Politi

Fatal error: Call to undefined method Mage_Catalog_Model_Resource_Product_Collection::addCategoriesFilter().... – Politi

Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Collection.php just doesn't seem to be being loaded at all – Politi

did you actually extend Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Collection? – Gorham

yes, using the almost blank Mage/Catalog/Model/Resource/Eav/Mysql4/Product/Collection.php as a starting point – Politi

I ended up finding an alternative solution by filtering on attribute_set_id instead. I'll mark this correct as it seems to have worked for some in the forum too. – Politi

I'm looking to implement this so I can have filtering by multiple categories on the category page. So I'm on Manufacturer > ManufName page and then I filter by another category e.g. Type > Widgets but I'm unclear where the product collection for category pages get built. Don't suppose you have an idea? TIA. – Gondar

@dangvy, I think you should post this as a question, as it is slightly different, and will need a different solution. – Gorham

Ensure you clear the Magento cache after all this work guys, so that it will properly load the new code. – Gorham

Answer 2

Here's a method that doesn't require modifications to core. It's taken from this post with the addition of the 'group' clause to handle duplicate product records.

$categories = array(7,45,233);

        $collection = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect('*')
            ->joinField('category_id',
                'catalog/category_product',
                'category_id',
                'product_id=entity_id',
                null,
                'left')
            ->addAttributeToFilter('category_id', array('in' => $categories));
        $collection->getSelect()->group('e.entity_id');

Acentric

The join is working but the filtering is not. Nothing is added to the WHERE clause. – Nord

I've tried this solution as well and ran into the same issue as @ButtleButkus ... I see the join is added to the generated query, but no WHERE condition. We're using Magento EE 1.12 (aka CE 1.7). – Renfroe

I've been stepping through the code, and this solution would work when Magento's flat catalog is turned off. When flat catalog is enabled the following occurs. The addAttributeToFilter method recognises that the category_id field is not in the SELECT list, and calls addAttributeToSelect to add it. The addAttributeToSelect method then fails because category_id is not a product attribute, and no filter is added to the collection. – Renfroe

Thanks for explaining the group clause, I was looking for a similar solution but did not understand how it worked. No I do! – Svend

@JimOHalloran I experienced the exact opposite. It only works if flat catalog is turned on. – Rationality

Answer 3

If you want to filter on multiple categories, using AND (so a product must be in categorie A, B and C to show up, you need to have multiple joins:

$products = Mage::getModel('catalog/product')->getCollection()
    ->joinField('category_id_1', 'catalog/category_product', 'category_id', 'product_id=entity_id', null, 'left')
    ->joinField('category_id_2', 'catalog/category_product', 'category_id', 'product_id=entity_id', null, 'left')
    ->addAttributeToFilter('category_id_1', array('eq' => 358))
    ->addAttributeToFilter('category_id_2', array('eq' => 252))
// etc...
;

Hairsplitting

Answer 4

I managed to resolve this (after much trial and error) with the following code:

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToFilter('status', 1);
$collection->addAttributeToSelect(array('name','sku','price','small_image'));

// Filter by multiple categories
$collection->joinField('category_id','catalog/category_product','category_id','product_id=entity_id',null,'left');
$data_cats = $this->getRequest()->getParam('categories');
// Or $data_cats = array(85,86,87,88);

      $filter_cats = array();
      foreach ($data_cats as $value_cats) {
         $filter_cats[] = array(
         'attribute' => 'category_id',
         'finset'    => $value_cats
      );
}

$collection->addAttributeToFilter($filter_cats);

Hope this helps someone ;)

Osteitis

Answer 5

• Magento 1.8.0.0;
• Flat catalog enabled in admin;
• Make sure you've cached the block in which you'll place this;
• Don't add this in paid themes ..

• The inner join hard-coded here reproduces this:

  $collection->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInCatalogIds());

  without 'cat_index.category_id=2'

$category = Mage::getModel('catalog/category')->load(100);
$allChildsIds = $category->getAllChildren($category);

$visibility = Mage::getModel('catalog/product_visibility');

$collection = Mage::getResourceModel('catalog/product_collection');
$collection = $this->_addProductAttributesAndPrices($collection)
  ->addStoreFilter()
  ->setFlag('do_not_use_category_id', true)
  ->setFlag('disable_root_category_filter', true)
  ->addAttributeToSort('created_at', 'desc');

$whereCategoryCondition = $collection->getConnection()
  ->quoteInto('cat_index.category_id IN(?) ', $allChildsIds);
$collection->getSelect()->where($whereCategoryCondition);

$conditions = array();
$conditions[] = "cat_index.product_id = e.entity_id";
$conditions[] = $collection->getConnection()
  ->quoteInto('cat_index.store_id = ? ', Mage::app()->getStore()->getStoreId());
$conditions[] = $collection->getConnection()
  ->quoteInto('cat_index.visibility IN(?) ', $visibility->getVisibleInCatalogIds());

$collection->getSelect()->join(
  array('cat_index' => $collection->getTable('catalog/category_product_index')),
  join(' AND ', $conditions),
  array()
);

$collection
  ->setPageSize(3)
  ->setCurPage(1);

$collection->load();

Fuqua

Answer 6

Filter Product Collection using multiple category ids

$all_categories = array('3','13','113');   
$productCollection = Mage::getModel('catalog/product')->getCollection();
$productCollection->joinField('category_id', 'catalog/category_product', 'category_id', 
                    'product_id = entity_id', null, 'left')
                  ->addAttributeToSelect('*')
                  ->addAttributeToFilter('type_id', array('eq' => 'simple'))
                  ->addAttributeToFilter('category_id', array($all_categories));
foreach($productCollection as $product)
{
    echo $product->getId() .$product->getName() . "<br/>";
}

You can remove the condition for product type i.e type_id or modify it as per requirement.

Mclin

Answer 7

Reference: https://www.fmeextensions.com/blog/get-product-collection-by-category-id-magento-2/

$ids = [1,2,3,4,5,6,7];
$collectionFactory = $objectManager->get('\Magento\Catalog\Model\ResourceModel\Product\CollectionFactory')->create();
$products = $collectionFactory->addAttributeToSelect('*')
                            ->addCategoriesFilter(['in' => $ids]);

foreach ($products as $product) {
    echo $product->getId() . "<br />";
    echo $product->getName() . "<br />";
    echo $product->getProductUrl() . "<br />"; 

}

Seton