6

We have about 68 000 disabled products in our database. We need somehow to delete all disabled products from the shop. The normal Magento's delete product process in the admin does not work as it gets interrupted by the server (time exceeded on a shared host).

Also tried with product import and it's deleted option, but that too gets interrupted. Also tried Magmi with the same result. The problem might be related to reindexing that takes a long time, but not sure.

Just wondering if there is a safe way to delete all disabled products via SQL? Or would there be another way to automatically delete products one by one?

4 Answers 4

7

I would actually try to do it via SQL because in Magento, relations are done with foreign keys and ON DELETE CASCADE, so that you don't end up with dead data. However, before such an action, always make a backup, and try on a test database first!

DELETE catalog_product_entity FROM catalog_product_entity e
    INNER JOIN catalog_product_entity_int v
    ON v.entity_id=e.entity_id
    AND v.attribute_id=(SELECT attribute_id FROM eav_attribute ON attribute_code='status')
    WHERE v.value=2

If you prefer using the Magento methods, you can use the core/iterator resource model to not iterate over the collection. This avoids loading all products at once in memory, as in the other answers:

    $collection = Mage::getModel('catalog/product')->getCollection()
       ->addAttributeToSelect('status')
       ->addAttributeToFilter('status', array('neq' => 1));

   Mage::getResourceModel('core/iterator')
    ->walk($collection->getSelect(),[function($args) {
        $productId = $args['row']['entity_id'];
        $product = Mage::getModel('catalog/product')->setId($productId);
        $product->delete();
    }]);

The iterator fetches the db result row by row, without instantiating Magento models. To delete a product, you don't need to load it, just instantiate a model, set the id, and call delete().

2

Direct SQL way (backup your database first). Based on Fabian's answer, but this one actually works.

DELETE e FROM catalog_product_entity e
INNER JOIN catalog_product_entity_int v
ON v.entity_id=e.entity_id
AND v.attribute_id IN 
    (SELECT attribute_id FROM eav_attribute WHERE attribute_code='status')
WHERE v.value=2;
0
0

I think you'll have the same timeout problem I had when I wrote an import script, because the indexing was set to automatic.

I would create a script in your magento root (or in a subfolder) and do the following:

1) make sure you've got all necessary includes ..

// load the mage file
require("../app/Mage.php");

// load magento in admin mode
Mage::app('admin');

Loading in admin mode is necessary to be able to delete.

2) Make sure you disable indexing:

// set the indexing modus in magento 
// TRUE or FALSE
function setIndexingManual($value = TRUE) { 

    // set the magento indexing status to auto
    $pCollection = Mage::getSingleton('index/indexer')->getProcessesCollection();

    if($value) { // TRUE
        $mode = Mage_Index_Model_Process::MODE_REAL_TIME;
    } else {    
        $mode = Mage_Index_Model_Process::MODE_MANUAL;
    }

    foreach ($pCollection as $process) {
      $process->setMode($mode)->save();
    }
}

Call it with the following before you do any mass edits / deletes / creates:

// set the magento indexing status to manual
setIndexingManual(FALSE);

Now do your stuff by first getting all disabled products ..

$collection = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('status', array('eq' => 2)) //only disabled 
    ->load();

now ..loop through the collection and delete each product

// loop through each product
foreach($collection as $product){

   try {
        // delete the product
        $product_load = Mage::getSingleton('catalog/product')->load($product->getId());

        Mage::dispatchEvent('catalog_controller_product_delete', array('product' => $product_load));

        $product_load->delete();

   } catch(Exception $e) { // end try
        // error log
        Mage::log($e->getMessage());

        $error = $e->getMessage();

        echo "-> <b>deletion of " . $product->getName() . "(" . $product->getId() . ") failed!</b> <br />$error<br /><br />";

    }



}

Now you can enable indexing again ..

// set the magento indexing status to auto
setIndexingManual();
4
  • Thank you. This works at the test shop with a bit less products. With the actual site it presents this error without deleting the products: PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 72 bytes) in /public_html/lib/Zend/Db/Adapter/Abstract.php on line 872
    – Webninja
    Sep 17, 2015 at 7:58
  • Have you tried increasing your memory limit?
    – tecjam
    Sep 17, 2015 at 8:08
  • Is there a way to do it in shared hosting? I tried with the htaccess file, but with no luck. Tried this: php_value memory_limit 512M
    – Webninja
    Sep 17, 2015 at 8:46
  • 1
    You could try and put ini_set('memory_limit','1024M'); in the first line of the file you are executing. Else you may have to export the DB, install it locally and do your deletes there and then re-import. However, I am surprised you managed to run the shop at all with 70,000 Produkt on a shared hosting.
    – tecjam
    Sep 17, 2015 at 9:49
0

Yes direct in DB is a dangerous move. Easiest way to delete disabled products programmatically is this;

This is assuming you are not using multistore where products could have different status in each store view.

Create a php file with this code in;

include_once "app/Mage.php";
Mage::init();
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID); // This gives us the rights to delete


$collection = Mage::getModel('catalog/product')
->getCollection()
->addAttributeToSelect('*')
->addAttributeToFilter('status', array('neq' => 1)) // only get disabled
->load();

foreach($collection as $_product) {
    echo 'Deleting: '.$_product->getName().'<br />';
    $_product->delete();
}

Drop this code in Magento root direcory and call it via a browser. For automation I would recommend putting it in your shell directory (dont forget to modify the path to app/mage) and call it by cron each night.

I hope this will help you.

1
  • 1
    Loading a collection with 68000 products all at once is not a good idea, unless you have unlimited RAM. The needless addAttributeToSelect(*) does not make it better Dec 31, 2015 at 12:46

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.