9

Here is my code:

$catIds = array(7,8,9);
$collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect("*");
    ->addAttributeToFilter('category_ids', array('nin' => $catIds));

I want to get all products not in list of category ids but my code didn't give the expected result. Please show me the way, thanks.

  • what was the result you're expecting vs the results you got? – ahnbizcad Apr 14 '17 at 21:38
15

You need to join the table that holds the category/product relations.

A variation of the collection I use to find all products IN a list of categories should do the trick for you:

(untested, but should get you in the right track)

$productCollection = Mage::getResourceModel('catalog/product_collection')
    ->setStoreId(0)
    ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id=entity_id', null, 'left')
    ->addAttributeToFilter('category_id', array('nin' => $catIds))
    ->addAttributeToSelect('*');

$productCollection->getSelect()->group('product_id')->distinct(true);
$productCollection->load();

ref: http://www.proxiblue.com.au/blog/Collection_of_products_in_all_child_categories/

  • 1
    This doesn't appear to work if a product appears in more than one category. The 'nin' simply excludes that one row, but the product ID will still come up for the other categories. – greatwitenorth Dec 5 '16 at 22:09
  • Hi, As the code stands, it works 100%. The code written will exclude the product as per the given category list in the variable $catIds. If the product appears in more than one category, and that category is not included in the exclusion list, well, it will appear. The fault then is that you are not exclusing all the categories. The code cannot magically (as it is written) know what other categories the product appears in> – ProxiBlue Dec 6 '16 at 0:03
  • Sound more like you are wanting a means to exclude by product ids, which you can easily derive form the above code. The first tep would be to swap to IN, then get the product Ids resulting form this query, and then use that in a new collection for products, excluding the given products by id. Even better is to make it a subquery of teh product collection that is excluding by product ids. – ProxiBlue Dec 6 '16 at 0:29
5

Following code will work for you:

$catIds = array(7,8,9);
$_productCollection = Mage::getModel('catalog/product')
                ->getCollection()
                ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
                ->addAttributeToFilter('category_id', array('nin' => array('finset' => $catIds)))
                ->addAttributeToSelect('*');
1

I've found a somewhat better way of doing this, using an anti-join (Magento 1.9).

Benefits of this approach

The benefit of this over the original answer is that you won't get false positives, and it is faster and less error-prone as a result. For example, suppose you have a single product:

  1. Shirt (in categories: 1 | 2)

You want to "find all products not in category 3, then add them to category 3". So you run a NOT IN query, and it'll return two rows (name | category_id):

1. "Shirt" | 1
2. "Shirt" | 2

No big deal, Magento will still only return the first result, and then you add it. Except! The second time this query gets run, you have the same results:

1. "Shirt" | 1
2. "Shirt" | 2

And Magento will tell you that you still haven't added this shirt to category 3. This is because when a product belongs to multiple categories, they'll have multiple rows in the "catalog_product_entity" table. And so a LEFT JOIN will return multiple results.

This is undesirable because

  1. Your result set will be larger than needed, which will use up more memory than necessary. Especially so if you have a very large inventory of thousands of items.
  2. You will need to do an additional check in PHP to determine if the results are false positives (e.g., in_array($categoryThree, $product->getCategories())), meaning you'll loop through unnecessary results. This will make your script/code slower, especially with large inventories.

Solution

// All products not in this category ID
$notInThisCatId = '123';

$filteredProducts = Mage::getModel('catalog/product')->getCollection();
$filteredProducts
    ->joinField('category_id', 'catalog_category_product', 'category_id', 'product_id=entity_id', ['category_id'=>$notInThisCatId], 'left');
$filteredProducts
    ->addAttributeToFilter('category_id', [
        ['null' => true]
    ]);

The SQL query generated will look like:

SELECT 
    DISTINCT `e`.*, `at_category_id`.`category_id` 
FROM `catalog_product_entity` AS `e`
LEFT JOIN `catalog_category_product` AS `at_category_id` 
    ON (at_category_id.`product_id`=e.entity_id) AND (at_category_id.category_id = '123')
WHERE (at_category_id.category_id IS NULL)
GROUP BY `e`.`entity_id`;

Explanation:

Given the product and product<=>category relationship tables:

catalog_product_entity +-----------+ | ENTITY_ID | +-----------+ | 423 | | 424 | | 425 | +-----------+

catalog_category_product +-------------+------------+ | CATEGORY_ID | PRODUCT_ID | +-------------+------------+ | 3 | 423 | | 123 | 424 | | 3 | 425 | +-------------+------------+

Your query is saying, "give me all rows in "catalog_product_entity", and paste on the "category_id" column from "catalog_category_product". Then just give me the rows that category_id = 124".

Because it's a left join, it'll always have the rows from "catalog_product_entity". For any rows that can't be matched up, it'll be NULL:

Result +-------------+-------------+ | ENTITY_ID | CATEGORY_ID | +-------------+-------------+ | 423 | NULL | | 424 | 123 | | 425 | NULL | +-------------+-------------+

From there, the query then says, "ok, now give me everything where the category_id is NULL".

1

Not as easy as it may look.

Here is the GROUP_CONCAT -based option as it's default limit (1024 but could be increased of course) should be okay with product category IDs separated by commas set.

$categoryIdsToExclude = array(1, 2, 4); // Category IDs products should not be in

$collection = Mage::getModel('catalog/product')->getCollection();

$selectCategories = $collection->getConnection()->select();
$selectCategories->from($collection->getTable('catalog/category_product'), array('product_id', 'category_id'));
$mysqlHelper = Mage::getResourceHelper('core');
$mysqlHelper->addGroupConcatColumn(
    $selectCategories,
    'category_ids_set',
    'category_id',
    ','
);
$selectCategories->group('product_id');

$collection->getSelect()->joinLeft(
    array('category_ids_set_table' => new Zend_Db_Expr('(' . $selectCategories->__toString() . ')')),
    'category_ids_set_table.product_id = e.entity_id',
    array('category_ids_set' => 'category_ids_set_table.category_ids_set')
);

foreach ($categoryIdsToExclude as $val) {
    $collection->getSelect()->where('NOT FIND_IN_SET(?, category_ids_set)', $val);
}

Also (if you don't like GROUP_CONCAT) you may use WHERE product_id NOT IN a subquery of a product IDs are actually IN categories you need to exclude (not giving it here).

Anti-join approach from another answer will also work. But in this case you can't easily add additional conditions.

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.