In a custom module in Magento 1.9 I was associating products to categories using the following method:

public function addProductToCategory($productId, $categoryId)
    $category = Mage::getModel('catalog/category')

    $productsInCategory = $category->getProductsPosition();
    $productsInCategory[$productId] = 1;

But, as you can see above, I forgot to check if the category actually exists before adding the product to it (using if ($category->getId()) or similar).

This led me to some inconsistency at my database, where some categories were created with blank attributes and parent_id equals to 0 at the table catalog_category_entity (because the $categoryId was invalid).

My question is: can I revert it by selecting all categories associated to at least one product (joining the table catalog_category_entity with catalog_category_product) and deleting all categories with parent_id equals to 0? Something like:

$categoryProductTable = Mage::getSingleton("core/resource")->getTableName("catalog/category_product");
$collection = Mage::getModel("catalog/category")->getCollection();
    ->join(array('p' => $categoryProductTable), 'p.category_id = e.entity_id')
    ->where("e.parent_id = 0")

Where the SQL would be:

SELECT `e`.*, `p`.* FROM `catalog_category_entity` AS `e`
INNER JOIN `catalog_category_product` AS `p` ON p.category_id = e.entity_id 
WHERE (`e`.`entity_type_id` = '3') AND (e.parent_id = 0) 
GROUP BY `e`.`entity_id`;

I've tested the query above and it seems right, but I'm afraid to be missing something. Any other ideas to solve the problem? Thanks in advance!

1 Answer 1


Take the backup of your database before you run your DELETE query.

After you run your SQL to remove the wrong records, reindex the store at first.

Then just use this code in your custom module method to assign the categories to products or in other words associate categories to a product, which keeps things much easier rather than the one that you use now.

 $_product = Mage::getSingleton('catalog/product')->load($productId);
 /* @var Magento_Catalog_Model_Product */

to get the list of categories a product has been assigned to

just use

$_product->getCategoryIds(); // returns the list of category Ids the product has been assigned to 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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