Mini Tutorial: How to Quickly Update Product Categories in Magento

October 22, 2012 by Allen

Recently, I was working on a scheduled task for a client to update product categories in Magento depending on certain conditions. The usual way to update a product is with the following code:

foreach($products as $product)
{
    $categoryIds = $product->getCategoryIds();
     if(!in_array($catId, $categoryIds)){
        $categoryIds[] = $catId;
        $product->setCategoryIds($categoryIds);
        $product->save();
    }
}

Normally, you get the collection, update the products while looping through it. But when the collection is over a thousand products, this becomes very inefficient.  The average time it takes to save a product this way usually takes 1-2 seconds on my workstation. However, execution time can add up very quickly.

Then… I saw someone who was using a direct SQL call to update the product category’s position. Hm… definitely a good idea to bypass all overheads, even though it’s somewhat “hacky”.

Here is what I came up after experimenting with it a bit:

$resource = Mage::getSingleton('core/resource');
$write = $resource->getConnection('core_write');

try
{
  foreach($products as $product)
  {
     $categoryIds = $product->getCategoryIds();
     if(!in_array($cat_id, $categoryIds)){
        $write->query("replace into
        `catalog_category_product` (category_id,product_id,position)
        VALUES (?,?,0)",array($cat_id,$product->getEntityId()));
    }
  }
}
catch(Exception $e) {
     Mage::log(print_r($e,true), null, 'exception.log');
}

The products showed up on the correct category after it ran and nothing seemed broken after running the indexer. It took a fraction of the run time when comparing to the original method. I think it’s working well. :) Try it out for yourself!

About Allen

Certified Magento Developer

Read more posts by Allen