SQL Script To Get All WooCommerce Orders Including Metadata

A client of mine has a rather large WooCommerce database. We’ve been trying to run reports using the WooCommerce Customer/Order CSV Export plugin but have been running into timeout issues—the glorious WordPress white screen of death.

I got fed up and wrote the following script to export the needed information. Customize as you see fit. Enjoy.

Update 26 Aug 2016: By popular demand, I added a way to query for orders based on product name. Querying by product id is the right way but makes my head hurt. Suggestions welcome.

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 '2015-01-01' AND '2015-07-08' and
post_status = 'wc-completed' and
oi.order_item_name = 'Product Name'
group by
p.ID

52 comments on “SQL Script To Get All WooCommerce Orders Including Metadata”

Your query to me served as a basis for arriving at this one that adds the phone and order status id.

This part of your query seems not to work according to my wp_posts fields table:

post_status = 'wc-completed'

I do not have any ‘wc-completed’ value on p.post_status field.

This is the version I use, if I let someone else serves.

Greetings!

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_phone' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_phone,
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 = '_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,
max( CASE WHEN p.ID = wptr.object_id THEN wptr.term_taxonomy_id END ) as status
from
wp_posts as p,
wp_postmeta as pm,
wp_term_relationships as wptr
where
post_type = 'shop_order' and
p.ID = pm.post_id and
post_date BETWEEN '2015-06-31' AND '2015-08-01'
group by
p.ID

Hi Pedro, thanks for the additions and you’re welcome. WooCommerce added the prefix wc- to their order statuses in version 2.2. I’m guessing that might be why you’re not seeing any orders. What version do you have?

Where are stored information about ordered products which I can match to particular order retrieved using query above? I have tried to use data from wp_woocommerce_order_items table but I have no idea what correlation is between order posts and order_items.

Really thank you for this code .I need to download order report of woocommerce in csv format, for that i made the following query:
header(‘Content-Type: text/csv; charset=utf-8′);
header(‘Content-Disposition: attachment; filename=data.csv’);
// create a file pointer connected to the output stream
$output = fopen(‘php://output’, ‘w’);
fputcsv($output, array(‘ID’, ‘Date’, ‘Status’,’Name’));
$rows = mysql_query(‘SELECT ID,post_date,post_status,post_name FROM wp_posts WHERE post_date LIKE “%2016-03-30%”‘);
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);

And this is just a trail and here i only fetch data from post table.

But i need to connect to *postmeta* and other tables, so that i can get all information about order.
I see your code ,and i am very thankful to you . The same situation i faced now . But i don’t know how to integrate with this with my code.

Please help to complete this using your code.

Currently i have a plugin, but that is working slow, that’s why i making this page.

Hi Tushar, good question. I don’t have time right now to figure this out but I’ve always wanted to.

For this report you’d likely need to query:

1. wc order items table to get orders (order_id) that have particular product line item. It would be a search by product name, which is not ideal but easier than joining multiple tables to search by product ID.
2. Take those order_id’s to query wp_postmeta (post_id) to get the users email which is stored as meta_key = _customer_email (I think).

Hi,
i want to get all the order data like all this data :
Order ID,Client Email,Ship Type,Notes(or comments if any),Payment method(Stored Cards, net due on delivery,credit card etc),CreatedDate,Updated Date,Shipping Charge,Payment Status,Total Amount,Order Status (pending ,processed, completed,dispatched etc.),Payment Profile Id (this is related to authorize.net),Shipping Address, -Address1, -Address2, -shipping_country, -shipping_state, -postcode,
From woocommerce and want to create Json of all these values. can you let me know how can i get all this data.
regards,
Rohit

Hi Steve, thanks for your comments. Can you elaborate and give me an example of the join you have in mind? Or do you mean select? If so, I didn’t want a hundred sub-queries bogging it down. Let me know!

Good question. It’s been a while since I wrote this and it does seem weird to use max() on strings. I’m guessin I found an example on the interwebs and it worked and got me away from a bunch of sub-queries. I just played around with it again and removing max() returns NULL in every one of those fields. If you have any ideas on improving I’m happy to update. -J

Great script. I didn’t see where we could change the script for a particular ID though. Was that asked somewhere in comments? I’d love to be able to pull only one product and not all products if need be.

hai……
now i am developing the woo-commerce website ,i want to add the quick buy option without using plugin .now i create page template for quick buy option and its has same fields of checkout page ,tell me how to store the my new checkout form data into wordpress databse? .

Hi and happy thanks giving. I found this post when searching on how to export all of our customer database info and order info with SQL. I tried searching for the plugin mentioned but I believe the name has changed, any chance you could confirm what the current plugin is for this query? Is there a basic how to on how to run this script within the plugin?

Your code is awesome. I changed it a little to add some additional metafields I added but I was hoping to add to the products ordered at the end with a size variation. The meta_key value for that table is Size. Here is was I was trying:

I finally ended up with this code to get the items and how many of them listed :

SELECT oi.order_id AS order_id,
po.post_date AS date,
oi.order_item_name AS item,
CASE WHEN oim.meta_key = ‘_qty’ and oim.meta_value IS NOT NULL THEN oim.meta_value end as qty
FROM wp_woocommerce_order_items AS oi
LEFT JOIN wp_woocommerce_order_itemmeta AS oim
ON oi.order_item_id = oim.order_item_id
LEFT JOIN wp_posts AS po
ON po.id = oi.order_id
WHERE oim.meta_key = ‘_qty’ and
po.post_status ‘trash’
ORDER BY oi.order_item_id ASC

I don’t know if this is the clean way to do it but it gives me what i need. The next step would be mixing your query with this one to get the wp_postmeta field ont the same row but i didn’t find out how and even if it is possible.

Sorry for the multiple reply, i don’t know how to edit the previous one.
With the help of your code, I found it, thanks a lot :

SELECT oi.order_item_id AS order_item_id,
oi.order_id AS order_id,
oi.order_item_name AS item,
max( CASE WHEN oim.meta_key = ‘_qty’ and oi.order_item_id = oim.order_item_id THEN oim.meta_value END ) as qte,
max( CASE WHEN oim.meta_key = ‘_line_total’ and oi.order_item_id = oim.order_item_id THEN oim.meta_value END ) as item_total,
max( CASE WHEN pm.meta_key = ‘_billing_first_name’ and oi.order_id = pm.post_id THEN pm.meta_value END ) as nom,
max( CASE WHEN pm.meta_key = ‘_billing_email’ and oi.order_id = pm.post_id THEN pm.meta_value END ) as courriel,
max( CASE WHEN pm.meta_key = ‘_order_total’ and oi.order_id = pm.post_id THEN pm.meta_value END ) as order_total
FROM wp_woocommerce_order_items AS oi
LEFT JOIN wp_woocommerce_order_itemmeta AS oim
ON oi.order_item_id = oim.order_item_id
left join wp_postmeta AS pm on oi.order_id = pm.post_id
left join wp_posts AS p on oi.order_id = p.ID
GROUP BY oi.order_item_id ASC

Hi all,
reading some of your queries I created the one I’m using now, that query solves me a lot of problem, but not all:
1) the “product_item” field shows not only the product, but the product + attributes variation. How can I get these 2 parts in 2 separated columns?
2) the “order_total” field sometimes is incorrect and appear in a terrible format (exported in excel is a datetime :-/ )

Hi, We have a website where the user must prove they are over 18 by way of uploading ID photo to either the order or their user account. I am trying to link to the user_meta table and I’m using the below sql. However the values from this table always appear as null on my query when I know there is data in there. I’m not great with sql so any help would be much appreciated, here’s the 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,
max( CASE WHEN pm.meta_key = ‘_woo_ufdc_uploaded_file_path_1′ and p.ID = pm.post_id THEN pm.meta_value END ) as ID_upload,
max( CASE WHEN pm.meta_key = ‘_customer_user’ and p.ID = pm.post_id THEN pm.meta_value END) as customer_user,
max( CASE WHEN u.meta_key = ‘make_verify’ AND “customer_user” = u.user_id THEN u.meta_value END) as verified,
max( CASE WHEN u.meta_key = ‘user_uploaded_id’ AND “customer_user” = u.user_id THEN u.meta_value END) as user_upload,

[…] you run the risk of messing up your data if you don’t know what you’re doing. Here is a great blog post that can serve as a starting point for you, which includes an example of a SQL query that returns […]

Reductress
I crank out on-going web projects for Reductress. Super fun as you can imagine.

Need hosting?

Media Temple
My go to hosting provider. You get rockstar hosting and I make a little cash. No pressure, just clean honest fun.

Past work

How To Win At Feminism
The badasses over at Reductress are at it again with their new book How To Win At Feminism.

Nice Manners Music
Nice Manners is an LA based recording studio offering their clients all sorts of digital audio services. This is a demo of an internal genre based music search & player I built for them. I am ecstatic about launching this project—check it out.

Our Restroom
Our Restroom is an (inter)national crowdsourced campaign who’s goal is simple: compel businesses to make their single occupancy restrooms gender neutral. Championed by Kristin Russo, designed by Allison Weiss, built by me on WordPress.

Get Your Shit Together
This is a site dedicated to helping you get your shit together before an unexpected tragedy, like the loss of a loved one.

Trivial Beersuit
A site that provides professional pub trivia to bars and restaurants.

Audi YouTube Channel
This site was a quick turn-around hair pulling adventure for the Super Bowl: revamping Audi’s YouTube Channel.

Everyone Is Gay
Everyone Is Gay is an indispensable question and answer resource for the LGBTQ community. Hell, it’s great advice for anyone with a body with an emotion or two. I made the site responsive among other things. Give it a look see.

Teach.org
The new Teach.org. Providing tools to help students become teachers.