SQL Query to Retrieve Woocommerce Order with Product Detail

Woocommerce order detail is saved in several tables which could be tricky to retrieve, this snippet may be handy to help you out

SQL Query to Retrieve Woocommerce Order with Product Detail
Photo by Toa Heftiba / Unsplash

Code

select
    p.ID as order_id,
    p.post_date,
    ids.ids,
    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 = '_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
    join
    (select order_id, group_concat( product_id separator '|' ) ids from
    (select i.order_item_name, i.order_id, im.meta_value product_id, im.meta_key FROM
    wp_woocommerce_order_items i join wp_woocommerce_order_itemmeta im
    where im.meta_key = '_product_id' and i.order_item_id = im.order_item_id) pds
    group by order_id) ids on p.ID = ids.order_id
where
    post_type = 'shop_order' and
    post_date BETWEEN '2023-04-01' AND '2023-05-01' and
    (post_status = 'wc-completed' OR post_status = 'wc-processing' )
group by
    p.ID;

reference

where clause for order with certain product IDs

replace PRODUCT_ID1&PRODUCT_ID2 to suit your needs

 ids REGEXP '.*(PRODUCT_ID1|PRODUCT_ID2).*'