4

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?

  • 1
    Please refer this link inchoo.net/ecommerce/magento/delete-test-orders-in-magento – Keyul Shah Apr 22 '14 at 16:21
  • Thanks :) lol, that might help someone else but I doubt my several resourceModel collections are going to kill the server; especially when Magento can go that itself by compiling the config.xml. – ash Apr 22 '14 at 16:31
  • better u can use extensions – Keyul Shah Apr 22 '14 at 16:34
  • 1
    For versions after 1.4 your script should work. There are foreign keys in the invoice/shipment/creditmemo/payment tables (and the tables for the items) to the order table. So everything should cascade nicely. In versions prior to 1.4 this won't work because all the orders, invoices, shipments and creditmemos were stored in the same tables (EAV) and you have to delete each of them manually (including their items and order payments). – Marius Apr 24 '14 at 6:49
  • 1
    @DavidManners I will try, but I don't feel that this is a full answer. I will try to come up with something more valuable – Marius May 29 '14 at 8:12
5

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();
}
  • So an end user has to contact a developer to delete orders... hmm. – Eddie B Jul 15 '15 at 19:05
  • We have placed order (Recurring Profiles). And we have use above mentioned coding. Only deleted Order details, But does not delete Recurring profile records. How to delete both order and related recurring profile details. – Sankar_k Feb 23 '17 at 6:30
1

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 increment_id=@increment_id;
delete from prefix_sales_order_entity where entity_id=@order_id or parent_id=@order_id;
delete from prefix_sales_order where increment_id=@increment_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

1
  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" 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 <dejan.radic@inchoo.net>
  */

  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" 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.

0

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

  • I appreciate you trying to help, but this kind of answer contributes to the confusion and bad level of code in the Magento Community - regardless of the fact you've stated it's against the Magento way. Especially as Marius has explained that after 1.4 the constraints will take care of everything with a simple ORM delete. – ash Apr 24 '14 at 10:12
  • Try to replace SQL query in magento way, Then it won't be that bad :) – Sukeshini Apr 24 '14 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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