I have a rather specific problem regarding product collections and excluding multiple categories. The following code example does almost exactly what I want to achieve: getting a product collection with the most sold products and excluding all categories that contain the word "free". There is only 1 problem: I can only filter on 1 product category, since products can have multiple categories there are still products shown that had a category containing the word free. Is there a way the filter away all products that have at least 1 category containing the word free?

        $freeCategories = array();
        $rootId = Mage::app()->getStore($storeId)->getRootCategoryId();
        $categories = Mage::getModel('catalog/category')
            ->addFieldToFilter('path', array('like'=> "1/$rootId/%"))
            ->addFieldToFilter('name', array('like' => '%free%'));

        foreach($categories as $category){
            $freeCategories[] = $category->getId();

        $storeId = Mage::app()->getStore()->getId();
        $products = Mage::getResourceModel('reports/product_collection')
            ->setOrder('ordered_qty', 'desc') //best sellers on top*/
            ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
            ->addAttributeToFilter('category_id', array('nin' => array('finset' => $freeCategories)));


1 Answer 1


I have had this issue before, and I solved it by working the problem the other way round.

First get a list of product ids, of products in the given categories, and then exclude those products :)

The following code is a snippet form my Dynamic Category Products Extension, where a rule can load all products not in a category (exactly what you are doing ;) )

$this->getValue() contained an array of category ids (which you get from your initial collection already)

Please ask if you need any clarification on the code.

                $value = explode(',',$this->getValue());
                // since a product can appear in multiple categories,
                // we must eliminate via the product ids of any
                // products that has the given category id in it.
                $subCollection = mage::getModel('catalog/product')

                        'in' => $value)
                // remove all columns, and only get the entity_id
                // so it will work as a subquery for IN/NIN
                $subSelect = $subCollection->getSelect();
                $newColumns = array(
                    '0' => array(
                $this->getHelper()->debug("Category ID subquery:" . $subSelect);
                    'e.entity_id NOT IN (?)',
                    new Zend_Db_Expr($subSelect->__toString())

Also note that you do not need to do a foreach on your category colelction. Simpy do: $category->getAllIds(), which will be much more efficient.

Hope this helps.

  • Thank you, this really helped me on the right track. Worked in my situation with some minor modifications
    – kevin
    Feb 3, 2016 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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