Skip to content

Instantly share code, notes, and snippets.

@aidik
Forked from lukecav/Query
Last active January 24, 2022 17:20
Show Gist options
  • Save aidik/ca0e02d5f25a958ff8143578c7a78836 to your computer and use it in GitHub Desktop.
Save aidik/ca0e02d5f25a958ff8143578c7a78836 to your computer and use it in GitHub Desktop.
Improved MySQL/Maria script to get all WooCommerce orders including more metadata and better data types
SELECT `p`.`id` AS `order_id`,
Cast(`p`.`post_date` AS datetime) AS `order_date`,
SUBSTRING(`p`.`post_status`, 4) AS `order_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_company' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value` end) AS `_billing_company`,
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_country' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value` end) AS `_billing_country`,
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_company' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value` end) AS `_shipping_company`,
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_country' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value` end) AS `_shipping_country`,
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` = '_payment_method_title' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value` end) AS `_payment_method_title`,
Cast(Max(CASE WHEN `pm`.`meta_key` = '_order_total' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value` end) AS DECIMAL(16, 2)) AS `order_total`,
Cast(Max(CASE WHEN `pm`.`meta_key` = '_order_tax' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value` end) AS DECIMAL(16, 2)) AS `order_tax`,
Cast(Max(CASE WHEN `pm`.`meta_key` = '_order_shipping' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value` end) AS DECIMAL(16, 2)) AS `_order_shipping`,
Cast(Max(CASE WHEN `pm`.`meta_key` = '_order_shipping_tax' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value` end) AS DECIMAL(16, 2)) AS `_order_shipping_tax`,
Cast(Max(CASE WHEN `pm`.`meta_key` = '_paid_date' AND `p`.`id` = `pm`.`post_id` THEN `pm`.`meta_value` end) AS datetime) AS `paid_date`,
(SELECT Group_concat(`wp_woocommerce_order_items`.`order_item_name` SEPARATOR '|')
FROM `wp_woocommerce_order_items`
WHERE `wp_woocommerce_order_items`.`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 `p`.`post_type` = 'shop_order'
GROUP BY `p`.`id`
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment