MySQL script to get all WooCommerce orders including metadata

Query

select
    p.ID as order_id,
    p.post_date,
    max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
    max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
    max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
    max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
    max( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
    max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
    max( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
    max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
    max( CASE WHEN pm.meta_key = '_shipping_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
    max( CASE WHEN pm.meta_key = '_shipping_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
    max( CASE WHEN pm.meta_key = '_shipping_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
    max( CASE WHEN pm.meta_key = '_shipping_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
    max( CASE WHEN pm.meta_key = '_shipping_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
    max( CASE WHEN pm.meta_key = '_shipping_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
    max( CASE WHEN pm.meta_key = '_shipping_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
    max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
    max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
    max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
    ( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
from
    wp_posts p 
    join wp_postmeta pm on p.ID = pm.post_id
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
where
    post_type = 'shop_order' and
    post_date BETWEEN '2021-01-01' AND '2021-08-01' and
    post_status = 'wc-completed'
group by
    p.ID

To delete all orders regardless of their status or duplication state, you will need to modify the query to target all relevant records in the `wp_postmeta` and `wp_posts` tables. Here's how you can adjust your queries to achieve this:**Note**: Always ensure you have a backup of your database before running any DELETE operations.### Show All OrdersTo show all orders, you can simply query the `wp_posts` table for posts of type `shop_order`.SELECT * FROM DB-NAME-HERE.wp_posts WHERE post_type = 'shop_order'   AND ID BETWEEN MIN_ORDER_ID AND MAX_ORDER_ID;### Delete All OrdersTo delete all orders, you will need to delete entries from both `wp_postmeta` and `wp_posts` tables. First, delete the related metadata, and then delete the orders themselves:1. **Delete Metadata of Orders:**DELETE pmFROM DB-NAME-HERE.wp_postmeta pmINNER JOIN DB-NAME-HERE.wp_posts p ON p.ID = pm.post_idWHERE p.post_type = 'shop_order'  AND pm.post_id BETWEEN MIN_ORDER_ID AND MAX_ORDER_ID;2. **Delete Orders:**DELETE FROM DB-NAME-HERE.wp_postsWHERE post_type = 'shop_order'  AND ID BETWEEN MIN_ORDER_ID AND MAX_ORDER_ID;These queries will ensure that all orders and their associated metadata within the specified ID range are deleted. Remember to replace `DB-NAME-HERE`, `MIN_ORDER_ID`, and `MAX_ORDER_ID` with your actual database name and the desired ID range.On 15 Jun 2024, at 6:02 am, Brian ***@***.***> wrote:Re: ***@***.*** commented on this gist.Would love some help. I just want to delete ALL orders no matter the status, with a date range...—Reply to this email directly, view it on GitHub or unsubscribe.You are receiving this email because you commented on the thread.Triage notifications on the go with GitHub Mobile for iOS or Android.                                                           

@Flioper & @iputuadi want to maybe post what you have so far so one can assist?

I want to get the value of the quantity in each order. I try to use the "_qty" parameter, but it doesn't show anything. Do you know how to get the quantity value for each order on woocommerce via a query?

With qty was a little harder, here i have this code, for me its works, shows everything :) (select group_concat(woim.meta_value separator ' ' ) from wp_woocommerce_order_items woi join wp_woocommerce_order_itemmeta woim on woi.order_item_id = woim.order_item_id where woim.meta_key = '_qty' and woi.order_id = oi.order_id) as qty,

Yes, this works.
Thanks a lot.