Excluding products from multiple categories in collection

Question

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')
            ->getCollection()
            ->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')
            ->addOrderedQty()
            ->addAttributeToSelect('*')
            ->setStoreId($storeId)
            ->addStoreFilter($storeId)
            ->setOrder('ordered_qty', 'desc') //best sellers on top*/
            ->setPageSize($amount)
            ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
            ->addAttributeToFilter('category_id', array('nin' => array('finset' => $freeCategories)));

        Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($products);
        Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($products);
        Mage::getSingleton('cataloginventory/stock')->addInStockFilterToCollection($products);

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')
                    ->getCollection();
                $subCollection->joinField(
                        'category_id',
                        'catalog/category_product',
                        'category_id',
                        'product_id=entity_id',
                        null,
                        'left'
                );

                $subCollection->addAttributeToFilter(
                    'category_id',
                    array(
                        'in' => $value)
                );
                // remove all columns, and only get the entity_id
                // so it will work as a subquery for IN/NIN
                $subSelect = $subCollection->getSelect();
                $subSelect->reset(Zend_Db_Select::COLUMNS);
                $newColumns = array(
                    '0' => array(
                        'e',
                        'entity_id',
                        null));
                $subSelect->setPart(
                    Zend_Db_Select::COLUMNS,
                    $newColumns
                );
                $this->getHelper()->debug("Category ID subquery:" . $subSelect);
                $collection->getSelect()->where(
                    '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.