Exclude products with category id - productCollection

Question

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
print_r($collection->getData());

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,

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;
$collection->joinTable(
    ['exclude_category_product' => 'catalog/category_product'],
    'product_id=entity_id',
    ['category_id'],
    null,
    'left'
);
$collection->joinTable(
    ['exclude_category' => 'catalog/category'],
    'entity_id=category_id',
    ['entity_id'],
    ['path' => [
        ['like' => '%/'.$excludeId],
        ['like' => '%/'.$excludeId.'/%']
    ]],
    'left'
);
$collection->getSelect()->where(new Zend_Db_Expr('exclude_category.entity_id IS NULL'));