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')
->setStoreId(Mage::app()->getStore()->getId())
->load($categoryId);
$productsInCategory = $category->getProductsPosition();
$productsInCategory[$productId] = 1;
$category->setPostedProducts($productsInCategory);
$category->save();
}
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();
$collection->getSelect()
->join(array('p' => $categoryProductTable), 'p.category_id = e.entity_id')
->where("e.parent_id = 0")
->group("e.entity_id");
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!