I am currently trying to run a productCollection to try and get a list of products that are not assigned to one or more categories.

$collection = Mage::getResourceModel('catalog/product_collection');
$catId = [46]; // category id to exclude
$collection->getSelect()->join(array('cats' => 'catalog_category_product'), 'cats.product_id =         e.entity_id'); // Join with category on product/entity id
$collection->getSelect()->where('cats.category_id', array('nin' => array('finset' => $catId))); // exclude category from collection

echo '<pre>';
echo $collection->getSelect(); // See sql query

This is what I currently have however it doesn't exclude the fact that these products may be in a child category of the id provided.

Any help would be greatly appreciated,

1 Answer 1


You have to also join the category_entity table and then use the path field to filter categories.

To do so, it is easier to left join categories that match your exclude criteria and then filter your collection to only return products with no matches in the joined table.

Example code (untested):

$excludeId = 46;
    ['exclude_category_product' => 'catalog/category_product'],
    ['exclude_category' => 'catalog/category'],
    ['path' => [
        ['like' => '%/'.$excludeId],
        ['like' => '%/'.$excludeId.'/%']
$collection->getSelect()->where(new Zend_Db_Expr('exclude_category.entity_id IS NULL'));

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.