
One of the question that PrestaShoppers ask time by time is how to remove orders.
Why orders can’t be deleted by default?
Mainly because in Europe it’s considered illegal to remove ordering, invoice or payment information from a business.
Find out if in your country it’s illegal to remove orders and follow this tutorial at your own risk.
Still want to delete some orders?
If you’ve just created your store and started testing it, then decided that all is good and the tests must be removed, PrestaShop provides a module called Database Cleaner.
This can remove all the content in your store (that includes orders) to start from scratch.
The problem arises when you already had your store into the wild for a while, did some changes to your store configuration, maybe you added a new addon that has a big impact on the store’s functionality, did an upgrade from an older version (we all know how fun that is), whatever the reason might be you now want to test the ordering process. Then you’re stuck with those fake orders.
A feature that PrestaShop could implement to make our lives easier when testing the store is to flag all orders placed when store is in Demo mode as test orders and allow us to remove them. But until then …
What can be done?
For those of you who don’t have any form of OCD, just cancel the orders and forget about them.
But for the rest of “normal” people, don’t despair, there’s hope and hope dies last.
Searching through PrestaShop Forum you’ll find a few hacks, some free and paid modules that can accomplish the task. But what most of them don’t address is how clean the database remains after removing a test order. This leads to a database filled with junk data that could cause some serious damage to your store.
The clean way to delete test orders
Because we are talking about keeping a clean database we’re going to work with the database directly using MySQL queries.
You could use PhpMyAdmin or whatever method you find more convenient to run the queries.
PrestaShop hasn’t yet implemented FOREIGN KEY constraints so we need to manually find how the id keys included in ps_orders
table are spread across the database then simulate the ON DELETE CASCADE
behavior.
Search for all the tables that have id_order
and also the tables that have id_cart
because all orders have associated a cart.
1 2 3 4 5 6 7 |
SELECT DISTINCT `TABLE_NAME`, `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `COLUMN_NAME` LIKE 'id_order' OR `COLUMN_NAME` LIKE 'id_cart' AND `TABLE_SCHEMA` = '{{PS DATABASE NAME}}' GROUP BY `TABLE_NAME` ORDER BY `COLUMN_NAME` DESC, `TABLE_NAME` ASC; |
In the highlighted line 5 make sure you replace {{PS DATABASE NAME }}
with the database name of your store.
With the resulted tables create a DELETE
query from their LEFT JOIN
.
If you have in the result listed tables that don’t appear on my query, include them also in query.
If in my query are tables that are not present in your resulted list, remove them from your query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
DELETE ps_orders, ps_customer_thread, ps_order_carrier, ps_order_cart_rule, ps_order_detail, ps_order_history, ps_order_invoice, ps_order_invoice_payment, ps_order_return, ps_order_slip, ps_stock_mvt, ps_cart, ps_cart_cart_rule, ps_cart_product, ps_customization, ps_message, ps_specific_price FROM ps_orders LEFT JOIN ps_customer_thread ON ps_orders.id_order = ps_customer_thread.id_order LEFT JOIN ps_order_carrier ON ps_orders.id_order = ps_order_carrier.id_order LEFT JOIN ps_order_cart_rule ON ps_orders.id_order = ps_order_cart_rule.id_order LEFT JOIN ps_order_detail ON ps_orders.id_order = ps_order_detail.id_order LEFT JOIN ps_order_history ON ps_orders.id_order = ps_order_history.id_order LEFT JOIN ps_order_invoice ON ps_orders.id_order = ps_order_invoice.id_order LEFT JOIN ps_order_invoice_payment ON ps_orders.id_order = ps_order_invoice_payment.id_order LEFT JOIN ps_order_return ON ps_orders.id_order = ps_order_return.id_order LEFT JOIN ps_order_slip ON ps_orders.id_order = ps_order_slip.id_order LEFT JOIN ps_stock_mvt ON ps_orders.id_order = ps_stock_mvt.id_order LEFT JOIN ps_cart ON ps_orders.id_cart = ps_cart.id_cart LEFT JOIN ps_cart_cart_rule ON ps_orders.id_cart = ps_cart.id_cart LEFT JOIN ps_cart_product ON ps_orders.id_cart = ps_cart.id_cart LEFT JOIN ps_customization ON ps_orders.id_cart = ps_cart.id_cart LEFT JOIN ps_message ON ps_orders.id_cart = ps_cart.id_cart LEFT JOIN ps_specific_price ON ps_orders.id_cart = ps_cart.id_cart WHERE ps_orders.id_order = {{ID OF ORDER TO DELETE}}; |
In the highlighted line 37 make sure you replace {{ID OF ORDER TO DELETE}}
with the order id that you want to remove.
In the trenches
But in a real life scenario when we test, we test. We might have many test orders placed and repeating the process above for all of them is tedious.
I often create one or more test customer accounts which I use to place many different test orders.
If you want to delete the test orders placed with a test customer account edit the snippet code Remove a test order and replace the WHERE
clause from the highlighted line 37 with:
37 |
WHERE ps_orders.id_order IN ( SELECT id_order FROM ( SELECT * FROM ps_orders WHERE id_customer = {{ID OF TEST CUSTOMER}} ) AS tmp_test_orders ); |
Make sure you replace {{ID OF TEST CUSTOMER}}
with the customer id that you want to remove all of its placed orders.
What’s weird in all this “ILLEGAL TO DELETE ORDERS” thing is that we can delete customers and if the invoice feature is disabled there is no way to retrieve any information about who placed those orders, which makes them useless if some government agency wants to track them. You might still want to keep them for some statistical accuracy.
Here’s a little bonus MySQL query to see if you have orders of customers that have been removed.
1 |
SELECT DISTINCT `ps_orders`.`id_customer` FROM `ps_orders` LEFT JOIN `ps_customer` ON `ps_orders`.`id_customer` = `ps_customer`.`id_customer`; |
If you made many test customer accounts and now you want to remove them and their orders, first remove the customers as you would normally do from Back Office.
Then remove all the orders placed by removed customers by editing the snippet code Remove a test order and replace the WHERE
clause from the highlighted line 37 with:
37 38 39 40 |
WHERE ps_orders.id_order IN ( SELECT id_order FROM ( SELECT `ps_orders`.`id_order` FROM `ps_orders` LEFT JOIN `ps_customer` ON `ps_orders`.`id_customer` = `ps_customer`.`id_customer` WHERE `ps_customer`.`id_customer` IS NULL ) AS tmp_test_orders ); |
Now your orders are clean as a whistle.
I wish you less test orders and more real ones.
-
Jorge Aramuni
-
Gabriel Arama
-
Jorge Aramuni
-
-
Gabriel Arama
-
-
PsDesigner