I want to get product ids of a specific category and product ids not in other categories only in a specific category.

For example,

category ids = 3, 4, 10 and product ids = 10, 12, 14 and category id 3 belong to product id 10 and category id 4 belong to product ids 10, 12, 14 and category id 10 belong to product id 14.

When I filter on category id = 4, it returns product ids 10, 12 and 14, but I want to return only product id 12 because I only want the product id which is not in other categories. In my filter category id 4 returns product ids 10,12 and 14 but I only want to product id 4 because 10 and 14 belong to other categories which I don't want. I only want to product id 12 in filter. Which filter I used ?

$catIds = array(4);
$notCatIds = array(3, 10);
$productCollection = Mage::getResourceModel('catalog/product_collection')
    ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id=entity_id', null, 'left')
    ->addAttributeToFilter('category_id', array('in' => $notCatIds))
    ->addAttributeToFilter('category_id', array('in' => $catIds))
    ->addAttributeToFilter('category_id', array('nin' => $notCatIds))
    ->addAttributeToFilter('category_id', array('nin' => array(2)))// 2 is default category id

echo '<pre>';
  • Did you solve??
    – Gem
    Apr 23, 2018 at 4:20

2 Answers 2


Step1: all categories ids form Category and then

$categiesIDs= Mage::getResourceModel('catalog/category_collection')->getAllIds();

Step2: exclude your category[12] from $categiesIDs uset

Step3: Then Product collection filter by Categories ids.

        array('at_category_id' => $productCollection->getTable('catalog/category_product')),
            'cattotal'=> new Zend_Db_Expr('COUNT(*)')
  • 12 is product id not category id. what you say i dont understand ? please eleborate
    – john
    Feb 18, 2015 at 18:23
  • 10,12 and 14 are product ids which belongs to category id (4) and category id (3) belongs to product id(10) and category id 10 belongs to product id(14).Now My issue is when i filter using category id(4), it returns 10,12,14 product ids but i only want to product id 12 because product id(12) is not belongs to other categories
    – john
    Feb 18, 2015 at 18:29
  • just wait .. you want to this type of work.get product collection filter which have only category
    – Amit Bera
    Feb 18, 2015 at 18:39
  • yes something like this. In sort not common product ids not in filter get back only. Example men and women are categories, product ids (10,12,14).men contains 10 and 14 product ids and women category contain 10,12,14 product ids. when i filter women category,i only want product id(12) because product id(12) not contain in men category.
    – john
    Feb 18, 2015 at 18:45
  • i don't want to 10 and 14 product ids because it belongs to other(men) category in filter.
    – john
    Feb 18, 2015 at 18:47

The below query will give some insights to you.

From your scenario, one must say you need to list the products belongs to a single category. So below query will satisfy your requirements.

It's a bit of raw if you can rewrite Magento way better.

Note: The catlist field added to see what are all the categories attached to a product in a comma separated way.

$catIds = array(4);
$productCollection = Mage::getResourceModel('catalog/product_collection');
    array('at_category_id' => $productCollection->getTable('catalog/category_product')),
        'cattotal'=> new Zend_Db_Expr('(SELECT COUNT(product_id) FROM '.$productCollection->getTable('catalog/category_product').' WHERE product_id=e.entity_id AND category_id NOT IN ('.implode(",", $catIds).'))'),
        'catlist'=> new Zend_Db_Expr('GROUP_CONCAT(at_category_id.category_id)')
->where("at_category_id.category_id IN (".implode(",",$catIds).")")

Adapting some elements from Amit Bera's answer.

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.