Calling Developers!
We are reenergizing our code contribution process! Learn More

What are the Slack Archives?

It’s a history of our time together in the Slack Community! There’s a ton of knowledge in here, so feel free to search through the archives for a possible answer to your question.

Because this space is not active, you won’t be able to create a new post or comment here. If you have a question or want to start a discussion about something, head over to our categories and pick one to post in! You can always refer back to a post from Slack Archives if needed; just copy the link to use it as a reference..

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

Options
michael.schmetter
michael.schmetter Posts: 119 🧑🏻‍🚀 - Cadet

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

Comments

  • UKEP86J66
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet
    Options

    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
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet
    Options

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

  • michael.schmetter
    michael.schmetter Posts: 119 🧑🏻‍🚀 - Cadet
    Options

    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
    UKEP86J66 Posts: 208 🧑🏻‍🚀 - Cadet
    Options

    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
    michael.schmetter Posts: 119 🧑🏻‍🚀 - Cadet
    edited June 2022
    Options

    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