Filter product collection by multiple categories?

Question

I have this snippet of code that gets the most recently added products:

$_productCollection = Mage::getResourceModel('catalog/product_collection')
                ->addAttributeToSelect('*')
                ->addAttributeToFilter($preorderAttribute, array(
                    'eq' => Mage::getResourceModel('catalog/product')
                        ->getAttribute($preorderAttribute)
                        ->getSource()
                        ->getOptionId($preorderValue)
                ))
                ->setVisibility(array(2,3,4))
                ->setOrder('created_at', 'desc')
                ->setPage(1, 12);

I want to further filter this by category, for example, categories with id 3 and 4. Only products from 3 and 4 categories are selected in the collection. How can I achieve this?

I tried to use addAttributeToFilter to filter by category IDs but there seems to be a variety of different ways to do it, and it's neither not working or simply gives a Fatal error:

Call to a non-member function getBackend()

....now I'm at a loss.

Any help would be appreciated.

Answer 1

The following code example works for me to show only particular two categories (7 and 8) products. Hope it will work for you:

$productCollection = Mage::getModel('catalog/product')
    ->getCollection()
    ->joinField(
        'category_id', 'catalog/category_product', 'category_id', 
        'product_id = entity_id', null, 'left'
    )
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('category_id', array(
            array('finset' => array('7', '8', )),
    ))
    ->addAttributeToSort('created_at', 'desc');

foreach($productCollection as $product){
    echo $product->getId() . "<br/>\n";
};

Answer 2

Another alternative:

In the example, $categoryIds is an array of category Ids. (I usually use $collection->getFlag('category_ids') but changed for this example, see explanantion lower down)

$currentStoreId is populated by any means that can get the current store id. (in my observer event I use $collection->getFlag('store_id'))

$conditions = array(
    'cat_index.product_id=e.entity_id',
    $collection->getConnection()->quoteInto('cat_index.category_id IN (' . $categoryIds . ')', "")
);
if (!Mage::app()->getStore()->isAdmin()) {
    $conditions[] = $collection->getConnection()->quoteInto('cat_index.store_id=?', $currentStoreId );
}
$joinCond = join(' AND ', $conditions);
$fromPart = $collection->getSelect()->getPart(Zend_Db_Select::FROM);

if (isset($fromPart['cat_index'])) {
    $fromPart['cat_index']['joinCondition'] = $joinCond;
    $collection->getSelect()->setPart(Zend_Db_Select::FROM, $fromPart);
} else {
    $collection->getSelect()->join(
            array('cat_index' => $collection->getTable('catalog/category_product_index')), $joinCond, array('cat_index_category_id' => 'category_id')
    );
}

I use the above code in an observer to filter with multiple categories AFTER the built in call Mage_Catalog_Model_Resource_Product_Collection::_applyProductLimitations() does it's thing, as it sends an event called 'catalog_product_collection_apply_limitations_after'

The _applyProductLimitations method essentially applies the filters set by the call to addCategoryFilter.

By using a flag in the $collection, it is an easy matter to store the multiple category ids for the observer.

Thus whenever I have the need to filter by multiple categories, I set a flag on the collection at some point

an example would be to rewrite Mage_Catalog_Model_Category::getProductCollection()

public function getProductCollection() {
    $collection = parent::getProductCollection();
    $collection->setFlag('category_ids',array(19,243,42,54)); //the array of values can be from somehwre, this hard coded array serves purely as an example
    return $collection;    
}

Now, when magento fires the event, my code replaces the category filter with my multiple filter :)

This is essentially how my Dynamic Category Products module does its thing for its multiple category filters.

Ref The core code of addCategoryFilter:

/**
 * Specify category filter for product collection
 *
 * @param Mage_Catalog_Model_Category $category
 * @return Mage_Catalog_Model_Resource_Product_Collection
 */
public function addCategoryFilter(Mage_Catalog_Model_Category $category)
{
    $this->_productLimitationFilters['category_id'] = $category->getId();
    if ($category->getIsAnchor()) {
        unset($this->_productLimitationFilters['category_is_anchor']);
    } else {
        $this->_productLimitationFilters['category_is_anchor'] = 1;
    }

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

    return $this;
}

Answer 3

I worked with the techniques offered in both @monojit banik and @ProxiBlue's answers, and got the "Item (Mage_Catalog_Model_Product) with the same id "xx" already exist" error identified in @MTM's comment with both.

To avoid the error, which occurs outside the collection load in the layered navigation, I tried a couple of different methods to get a distinct set of products in a single pass, but nothing I came up with worked.

Ultimately, (EDIT: based on this blog post, https://www.muddyweb.com/general-ramblings/filtering-a-magento-product-collection-by-multiple-categories/) I ended up using two separate collection loads to get the distinct product set and avoid the error as follows. There may be a performance penalty to using two passes, but nothing was noticeable.

    $category_ids = [helper method to get desired category id array];

    //Need distinct products even if products are assigned to more than one of 
    //  the selected categories.
    $id_collection = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('id')
        ->addAttributeToFilter('status', 1)
        ->addAttributeToFilter('visibility', 4)
        ->addStoreFilter();

    $conditions = array();
    foreach ($category_ids as $categoryId) {
        if (is_numeric($categoryId)) {
            $conditions[] = "{{table}}.category_id = $categoryId";
        }
    }

    $id_collection->distinct(true)
        ->joinField('category_id', 'catalog/category_product', null,
                    'product_id = entity_id', implode(" OR ", $conditions), 'inner');

    $ids = $id_collection->getAllIds();

    //Now, use the product ids array obtained above to get the final product collection.
    /** @var Mage_Catalog_Model_Resource_Product_Collection $collection */
    $collection = Mage::getResourceModel('catalog/product_collection')
        ->setStoreId($this->getStoreId())
        ->addAttributeToSelect('*')
        ->addAttributeToFilter('entity_id', array('in' => $ids) )
    ;

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

Answer 5

for error: 'invalid attribut entity_id' don't make space between product_id = entity_id !! the good syntax is:

 ->joinField(
    'category_id', 'catalog/category_product', 'category_id', 
    'product_id=entity_id', null, 'left'
)