Skip to navigation
Query woocommerce db to get all orders with product
19.12.25
``` SELECT p.ID AS order_id, p.post_date AS order_date, -- Combine first and last name for a full name CONCAT( MAX(CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END), ' ', MAX(CASE WHEN pm.meta_key = '_billing_last_name' THEN pm.meta_value END) ) AS customer_name, MAX(CASE WHEN pm.meta_key = '_billing_email' THEN pm.meta_value END) AS customer_email, oim.meta_value AS product_id, oi.order_item_name AS product_name FROM wp_posts p 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 JOIN wp_postmeta pm ON p.ID = pm.post_id WHERE p.post_type = 'shop_order' AND p.post_status NOT IN ('trash', 'wc-cancelled') AND oim.meta_key = '_product_id' AND pm.meta_key IN ('_billing_first_name', '_billing_last_name', '_billing_email') GROUP BY oi.order_item_id ORDER BY p.ID DESC; ```
Reply
Anonymous
``` SELECT p.ID AS order_id, p.post_date AS order_date, -- Combine first and last name for a full name CONCAT( MAX(CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END), ' ', MAX(CASE WHEN pm.meta_key = '_billing_last_name' THEN pm.meta_value END) ) AS customer_name, MAX(CASE WHEN pm.meta_key = '_billing_email' THEN pm.meta_value END) AS customer_email, -- Product Details MAX(CASE WHEN oim.meta_key = '_product_id' THEN oim.meta_value END) AS product_id, oi.order_item_name AS product_name, -- Quantity and Total MAX(CASE WHEN oim.meta_key = '_qty' THEN oim.meta_value END) AS qty, MAX(CASE WHEN oim.meta_key = '_line_total' THEN oim.meta_value END) AS line_total FROM wp_posts p 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 JOIN wp_postmeta pm ON p.ID = pm.post_id WHERE p.post_type = 'shop_order' AND p.post_status NOT IN ('trash', 'wc-cancelled', 'wc-refunded') AND pm.meta_key IN ('_billing_first_name', '_billing_last_name', '_billing_email') GROUP BY oi.order_item_id ORDER BY p.ID DESC; ```
19.12.25
Reply
Anonymous
Information Epoch 1766528946
There is no perfect productivity system.
Home
Notebook
Contact us