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.

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
3
  • 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()". Commented Jun 9, 2016 at 4:30
  • Fair call, here's me trying to do it the "right" way. I'll revert that edit. Commented Jun 9, 2016 at 4:32
  • $collection->getSelect()->where("at_category_we_dont_want.category_id IS NULL"); Here's the previous working code for me. Commented Jun 9, 2016 at 5:44

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.