7

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.

22

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";
};
  • 3
    +1 Perfect. BTW we can also use 'in' in place of 'finset'. So it looks like below $_productCollection->addAttributeToFilter('category_id', array('in' => array('106','107','108','110'))) – Haijerome Apr 3 '14 at 5:56
  • 2
    This code doesn't work when there are two or more categories to filter and any product is assigned to multiple of these categories. Returns error as Item (Mage_Catalog_Model_Product) with the same id "xx" already exist. – MTM Oct 4 '14 at 17:10
  • 1
    You can handle the case where there are products in multiple categories by grouping by product $productCollection->getSelect()->group('e.entity_id'); Just remember that some functionality such as collection counts is thrown off by adding grouping. – Robert Egginton Feb 10 '16 at 12:16
  • Also, the code above will not work with older versions of Magento where whitespace was not stripped in the join's bind variable, so the condition needs to be 'product_id=entity_id' - note the lack of spaces. – Robert Egginton Feb 10 '16 at 12:17
  • No need to group if you use "in" as @Haijerom said Edit: actually you'll still need as you can have several times the same product apprearing – Erdal G. Nov 22 '16 at 17:08
4

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;
}
2

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) )
    ;
  • Works like a charm. Good method – Erdal G. Nov 22 '16 at 17:19
0

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

-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.