What is the best way to delete a Magento order?

by

The question:

Synopsis

I would like to delete test orders from my Magento store because they affect the statistics and reports of sales. Would the following code delete all records from:

  • sales/order_invoice
  • sales/order_invoice_grid
  • sales/order_shipment
  • sales/order_shipment_grid
  • sales/order_creditmemo
  • sales/order_creditmemo_grid
  • sales/order_payment_transaction
  • sales/order
  • sales/order_grid

Code Snippet

$orders = Mage::getModel('sales/order')->getCollection()
    ->addFieldToFilter('entity_id', (array) $orderIds);

foreach ($orders as $order) {
    // XXX - Possible cascade delete?
    $order->delete();
}

Or would I need to delete each of the records individually?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

As stated in the comments the script you have should work nicely for magento version 1.4+.
In the 1.4 version the sales entities were mapped to flat tables that have constraints so everything should cascade nicely.

For versions prior to 1.4 all the entities related to sales (orders, invoices, shipments and creditmemos) were EAV and kept in the same tables. So nothing cascade.
For version prior to 1.4 you can try this script.
This should work for later versions also but I don’t think it’s needed. 

$orders = Mage::getModel('sales/order')->getCollection()
    ->addFieldToFilter('entity_id', (array) $orderIds);
foreach ($orders as $o) {
    //load order object - I know it's not ok to use load in a loop but it should be ok since it's a one time script
    $order = Mage::getModel('sales/order')->load($o->getId());

    $invoices = $order->getInvoiceCollection();
    foreach ($invoices as $invoice){
        //delete all invoice items
        $items = $invoice->getAllItems(); 
        foreach ($items as $item) {
            $item->delete();
        }
        //delete invoice
        $invoice->delete();
    }
    $creditnotes = $order->getCreditmemosCollection();
    foreach ($creditnotes as $creditnote){
        //delete all creditnote items
        $items = $creditnote->getAllItems(); 
        foreach ($items as $item) {
            $item->delete();
        }
        //delete credit note
        $creditnote->delete();
    }
    $shipments = $order->getShipmentsCollection();
    foreach ($shipments as $shipment){
        //delete all shipment items
        $items = $shipment->getAllItems(); 
        foreach ($items as $item) {
            $item->delete();
        }
        //delete shipment
        $shipment->delete();
    }
    //delete all order items
    $items = $order->getAllItems(); 
    foreach ($items as $item) {
        $item->delete();
    }
    //delete payment - not sure about this one
    $order->getPayment()->delete();
    //delete quote - this can be skipped
    if ($order->getQuote()) {
        foreach ($order->getQuote()->getAllItems() as $item) {
            $item->delete();
        }
        $order->getQuote()->delete();
    }
    //delete order
    $order->delete();
}

Method 2

There are some ways to delete orders in Magento

1. Delete order one by one

To do so, firstly you have to log on your PhpmyAdmin (use full access). Here, you have to run some queries like the following:

set @increment_id='200000111';
select @order_id:=entity_id from prefix_sales_order_entity where [email protected]_id;
delete from prefix_sales_order_entity where [email protected]_id or [email protected]_id;
delete from prefix_sales_order where [email protected]_id;

Please remember to replace “order ID” I the above example with the ID of order you wish to delete. Similarly to “prefix”, you can replace it with the one you have selected in your store. Besides, you also need to pay attention to REST of Database.

2. Delete all orders

Step 1: Log on PhpMyAdmin

Step 2: Run SQL Query

Here are the queries you need to add in your database to delete all orders:

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE `sales_order`;
TRUNCATE `sales_order_datetime`;
TRUNCATE `sales_order_decimal`;
TRUNCATE `sales_order_entity`;
TRUNCATE `sales_order_entity_datetime`;
TRUNCATE `sales_order_entity_decimal`;
TRUNCATE `sales_order_entity_int`;
TRUNCATE `sales_order_entity_text`;
TRUNCATE `sales_order_entity_varchar`;
TRUNCATE `sales_order_int`;
TRUNCATE `sales_order_text`;
TRUNCATE `sales_order_varchar`;
TRUNCATE `sales_flat_quote`;
TRUNCATE `sales_flat_quote_address`;
TRUNCATE `sales_flat_quote_address_item`;
TRUNCATE `sales_flat_quote_item`;
TRUNCATE `sales_flat_quote_item_option`;
TRUNCATE `sales_flat_order_item`;
TRUNCATE `sendfriend_log`;
TRUNCATE `tag`;
TRUNCATE `tag_relation`;
TRUNCATE `tag_summary`;
TRUNCATE `wishlist`;
TRUNCATE `log_quote`;
TRUNCATE `report_event`;

ALTER TABLE `sales_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_datetime` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_decimal` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_int` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_text` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_varchar` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1;
ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1;
ALTER TABLE `tag` AUTO_INCREMENT=1;
ALTER TABLE `tag_relation` AUTO_INCREMENT=1;
ALTER TABLE `tag_summary` AUTO_INCREMENT=1;
ALTER TABLE `wishlist` AUTO_INCREMENT=1;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;
ALTER TABLE `report_event` AUTO_INCREMENT=1;

— lets reset customers

TRUNCATE `customer_address_entity`;
TRUNCATE `customer_address_entity_datetime`;
TRUNCATE `customer_address_entity_decimal`;
TRUNCATE `customer_address_entity_int`;
TRUNCATE `customer_address_entity_text`;
TRUNCATE `customer_address_entity_varchar`;
TRUNCATE `customer_entity`;
TRUNCATE `customer_entity_datetime`;
TRUNCATE `customer_entity_decimal`;
TRUNCATE `customer_entity_int`;
TRUNCATE `customer_entity_text`;
TRUNCATE `customer_entity_varchar`;
TRUNCATE `log_customer`;
TRUNCATE `log_visitor`;
TRUNCATE `log_visitor_info`;

ALTER TABLE `customer_address_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `log_customer` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;

— Now, lets Reset all ID counters

TRUNCATE `eav_entity_store`;
ALTER TABLE `eav_entity_store` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

When finished, all orders are deleted in your site!

3. Write script

By creating a file with the level as exactly as index.php, you can quickly delete the unnecessary with its order ID.

load($orderId);

$invoices = $order->getInvoiceCollection();
foreach ($invoices as $invoice){
$invoice->delete();
}

$creditnotes = $order->getCreditmemosCollection();
foreach ($creditnotes as $creditnote){
$creditnote->delete();
}

$shipments = $order->getShipmentsCollection();
foreach ($shipments as $shipment){
$shipment->delete();
}

$order->delete();
?>

Now, the order is removed entirely from your system.

4. Use extensions

One of the best and fastest way to delete unwanted orders is using a Magento extension. There are a lot of extensions supporting this function you can see on Magento Connect.

For example, the following extension:

https://www.magentocommerce.com/magento-connect/delete-order-invoice-shipment-credit-memo.html

It helps to delete orders and its linked data such as invoice, credit memos, shipment. Moreover, it also can remove unwanted shipment, invoice and credit memos separately

Method 3

  if (version_compare(phpversion(), '5.2.0', '<')===true) {
      echo  '<div style="font:12px/1.35em arial, helvetica, sans-serif;"><div style="margin:0 0 25px 0; border-bottom:1px solid #ccc;"><h3 style="margin:0; font-size:1.7em; font-weight:normal; text-transform:none; text-align:left; color:#2f2f2f;">Whoops, it looks like you have an invalid PHP version.</h3></div><p>Magento supports PHP 5.2.0 or newer. <a href="http://www.magentocommerce.com/install" rel="nofollow noreferrer noopener" rel="nofollow noreferrer noopener" target="">Find out</a> how to install</a> Magento using PHP-CGI as a work-around.</p></div>';
      exit;
  }

  error_reporting(E_ALL | E_STRICT);
  ini_set('display_errors', 1);

  $mageFilename = 'app/Mage.php';

  if (!file_exists($mageFilename)) {
      echo $mageFilename." was not found";
      exit;
  }

  require_once $mageFilename;

  Mage::app();

  $executionPath = null;

  /*
  * determine Magento Edition
  */
  if (file_exists('LICENSE_EE.txt')) {
      $edition = 'EE';
  }elseif (file_exists('LICENSE_PRO.html')) {
      $edition = 'PE';
  } else {
      $edition = 'CE';    
  }

  if(($edition=='EE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)
          || ($edition=='PE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)
          || ($edition=='CE' && version_compare(Mage::getVersion(), '1.6.0.0.', '<')===true)
    ){
    $executionPath = 'old'; 
  } else {
    $executionPath = 'new';  
  }

  $xpathEntity = 'global/models/sales_entity/entities//table';

  if ($executionPath == 'old') {
      $xpathResource = 'global/models/sales_mysql4/entities//table';
  } else {
      $xpathResource = 'global/models/sales_resource/entities//table';
  }

  $salesEntitiesConf = array_merge(
      Mage::getSingleton('core/config')->init()->getXpath($xpathEntity), 
      Mage::getSingleton('core/config')->init()->getXpath($xpathResource)
  );

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


  /*
  * If you want delete System/Order Statuses (Status and State) you
  * should comments below lines (46-51)
  */
  $skipTables = array (
          $resource->getTableName('sales_order_status'),
          $resource->getTableName('sales_order_status_state'),
          $resource->getTableName('sales_order_status_label')
      );
  $salesEntitiesConf = array_diff($salesEntitiesConf, $skipTables);


  /*

  Multiple RDBMS Support in Magento CE 1.6+ / EE 1.11+
      http://www.magentocommerce.com/images/uploads/RDBMS_Guide2.pdf

  2.2. Adapters:

  ... The new Varien_DB_Adapter_Interface was added to sign a contract that all 
  developed adapters must execute in order to get Magento working on an actual 
  database. The interface describes the list of methods and constants that can be used by resource models...

  Used below in the loop:

  * If $executionPath == 'old'
      * Varien_Db_Adapter_Pdo_Mysql::showTableStatus()
      * Varien_Db_Adapter_Pdo_Mysql::truncate()  
  * Else
      * Varien_Db_Adapter_Interface::isTableExists()
      * Varien_Db_Adapter_Interface::truncateTable()

  */

  while ($table = current($salesEntitiesConf) ){
      $table = $resource->getTableName($table);

      if ($executionPath == 'old') {
          $isTableExists = $connection->showTableStatus($table);
      } else {
          $isTableExists = $connection->isTableExists($table);
      }
      if ($isTableExists) {
          try {
              if ($executionPath == 'old') {
                  $connection->truncate($table);
              } else {
                  $connection->truncateTable($table);
              }

              printf('Successfully truncated the <i style="color:green;">%s</i> table.<br />', $table);
          } catch(Exception $e) {
              printf('Error <i style="color:red;">%s</i> occurred truncating the <i style="color:red;">%s</i> table.<br />', $e->getMessage(), $table);
          }
      }

      next($salesEntitiesConf);
  }

  exit('All done...');
  /**
  * @author Dejan Radic <[email protected]>
  */

  if (version_compare(phpversion(), '5.2.0', '<')===true) {
      echo  '<div style="font:12px/1.35em arial, helvetica, sans-serif;"><div style="margin:0 0 25px 0; border-bottom:1px solid #ccc;"><h3 style="margin:0; font-size:1.7em; font-weight:normal; text-transform:none; text-align:left; color:#2f2f2f;">Whoops, it looks like you have an invalid PHP version.</h3></div><p>Magento supports PHP 5.2.0 or newer. <a href="http://www.magentocommerce.com/install" rel="nofollow noreferrer noopener" rel="nofollow noreferrer noopener" target="">Find out</a> how to install</a> Magento using PHP-CGI as a work-around.</p></div>';
      exit;
  }

  error_reporting(E_ALL | E_STRICT);
  ini_set('display_errors', 1);

  $mageFilename = 'app/Mage.php';

  if (!file_exists($mageFilename)) {
      echo $mageFilename." was not found";
      exit;
  }

  require_once $mageFilename;

  Mage::app();

  $executionPath = null;

  /*
  * determine Magento Edition
  */
  if (file_exists('LICENSE_EE.txt')) {
      $edition = 'EE';
  }elseif (file_exists('LICENSE_PRO.html')) {
      $edition = 'PE';
  } else {
      $edition = 'CE';    
  }

  if(($edition=='EE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)
          || ($edition=='PE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)
          || ($edition=='CE' && version_compare(Mage::getVersion(), '1.6.0.0.', '<')===true)
    ){
    $executionPath = 'old'; 
  } else {
    $executionPath = 'new';  
  }

  $xpathEntity = 'global/models/sales_entity/entities//table';

  if ($executionPath == 'old') {
      $xpathResource = 'global/models/sales_mysql4/entities//table';
  } else {
      $xpathResource = 'global/models/sales_resource/entities//table';
  }

  $salesEntitiesConf = array_merge(
      Mage::getSingleton('core/config')->init()->getXpath($xpathEntity), 
      Mage::getSingleton('core/config')->init()->getXpath($xpathResource)
  );

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


  /*
  * If you want delete System/Order Statuses (Status and State) you
  * should comments below lines (46-51)
  */
  $skipTables = array (
          $resource->getTableName('sales_order_status'),
          $resource->getTableName('sales_order_status_state'),
          $resource->getTableName('sales_order_status_label')
      );
  $salesEntitiesConf = array_diff($salesEntitiesConf, $skipTables);


  /*

  Multiple RDBMS Support in Magento CE 1.6+ / EE 1.11+
      http://www.magentocommerce.com/images/uploads/RDBMS_Guide2.pdf

  2.2. Adapters:

  ... The new Varien_DB_Adapter_Interface was added to sign a contract that all 
  developed adapters must execute in order to get Magento working on an actual 
  database. The interface describes the list of methods and constants that can be used by resource models...

  Used below in the loop:

  * If $executionPath == 'old'
      * Varien_Db_Adapter_Pdo_Mysql::showTableStatus()
      * Varien_Db_Adapter_Pdo_Mysql::truncate()  
  * Else
      * Varien_Db_Adapter_Interface::isTableExists()
      * Varien_Db_Adapter_Interface::truncateTable()

  */

  while ($table = current($salesEntitiesConf) ){
      $table = $resource->getTableName($table);

      if ($executionPath == 'old') {
          $isTableExists = $connection->showTableStatus($table);
      } else {
          $isTableExists = $connection->isTableExists($table);
      }
      if ($isTableExists) {
          try {
              if ($executionPath == 'old') {
                  $connection->truncate($table);
              } else {
                  $connection->truncateTable($table);
              }

              printf('Successfully truncated the <i style="color:green;">%s</i> table.<br />', $table);
          } catch(Exception $e) {
              printf('Error <i style="color:red;">%s</i> occurred truncating the <i style="color:red;">%s</i> table.<br />', $e->getMessage(), $table);
          }
      }

      next($salesEntitiesConf);
  }

  exit('All done...');

I have not performed this solution. I have taken reference from this post : https://www.codecanal.com/delete-test-orders-in-magento-programmatically/

So take backup before doing any changes.

Method 4

Try this, (you can use an external script to run the below code snippet. Magento CE 1.8)

    // Test orders collection.
    $collection = Mage::getModel('sales/order')->getCollection();
                    ->addFieldToFilter('entity_id', (array) $orderIds);

    // Retrieve the write connection.
    $writeConnection = Mage::getSingleton('core/resource')->getConnection('core_write');

    foreach($collection as $order) {
        $orderIncrementId = $order->getIncrementId();
        $query = "SET @orderId = '".$orderIncrementId."';
                SET FOREIGN_KEY_CHECKS = 1;
                DELETE FROM sales_flat_quote
                WHERE reserved_order_id = @orderId;
                DELETE FROM sales_flat_order
                WHERE increment_id = @orderId;
                ";

        // Execute the query.
        $writeConnection->query($query);
    }

NOTE: This code is not according to the standards since it uses direct
SQL queries. But it will do what you want


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment