MySQLPHPWooCommerceWordPress

WooCommerce Fetch All Orders by Date Range

0
2.2k
2017 / 11 / 03

Some times we need to display the reports of our WooCommerce store in custom way. I have searched a lot over the internet and find a SQL query that fetches all orders and its meta based on 2 dates range.

You can change the query based on your table prefix (“wp_” in my case) and modify query as per your needs.

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 '2017-11-01' AND '2017-11-10'
GROUP BY
    p.ID

 

It can be useful when you are going to implement custom reports.

Hi, I am a full-stack web developer with 5+ years of experience in working with different web technologies. Do you want to ask something? just send me a message through the contact form. Please visit my portfolio at hamzamehmood.com. Thanks