Skip to content

Instantly share code, notes, and snippets.

@aidik

aidik/Query

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.
Download ZIP

Revisions

  1. aidik revised this gist Nov 8, 2021. 1 changed file with 40 additions and 32 deletions.
    72 changes: 40 additions & 32 deletions Query
    View file
    Original file line number Diff line number Diff line change
    @@ -1,32 +1,40 @@
    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 '2021-01-01' AND '2021-08-01' and
    post_status = 'wc-completed'
    group by
    p.ID
    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`


  2. @lukecav lukecav revised this gist Aug 23, 2021. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion Query
    View file
    Original file line number Diff line number Diff line change
    @@ -26,7 +26,7 @@ from
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
    where
    post_type = 'shop_order' and
    post_date BETWEEN '2020-01-01' AND '2020-12-31' and
    post_date BETWEEN '2021-01-01' AND '2021-08-01' and
    post_status = 'wc-completed'
    group by
    p.ID
  3. @lukecav lukecav revised this gist Jan 6, 2021. 1 changed file with 2 additions and 3 deletions.
    5 changes: 2 additions & 3 deletions Query
    View file
    Original file line number Diff line number Diff line change
    @@ -26,8 +26,7 @@ from
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
    where
    post_type = 'shop_order' and
    post_date BETWEEN '2015-01-01' AND '2015-07-08' and
    post_status = 'wc-completed' and
    oi.order_item_name = 'Product Name'
    post_date BETWEEN '2020-01-01' AND '2020-12-31' and
    post_status = 'wc-completed'
    group by
    p.ID
  4. @lukecav lukecav created this gist Sep 14, 2017.
    33 changes: 33 additions & 0 deletions Query
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,33 @@
    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 '2015-01-01' AND '2015-07-08' and
    post_status = 'wc-completed' and
    oi.order_item_name = 'Product Name'
    group by
    p.ID