wc-mysql-queries.md

MySQL script to get all WooCommerce orders including metadata

SET @date_start = '2021-02-12' ;
SELECT
    p.ID as order_id,
    MAX( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as name,
    MAX( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as lastname,
    MAX( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as email,
    MAX( CASE WHEN pm.meta_key = '_billing_phone' and p.ID = pm.post_id THEN pm.meta_value END ) as phone,
    p.post_excerpt AS comment,
    MAX( CASE WHEN pm.meta_key = '_payment_method_title' and p.ID = pm.post_id THEN pm.meta_value END ) as payment,
    post_date AS pub_date,
    MAX( CASE WHEN oi.order_item_type = 'shipping' and p.ID = oi.order_id THEN oi.order_item_name END ) as deliveryservice
FROM
    prefix_posts p 
JOIN 
    prefix_postmeta pm 
ON 
    p.ID = pm.post_id
JOIN 
    prefix_woocommerce_order_items oi 
ON 
    p.ID = oi.order_id
WHERE
    post_type = 'shop_order' 
    AND 
    DATE(post_date) > @date_start 
    AND
    post_status = 'wc-completed' 
GROUP BY
    p.ID ;

MySQL script to get WooCommerce order details

SET @order_id = '10462' ;

SELECT
    orderdetails.*,
    sku.meta_value AS product_sku
FROM (
        
        SELECT 
                MAX(IF (order_itemmeta.meta_key = "_product_id",order_itemmeta.meta_value, 0)) AS product_id,
                MAX(IF (order_itemmeta.meta_key = "_qty",order_itemmeta.meta_value, 0)) AS count,
                MAX(IF (order_itemmeta.meta_key = "_line_total",order_itemmeta.meta_value, 0)) AS price,
                woocommerce_order_items.order_item_name AS idname
        FROM
                prefix_posts posts
        JOIN 
                prefix_woocommerce_order_items woocommerce_order_items
        ON 
                posts.id = woocommerce_order_items.order_id
                AND 
                woocommerce_order_items.order_item_type = "line_item"
        JOIN 
                prefix_woocommerce_order_itemmeta order_itemmeta
        ON 
                woocommerce_order_items.order_item_id = order_itemmeta.order_item_id 
        WHERE 
                 posts.ID = @order_id
        GROUP BY posts.id, woocommerce_order_items.order_item_id
     ) AS orderdetails
LEFT JOIN
    prefix_postmeta sku
ON 
    sku.post_id = orderdetails.product_id
    AND 
    sku.meta_key = '_sku';