CODEWITHSUNDEEP
magentofiltering
eduardev
eduardev

Reputation: 483

How to exclude a category from a magento getCollection

this might be a silly question, but well. I have the following code that retrieves all the products on my shop.

$products = Mage::getModel('catalog/product')->getCollection();
$products->addAttributeToFilter('status', 1);
$products->addAttributeToFilter('visibility', 4);
$products->addAttributeToFilter('type_id', 'simple');
$products->addAttributeToSelect('*');
$products->addStoreFilter($storeId);
$prodIds = $products->getAllIds();

Im aware of the:

$category = Mage::getModel('catalog/category')->load(9);
$products->addCategoryFilter($category);

to filter by a category ID, but how to get all products except one specific category ID ? (Magento 1.6.2)

Upvotes: 2

Views: 4279

Answers (3)

Darren Felton
Darren Felton

Reputation: 2299

Here is the logic I came up with to resolve this problem.

Note: Should work in Magento 1.X

  1. It dynamically pulls table names so that it would work in any installation.
  2. It validates that it is a correct catalog/category entity ID.
  3. It uses a unique table alias by category ID when performing the MySQL LEFT JOIN. This allows us to exclude the collection from multiple category IDs.

  4. It validates that we do not attempt to exclude the collection from the same category id twice.

    public function addCategoryExclusionFilter(Mage_Catalog_Model_Resource_Product_Collection $collection, $category_id)
{
    /* @var $resource Mage_Core_Model_Resource */
    /* @var $category Mage_Catalog_Model_Category */
    $resource = Mage::getModel('core/resource');
    $category = Mage::getModel('catalog/category')->load($category_id);
    $select   = $collection->getSelect();
    $tblccp   = $resource->getTableName('catalog_category_product');
    $tblAlias = $tblccp.'_'.$category_id;

    if (! $category->getId()) {
        Mage::throwException("Invalid `{$resource->getTableName('catalog/category')}`.`entity_id` value ({$category_id}).");
    }

    if (strpos($select->__toString(), $tblAlias) !== false) {
        Mage::throwException("Category (ID: {$category->getId()}) already excluded from collection");
    }

    $select->joinLeft(array($tblAlias => $tblccp), "(`{$tblAlias}`.`product_id` = `e`.`entity_id` AND `{$tblAlias}`.`category_id` = '{$category->getId()}')", array());
    $select->where("`{$tblAlias}`.`category_id` IS NULL");
}

Example of the MySQL query afterwards:

SELECT 
    `e`.* 
FROM
    `catalog_product_entity` AS `e`
LEFT JOIN
    `catalog_category_product` AS `catalog_category_product_28` ON (
        `catalog_category_product_28`.`product_id` = `e`.`entity_id` AND
        `catalog_category_product_28`.`category_id` = '28'
    ) 
WHERE 
    (`catalog_category_product_28`.`category_id` IS NULL)

What we are doing here is performing a join on the table which holds the relationship between product entities and category entities, BUT only where the record's category_id is equal to the category id which we are looking to exclude. This is important because the catalog_product_entity table has a 1:M relationship to the catalog_category_product table (at the time of posting this answer, I do not see the other answers here addressing this). Then, we add a WHERE declaration that we only want to select records where the category_id column for the joined table IS NULL (because there exists no record in the joined table, for product entities which we wish to select).

Upvotes: 0

joseantgv
joseantgv

Reputation: 2023

This is what I used:

$_productCollection = Mage::getModel('catalog/product')
        ->getCollection()
        ->addAttributeToSelect('*')
        ->addUrlRewrite();

Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($_productCollection);
Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($_productCollection);

$_productCollection->load();
$_productCollection->getSelect()->join(array('cats' => 'catalog_category_product'), 'cats.product_id = e.entity_id');
$_productCollection->getSelect()->where('cats.category_id not in (41)');

Upvotes: 1

wierdo
wierdo

Reputation: 426

I think this should work, presuming you know what category ID you want to filter out, but I can't test it right now

$catId = 9;

/* I'm almost positive 'e' is the alias used for catalog_product_entity, check your
   query with echo (string) $products->getSelect(); if it doesn't work */
$products->getSelect()->join(array('cats' => 'catalog_category_product'), 'cats.product_id = e.entity_id');
$products->getSelect()->where('cats.category_id', array('neq' => $catId));

Upvotes: 3

Related Questions