CODEWITHSUNDEEP
magentocollections
Alex Hadley
Alex Hadley

Reputation: 2125

Magento filter product collection by multiple categories

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

Upvotes: 6

Views: 27102

Answers (7)

Amir Khan
Amir Khan

Reputation: 221

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 />"; 

}

Upvotes: 0

ItsJhonny
ItsJhonny

Reputation: 538

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

Upvotes: 2

Ahmad Vaqas Khan
Ahmad Vaqas Khan

Reputation: 658

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.

Upvotes: 0

Laizer
Laizer

Reputation: 6150

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

Upvotes: 9

Daniel Ifrim
Daniel Ifrim

Reputation: 287

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

Upvotes: 0

Giel Berkers
Giel Berkers

Reputation: 2960

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

Upvotes: 3

ShaunOReilly
ShaunOReilly

Reputation: 2206

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

Upvotes: 4

Related Questions