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

Hi, that code worked for me too after removing oi.order_item_name = 'Product Name' but can you tell me how can I extract all orders done per product ID.

niiiiiiiiiiiiice ^_^

Hi All can you please advise i used this script , changed the date and removed this condition:

oi.order_item_name = 'Product Name' but i get null on the result can you please help?

@lukecav

@theodiablo

Thank you

Looking to add the the quantity of an item purchased to the "order_items", any suggestions?
So it could be something like:
"product name ( 2 ) | another product ( 1 ) | last product ( 5 )"

Hey,

Looking to add the the quantity of an item purchased to the "order_items", any suggestions?

I think WooCommerce >> Reports can do this for you: https://d.pr/i/zL8c1Y
The reports are created dynamically so you can probably take a peek on how this is solved in the code and use that.

Change wp_ prefix if you need and works fine!

Hi!
How can I retrieve orders where the date paid is greater than 1 day from the date the order was created?

You would need to include the order status for completed if you wanted to query that.

Thank you very much. Was a great help!

Hi,
how can I add SKU code along with order_item_name or even replace order_item_name with SKU in the inner subquery "select group_concat ..."?

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,