定位WooCommerce Export功能的查询

Question

我希望编辑查询用于WooCommerce内的'CSV Export'功能，我已经登录到我的phpMyAdmin部分，似乎无法理解它。

有人能帮忙吗?

Answer 1

对于将来想要这样做的人，下面是我从导出csv函数中得到的大部分值:

SELECT t.*
FROM
(
    SELECT
        pm.post_id as 'order_id',
        p.post_date as 'order_date',
        p.post_status as 'order_status',
        MAX(CASE WHEN pm.meta_key = '_order_shipping' THEN pm.meta_value END) as 'order_shipping',
        MAX(CASE WHEN pm.meta_key = '_order_shipping_tax' THEN pm.meta_value END) as 'order_shipping_tax',
        MAX(CASE WHEN pm.meta_key = '_order_tax' THEN pm.meta_value END) as 'order_tax',
        MAX(CASE WHEN pm.meta_key = '_cart_discount' THEN pm.meta_value END) as 'cart_discount',
        MAX(CASE WHEN pm.meta_key = '_order_total' THEN pm.meta_value END) as 'order_total',
        MAX(CASE WHEN pm.meta_key = '_payment_method' THEN pm.meta_value END) as 'payment_method',
        oi.order_item_name as 'shipping_type',
        MAX(CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END) as 'billing_first_name',
        MAX(CASE WHEN pm.meta_key = '_billing_last_name' THEN pm.meta_value END) as 'billing_last_name',
        MAX(CASE WHEN pm.meta_key = '_billing_email' THEN pm.meta_value END) as 'billing_email',
        MAX(CASE WHEN pm.meta_key = '_billing_phone' THEN pm.meta_value END) as 'billing_phone' ,
        MAX(CASE WHEN pm.meta_key = '_billing_address_1' THEN pm.meta_value END) as 'billing_address1',
        MAX(CASE WHEN pm.meta_key = '_billing_address_2' THEN pm.meta_value END) as 'billing_address2',
        MAX(CASE WHEN pm.meta_key = '_billing_postcode' THEN pm.meta_value END) as 'billing_postcode',
        MAX(CASE WHEN pm.meta_key = '_billing_city' THEN pm.meta_value END) as 'billing_city',
        MAX(CASE WHEN pm.meta_key = '_billing_state' THEN pm.meta_value END) as 'billing_state',
        MAX(CASE WHEN pm.meta_key = '_billing_country' THEN pm.meta_value END) as 'billing_country',
        MAX(CASE WHEN pm.meta_key = '_billing_company' THEN pm.meta_value END) as 'billing_company',
        MAX(CASE WHEN pm.meta_key = '_shipping_first_name' THEN pm.meta_value END) as 'shipping_first_name',
        MAX(CASE WHEN pm.meta_key = '_shipping_last_name' THEN pm.meta_value END) as 'shipping_last_name',
        MAX(CASE WHEN pm.meta_key = '_shipping_address_1' THEN pm.meta_value END) as 'shipping_address1',
        MAX(CASE WHEN pm.meta_key = '_shipping_address_2' THEN pm.meta_value END) as 'shipping_address2',
        MAX(CASE WHEN pm.meta_key = '_shipping_postcode' THEN pm.meta_value END) as 'shipping_postcode',
        MAX(CASE WHEN pm.meta_key = '_shipping_city' THEN pm.meta_value END) as 'shipping_city',
        MAX(CASE WHEN pm.meta_key = '_shipping_state' THEN pm.meta_value END) as 'shipping_state',
        MAX(CASE WHEN pm.meta_key = '_shipping_country' THEN pm.meta_value END) as 'shipping_country',
        MAX(CASE WHEN pm.meta_key = '_shipping_company' THEN pm.meta_value END) as 'shipping_company',
        t.items
    FROM
        wp_postmeta pm  INNER JOIN wp_posts p ON p.ID = pm.post_id AND  p.post_type = 'shop_order'
        INNER JOIN wp_woocommerce_order_items oi on oi.order_id = pm.post_id AND oi.order_item_type = 'shipping'
        INNER JOIN (SELECT order_id, GROUP_CONCAT(order_item_name SEPARATOR ' | ') as 'items' FROM wp_woocommerce_order_items oi1 WHERE oi1.order_item_type = 'line_item' group by order_id) as t ON t.order_ID = pm.post_id 
    GROUP BY
        pm.post_id
) as t
WHERE
    t.billing_email IS NOT NULL