How to get all products that aren't in a specific category in Magento?

Question

I'm having issue trying to get all the products that aren't in a specific category. Here's a code that works fine until I added the 'join-nin' query.

$collection = Mage::getModel('catalog/product')
                    ->getCollection()
                    ->addAttributeToSelect('*')
                    ->addAttributeToSort('created_at', 'desc')
                    ->addAttributeToFilter(
                        'status',
                        ['eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED])
                    ->addAttributeToFilter('visibility', [ 'in' =>[ 2, 4 ] ]);

Here's my 'join-nin'

$collection = $collection->joinField('category_xxx', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
                         ->addAttributeToFilter('category_xxx', ['nin'=>24])

Now I know that currently, it would give me duplicate products (result) for some reason so I have to group the result by 'e.entity_id'

$collection->getSelect()->group('e.entity_id');

Then when I looped through the collection and print/echo their Ids, I see the product which I specifically assigned to that category (24).

TLDR: I need to query all products with using 'in' and 'nin' for any categories.

Answer 1

This is a bit of a hack, but it looks like it's working for me. I'll try and come up with a better filter method, but for now try this:

Join the field, specifying the category ID you don't want as one of the left join conditions. This way, you can highlight within the query which products do exist in that category, then filter against that:

$collection = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToSort('created_at', 'desc')
    ->addAttributeToFilter(
        'status',
        ['eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED]
    )
    ->addAttributeToFilter('visibility', ['in' => [2, 4]])
    ->joinField(
        'category_we_dont_want',
        'catalog/category_product',
        'category_id',
        'product_id = entity_id',
        ['category_id' => 24],
        'left'
    );

Then, filter by products that didn't meet that join condition. In a left join, if the condition category_id => 24 isn't met the field value will just be null:

$collection->getSelect()->where('at_category_we_dont_want.category_id IS NULL');

This should give you products in your collection that don't exist in category 24.