Magento Product Collection exclude category

Question

I need to exclude a category from my productCollection but I dont know how to achieve this.

Requirements:

• Only visible products
• Ordered by: created_at DESC
• Exclude category id 43
• Limit 4 (products)

To retrieve my collection I use the following code:

$collection = Mage::getResourceModel('catalog/product_collection')
                ->setVisibility(Mage::getSingleton('catalog/product_visibility')->getVisibleInCatalogIds());



$collection =  $this->_addProductAttributesAndPrices($collection)
                    ->addAttributeToSort('created_at', 'DESC')
                    ->setPageSize(4)
                    ->setCurPage(1);

This works fine, but I can't add this filter:

->addFieldToFilter('category_id', array('nin' => array('43')))

---

I found these similar questions, but they wont solve my problem.

How to exclude a category from a magento getCollection

Magento - How do I exclude a category from product collection?

Answer 1

Try below code:

$collection = Mage::getResourceModel('catalog/product_collection');         
$collection->addAttributeToFilter('status', 1); // Enable products
$collection->addAttributeToFilter('visibility', 4); // Visible products 
$collection->addAttributeToSort('created_at', 'DESC') // Order by created_at DESC
           ->setPageSize(4) // Number of products
           ->setCurPage(1); // set page size
$catId = 43; // category id to exclude
$collection->getSelect()->join(array('cats' => 'catalog_category_product'), 'cats.product_id = e.entity_id'); // Join with category on product/entiry id
$collection->getSelect()->where('cats.category_id!=?',$catId); // exclude category from collection

echo '<pre>';
echo $collection->getSelect(); // See sql query
print_r($collection->getData()); Print collection in array format

Hope will help!

Answer 2

Expanding on Rajiv's answer as Martijn didn't include the group by, a full working example is:

$collection = Mage::getResourceModel('catalog/product_collection');         
$collection->addAttributeToFilter('status', 1); // Enable products
$collection->addAttributeToFilter('visibility', 4); // Visible products 
$collection->addAttributeToSort('created_at', 'DESC') // Order by created_at DESC
       ->setPageSize(4) // Number of products
       ->setCurPage(1); // set page size
$catId = 43; // category id to exclude
$collection->getSelect()->join(array('cats' => 'catalog_category_product'), 'cats.product_id =         e.entity_id'); // Join with category on product/entiry id
$collection->getSelect()->where('cats.category_id!=?',$catId); // exclude category from collection
$collection->getSelect()->group(array('e.entity_id')) // Group by e.entity_id to prevent Item with the same id already exists.. Exception

echo '<pre>';
echo $collection->getSelect(); // See sql query
print_r($collection->getData()); Print collection in array format