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
Posted in: Code Samples, Development  |  Tagged with: , , ,  |  Leave a comment
62 comments on “SQL Script To Get All WooCommerce Orders Including Metadata
  1. Pedro says:

    Thank you.

    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
    
  2. Adam says:

    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.

    • jibran says:

      Hi Adam, sorry it took me so long to respond to you. The correlation is p.ID in above query and order_id in wp_woocommerce_order_items.

      I figured out a solution to output pipe delimited product names to a new column, which demonstrates their relationship. Try this:

      ( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
      

      It doesn’t however provide you with quantity, price, etc. That information is in the wp_woocommerce_order_itemmeta (order_item_id is the column to key off).

      Hope that helps.

  3. How to use this in a foreach? Very good article 🙂

    • jibran says:

      Hi Marcos, take a look at $wpdb. Here’s a starting point.

      $rows = $wpdb->get_results( "select id, name from table" );
      foreach( $rows as $row ) { 
      var_dump($row)
      }
      

      Make sure to send your SQL statement through $wpdb->prepare, especially if you’re passing in user inputs.

      Hope that helps.

  4. peterkulcsar says:

    Hi,

    How can I get the ordered items meta datas with this SQL query? 🙂

  5. Jens says:

    Thanks for the script, it saved me a ton of time. Very helpful!

  6. Pedro Alonso says:

    You saved bro! I put as a view in MySQL to get this information more easily.

  7. Hi!

    I’m really a noob in SQL, and I would like to know how to export all of these data to a CSV file… Thank you 🙂

  8. sam says:

    Hi there,

    I’m keen to get the following data extracted – can you guide me?

    order id, product category like shirt, MRP, Discount %, Actual sale price, dispatch date, courier tracking id, courier company, last update date, last update by (user).

    Most are possible, but some aren’t.

    Thanks!
    Sam

  9. Manik says:

    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.

  10. tushar attar says:

    Hi can you please tell me how to export email Id of customers who have purchased a specific porduct. Thanks

    • jibran says:

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

      Hope this helps.

  11. Rohit Gomgee says:

    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

  12. Joko Auditis says:

    cool…. thanks jibran

  13. Louie says:

    thanks very handy, gave me a good starting point for another woo query I needed to write

  14. Steve says:

    Hi – just wondering if each of the lines beginning with max, e.g.

    max( CASE WHEN pm.meta_key = ‘_billing_email’ and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,

    could be replaced with a join statement instead?

    • jibran says:

      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!

  15. Steve says:

    Also just curious – why do you use the Max function on text strings like the email address, first name etc?

    • jibran says:

      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

  16. Stan says:

    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.

    Thanks.

    • jibran says:

      Hi Stan, I haven’t been able to query by product ID, but I just made an update to query by product name. See above. Let me know if there are any issues. -J

  17. 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? .

  18. Jakob Smith says:

    With inspiration from this post, I managed to put together an SQL script, that exports completed orders from pre-2.2 versions of WooCommerce. I’ve written about it here:

    https://www.omkalfatring.dk/export-orders-to-csv-from-woocommerce-versions-before-2-2/

  19. Aiman Yuuhi says:

    Thank you very much. I’ve been looking for this.. Hugs & Kisses from me.. XD

  20. Adarsh says:

    How can I get coupons code that order has been used !

  21. Cris says:

    Using left join is much faster

    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 pwp_woocommerce_order_items where order_id = p.ID ) as order_items
    FROM
    `pwp_posts` p LEFT JOIN pwp_postmeta pm ON (pm.post_id = p.ID )
    LEFT JOIN pwp_woocommerce_order_items oi ON (p.ID = oi.order_id)

    WHERE
    p.post_type = ‘shop_order’
    group by p.ID

  22. Myles says:

    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?

    Thanks!

  23. Shauny Poo says:

    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:

    select
    p.ID as order_id,
    p.post_date,
    p.post_status,
    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 = ‘Pickup Persons First Name’ and p.ID = pm.post_id THEN pm.meta_value END ) as pickup_persons_first_name,
    max( CASE WHEN pm.meta_key = ‘Pickup Persons Last Name’ and p.ID = pm.post_id THEN pm.meta_value END ) as pickup_persons_last_name,
    max( CASE WHEN pm.meta_key = ‘Troop Number’ and p.ID = pm.post_id THEN pm.meta_value END ) as troop_number,
    ( select group_concat( CONCAT(order_item_name,’ ‘, (CASE WHEN oim.meta_key = ‘Size’ and oi.order_item_id = oim.order_item_id THEN oim.meta_value End)) 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
    join wp_woocommerce_order_itemmeta oim on oi.order_item_id = oim.order_item_id

    group by
    p.ID

  24. Jerome says:

    Thanks a lot for this wonderful code !

    Instead of the pipe delimited product names, how do you think it would be possible that each product got is own line ? Repeating the order meta (ID, billing, etc) could achieve this I think.

    • Jerome says:

      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.

      Anyway, I can continue coding with these requests. Thanks a lot!

      • Jerome says:

        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

  25. Bianca says:

    This is what I used to select meta contents and order number. Other cols can be added.

    SELECT
    a.order_item_id,
    a.order_id,
    b.order_item_id,
    b.meta_key,
    b.meta_value
    FROM wp_woocommerce_order_items a LEFT JOIN
    wp_woocommerce_order_itemmeta b on a.order_item_id = b.order_item_id WHERE b.meta_key LIKE ‘%Bottle%’

  26. Bobby says:

    Hi everybody this thread has been super useful, thanks!

    I’m having a bit of trouble creating this table. My “order Items” don’t show up. I don’t think I’m doing the JOIN correctly, would someone be able to set me straight?

    I’m hoping to get a table with First Name, Last Name, Order Total, and Order Item.

    Thanks in advance!

    SELECT
    p.ID as order_id,
    p.post_date,
    max( CASE WHEN pm.meta_key = ‘_billing_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as ‘First Name’,
    max( CASE WHEN pm.meta_key = ‘_billing_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as ‘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_item_name’ and p.ID = pm.post_id THEN oi.order_item_name END ) as ‘Order Item’,
    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_term_relationships AS wptr,
    wp_postmeta AS pm

    INNER JOIN wp_woocommerce_order_items as oi ON pm.post_id = oi.order_id

    WHERE 1=1 AND

    post_type = ‘shop_order’ AND p.ID = pm.post_id

    GROUP BY

    p.ID DESC

  27. Federico says:

    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 :-/ )

    Could you please help me in fix the issues above?

  28. Federico says:

    select
    p.post_date,
    max( CASE WHEN oim.meta_key = ‘_qty’ THEN oim.meta_value END ) as qty,
    max( CASE WHEN pm.meta_key = ‘_billing_first_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as first_name,
    max( CASE WHEN pm.meta_key = ‘_billing_last_name’ and p.ID = pm.post_id THEN pm.meta_value END ) as last_name,
    max( CASE WHEN pm.meta_key = ‘_billing_email’ and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
    min(order_item_name) as product_item,
    ( select group_concat( order_item_name separator ‘|’ ) from wper_2_woocommerce_order_items where order_id = p.ID ) as order_items,
    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 = ‘PayPal Transaction Fee’ and p.ID = pm.post_id THEN pm.meta_value END ) as Paypal_fee,
    max( CASE WHEN pm.meta_key = ‘_cart_discount’ and p.ID = pm.post_id THEN pm.meta_value END ) as cart_discount,
    max( CASE WHEN pm.meta_key = ‘_order_key’ and p.ID = pm.post_id THEN pm.meta_value END ) as order_key,
    p.ID as order_id,
    max( CASE WHEN pm.meta_key = ‘_payment_method’ and p.ID = pm.post_id THEN pm.meta_value END ) as payment_method,
    max( CASE WHEN pm.meta_key = ‘_cart_hash’ and p.ID = pm.post_id THEN pm.meta_value END ) as cart_hash,
    max( CASE WHEN pm.meta_key = ‘_transaction_id’ and p.ID = pm.post_id THEN pm.meta_value END ) as transaction_id,
    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 oim.meta_key = ‘_product_id’ THEN oim.meta_value END ) as product_id,
    max( CASE WHEN oim.meta_key = ‘_variation_id’ THEN oim.meta_value END ) as variation_id,
    post_status,
    CAST(DATE_FORMAT(p.post_date,’%Y%m%d’) AS UNSIGNED) as yearmonthday
    from
    wper_2_posts p
    join wper_2_postmeta pm on p.ID = pm.post_id
    join wper_2_woocommerce_order_items oi on p.ID = oi.order_id
    join wper_2_woocommerce_order_itemmeta oim on oi.order_item_id = oim.order_item_id
    group by
    p.ID
    having yearmonthday > ‘20171016’

  29. cobracartech says:

    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,

    ( select group_concat( order_item_name separator ‘|’ ) from wp_woocommerce_order_items where order_id = p.ID AND order_item_name != “UK Delivery”) as order_items
    from
    wp_posts p
    LEFT join wp_postmeta pm on p.ID = pm.post_id
    LEFT join wp_woocommerce_order_items oi on p.ID = oi.order_id
    LEFT join wp_usermeta u on “customer_user” = u.user_id
    WHERE

    p.post_status = ‘wc-processing’ AND
    oi.order_id > 54930
    GROUP BY p.ID

  30. Jimmy says:

    Hi, I have SQL below that works when I ran it from mySQL query but I get BAD query when running it with php. Can someone help me change it to php?

    select
    p.ID as order_id,
    p.post_date,post_status,
    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 oim.meta_key = ‘_qty’ and oi.order_item_id = oi.order_item_id THEN oim.meta_value END ) as Qty,
    ( 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
    join wp_woocommerce_order_itemmeta oim on oi.order_item_id = oim.order_item_id
    where
    post_type = ‘shop_order’
    group by
    p.ID

  31. 45squaredgr says:

    So how do you take this and import it back into a new database?

  32. SIdeUP says:

    Good job! How can I get with this query all the orders of a certain customer id?

  33. conschneider says:

    Thanks a lot for the snippet! ?

  34. Dave Lovely says:

    This was so amazingly helpful!! Thanks!

  35. Thank you, this saved me a lot of time!

  36. infocaptor says:

    Excellent, I am collecting and publishing all related SQL queries https://www.crawlspider.com/where-to-find-products-in-woocommerce-database-tables/

  37. Paul says:

    Thank you! This helped me alot in a pinch!

9 Pings/Trackbacks for "SQL Script To Get All WooCommerce Orders Including Metadata"
  1. […] this bit of sql is based on this post at http://codecharismatic.com/ […]

  2. […] 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 […]

  3. […] I came across this post: SQL Script To Get All WooCommerce Orders Including Metadata […]

  4. […] possible) and group the quantity of the product purchased by user_id. I found this code previously (http://codecharismatic.com/sql-script-to-get-all-woocommerce-orders-including-metadata/) but have been unable to at least generate the table I would like to […]

  5. […] found this code, but have been unable to generate the table I would need. In future, I would like to add the […]

  6. […] and 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 […]

  7. […] this bit of sql is based on this post at http://codecharismatic.com/ […]

  8. […] like which products were ordered and how many, you’ll still have to export that information directly from the database or use a plugin to handle your […]

  9. […] which merchandise had been ordered and what number of, you’ll nonetheless should export that data instantly from the database or use a plugin to deal with your […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*