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

@iputuadi , you would need to list each line item on it's own row:
a query like this could do the trick:

SELECT
  p.ID AS 'Order ID',
  p.post_date AS 'Purchase Date',
  MAX( CASE WHEN pm.meta_key = '_billing_email' AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Email Address',
  MAX( CASE WHEN pm.meta_key = '_order_total' AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Order Total',
  oi.order_item_name AS 'Item Name',
  oi.order_id AS 'Item Order ID'
FROM wp_posts AS p
JOIN wp_postmeta AS pm ON p.ID = pm.post_id
JOIN wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
WHERE
  post_type = 'shop_order'
  AND oi.order_item_type = 'line_item'
  AND p.post_date BETWEEN '2023-08-01' AND '2023-08-30'
GROUP BY p.ID, oi.order_item_id
ORDER BY p.ID, oi.order_item_id;

@iputuadi , you would need to list each line item on it's own row: a query like this could do the trick:

SELECT
  p.ID AS 'Order ID',
  p.post_date AS 'Purchase Date',
  MAX( CASE WHEN pm.meta_key = '_billing_email' AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Email Address',
  MAX( CASE WHEN pm.meta_key = '_order_total' AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Order Total',
  oi.order_item_name AS 'Item Name',
  oi.order_id AS 'Item Order ID'
FROM wp_posts AS p
JOIN wp_postmeta AS pm ON p.ID = pm.post_id
JOIN wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
WHERE
  post_type = 'shop_order'
  AND oi.order_item_type = 'line_item'
  AND p.post_date BETWEEN '2023-08-01' AND '2023-08-30'
GROUP BY p.ID, oi.order_item_id
ORDER BY p.ID, oi.order_item_id;

Hi @robindevitt Big thanks for your help, I really appreciate it.

I just thought about the woocommerce rest API,
https://woocommerce.com/document/woocommerce-rest-api/

And yesterday I've found the solution there.

Thank You

@iputuadi you care to share the solution should someone come across a similar issue in the future?

@iputuadi you care to share the solution should someone come across a similar issue in the future?

Yes sure. Thank you.

@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,

Would love some help. I just want to delete ALL orders no matter the status, with a date range...

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.