1

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.

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.

  • Hi, thanks for helping. Your new updated code, the category_filter seems not to work but what you had previously does and I can query for multiple nin. With your new code, I get an error "Call to a member function getBackend()". – Mr Portal Jun 9 '16 at 4:30
  • Fair call, here's me trying to do it the "right" way. I'll revert that edit. – Robbie Averill Jun 9 '16 at 4:32
  • $collection->getSelect()->where("at_category_we_dont_want.category_id IS NULL"); Here's the previous working code for me. – Mr Portal Jun 9 '16 at 5:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.