How to delete test orders and keep a clean database in PrestaShop

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.

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.

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:

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.

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:

Now your orders are clean as a whistle.
I wish you less test orders and more real ones.