Hey guys! Does someone maybe has a SQL Query to remove orders and all related enities? I want to rem

michael.schmetter

Hey guys!
Does someone maybe has a SQL Query to remove orders and all related enities?
I want to remove some testdata

UKEP86J66

Deleting order data is tricky, because there are so many relations. You will need to adapt this to include any payment tables you might have but this covers the core:

BEGIN;

-- We need to cache these ids because the addresses must be deleted after the order has been
-- deleted, and if we don't have this cache we cannot find the addresses by id_order

CREATE TEMP TABLE tmp_sales_order_address_id (
    id_sales_order_address INT
);

INSERT INTO tmp_sales_order_address_id
SELECT fk_sales_order_address_billing FROM spy_sales_order WHERE id_sales_order = :orderid;

INSERT INTO tmp_sales_order_address_id
SELECT fk_sales_order_address_shipping FROM spy_sales_order WHERE id_sales_order = :orderid;

INSERT INTO tmp_sales_order_address_id
SELECT fk_sales_order_address FROM spy_sales_shipment WHERE fk_sales_order = :orderid;

delete from spy_oms_order_item_state_history where fk_sales_order_item IN (select id_sales_order_item from spy_sales_order_item where fk_sales_order = :orderid);
delete from spy_oms_transition_log where fk_sales_order = :orderid;
delete from spy_sales_order_totals where fk_sales_order = :orderid;
delete from spy_sales_discount where fk_sales_order = :orderid;
delete from spy_sales_payment where fk_sales_order = :orderid;
delete from spy_oms_event_timeout where fk_sales_order_item IN (select id_sales_order_item from spy_sales_order_item where fk_sales_order = :orderid);
delete from spy_sales_order_item_metadata where fk_sales_order_item IN (select id_sales_order_item from spy_sales_order_item where fk_sales_order = :orderid);
delete from spy_sales_order_item where fk_sales_order = :orderid;
delete from spy_sales_order_comment where fk_sales_order = :orderid;
delete from spy_sales_shipment where fk_sales_order = :orderid;
delete from spy_sales_expense where fk_sales_order = :orderid;
delete from spy_sales_order where id_sales_order = :orderid;
DELETE FROM spy_sales_order_address WHERE id_sales_order_address IN (SELECT id_sales_order_address FROM tmp_sales_order_address_id);
DROP TABLE tmp_sales_order_address_id;

COMMIT;

UKEP86J66

I had this to hand because I am doing exactly the same thing at the moment 🙂

michael.schmetter

Wow Rick, that was exactly what I was looking for!

Deleting order data is tricky, because there are so many relations.

That's why I asked 🙂

UKEP86J66

I’m not sure if it will work directly with a propel connection, because it’s a multi statement script. We are using it directly with psql and using the -v option to pass in the orderid parameter.

michael.schmetter

Here it is a bit pimped including some missing tables (discount_code and marketplace stuff)
And runnable from a good old SQL console use

insert into tmp_sales_order_id
SELECT id_sales_order from spy_sales_order;

To control which orders should be deleted