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

Hello
How can I add multiple SKU's with mutiple order items?

Thanks - this is nice Luke!
How to tweak it to display (and then delete) any duplicate orders?
...and any connected duplicated entries in other tables.

UPDATE - I made this to achieve the above.
NOTE a) Replace the following with your data: DB-NAME-HERE, MIN_ORDER_ID, MAX_ORDER_ID and the prefix "wp_".
b) You could also add a date restriction if needed.

SHOW DUPLICATES... (not originals) - test this first
SELECT * FROM DB-NAME-HERE.wp_postmeta as pm INNER JOIN DB-NAME-HERE.wp_posts as p ON p.ID = pm.post_id WHERE p.post_type = 'shop_order' AND pm.post_id BETWEEN MIN_ORDER_ID AND MAX_ORDER_ID AND pm.meta_id NOT IN ( SELECT * FROM (SELECT MIN(pm.meta_id) FROM wp_postmeta pm GROUP BY pm.post_id, pm.meta_key) x)

DELETE DUPLICATES... (not originals)
DELETE pm.* FROM DB-NAME-HERE.wp_postmeta as pm INNER JOIN DB-NAME-HERE.wp_posts as p ON p.ID = pm.post_id WHERE p.post_type = 'shop_order' AND pm.post_id BETWEEN MIN_ORDER_ID AND MAX_ORDER_ID AND pm.meta_id NOT IN ( SELECT * FROM (SELECT MIN(pm.meta_id) FROM wp_postmeta pm GROUP BY pm.post_id, pm.meta_key) x)

Thanks for the script!

Just one thing,
post_date BETWEEN '2020-01-01' AND '2020-12-31' doesn't include orders from 2020-12-31, so you need to use post_date BETWEEN '2020-01-01' AND '2021-01-01' instead.

@lukecav It is a pity there are no PRs for gist. In my fork, I added some more of the metadata, which is not as important, but I also cast some of the columns with proper types which is helpful for any further analysis. For the same reason, I also removed all the time range and order status based conditions.

Feel free to cherry-pick if anything looks useful to you: https://gist.github.com/aidik/ca0e02d5f25a958ff8143578c7a78836

I just want to do this and get orders per user so we can pass in a specific user for the result. Where is that where clause added?

How can i add a line to found by SKU these orders?

http://codecharismatic.com/sql-script-to-get-all-woocommerce-orders-including-metadata/

Hello, i was working with this code for a lot, thanks to you, and i have reached end... I tryed everything but cant display orders items quantity.
Tried like this:
(select meta_value from wp_woocommerce_order_itemmeta where meta_key = '_qty' and order_item_id = oi.order_item_id) as testa,
Was trying with MAX to
but its displays only one qty, its good if in order is one item, but not when in order more like 2... Can u help me?

http://codecharismatic.com/sql-script-to-get-all-woocommerce-orders-including-metadata/

Hello, i was working with this code for a lot, thanks to you, and i have reached end... I tryed everything but cant display orders items quantity. Tried like this: (select meta_value from wp_woocommerce_order_itemmeta where meta_key = '_qty' and order_item_id = oi.order_item_id) as testa, Was trying with MAX to but its displays only one qty, its good if in order is one item, but not when in order more like 2... Can u help me?

Hi sir, i have the same problem, i can't get the QTY value. Have you found a solution?

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

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

@Flioper & @iputuadi Maybe try this as a starting point:

SELECT
  p.ID AS 'Order ID',
  p.post_date AS 'Purchase Date',
  ( SELECT count( m.meta_value = m.meta_value)
    FROM wp_woocommerce_order_items i
    JOIN wp_woocommerce_order_itemmeta m ON i.order_item_id = m.order_item_id AND meta_key = '_qty'
    WHERE i.order_id = p.ID AND i.order_item_type = 'line_item') AS 'Total Items Ordered'
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
	p.post_date BETWEEN '2023-01-01' AND '2023-10-30'
GROUP BY p.ID

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

Hi, sure i have working code. I can show it in a week maybe. If u will wait i will post it here :)

Thanks @robindevitt for the code. but I don't think this is showing what I need yet.

Your query, just showing totals item in one invoice.

What I need is the purchasing QTY per item like in this piture.

WP Admin --> woocommerce --> Orders --> Detail Order

other than QTY, I also have a lot of trouble finding values from Customer purchase_note.

Basically, I want to display all the data that appears in the order details pages. into the query results in phpmyadmin.

I really appreciate sir. for your help.

It's been 2 days, but I haven't found a solution yet.

@iputuadi it should give you the QTY for your example it'll return 30. If you have multiple line items that the qty would be added together. Are you after retrieving all the line items for example: "30 x Product 1, 10 x Product 2, 5 x Product 3" and so on?

@iputuadi it should give you the QTY for your example it'll return 30. If you have multiple line items that the qty would be added together. Are you after retrieving all the line items for example: "30 x Product 1, 10 x Product 2, 5 x Product 3" and so on?

your query earlier, just shows recap data, what i really need, is the details data orders.

I give an example in the picture.

Example A:
When your query was executed, what appears is the recap data. as an example Order ID: 115 the result is = 1. Because there is only 1 item / product variant purchased in a transaction with code 115.

Example B:
For Order ID: 119 the result will appear is 2.

Because there are only 2 items / product variants purchased in transactions with code 119.

But what i really need, is the details data orders, per invoice (by Order ID). As in the picture.

The questions is, how to get the "QTY" value 30 from order ID 115, and qty value 12, 5, from order ID 119.

Thank you @robindevitt

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