Woocommerce查询用户有效订阅sku、数量的SQL编写问题求助

现有代码存在的问题 #

• 产品SKU取值逻辑错误：WooCommerce产品SKU存储在wp_postmeta表的_sku字段，而非wp_posts的post_name字段，会导致SKU匹配错误
• 缺少购买数量字段：购买数量存储在订单项元数据表的_qty键下，现有逻辑仅筛选了_product_id的元数据，无法同时获取数量
• 表关联逻辑冗余：没必要拆分两个视图关联，单条查询通过多次关联订单项元数据表即可拿到所需字段
• LEFT JOIN被WHERE条件隐式转为INNER JOIN：WHERE子句中对右表字段的非空过滤，会导致LEFT JOIN失去作用，部分关联不到的数据直接被过滤

优化后的查询语句 #

直接用以下语句创建视图即可拿到你需要的三个字段：

CREATE VIEW bought_orders AS
SELECT 
    pm_customer.meta_value AS user_id,
    pm_sku.meta_value AS product_sku,
    oim_qty.meta_value AS purchase_quantity,
    oi.order_id
FROM wp_woocommerce_order_items oi
-- 关联订阅订单主表，过滤有效订阅
INNER JOIN wp_posts p 
    ON oi.order_id = p.ID
    AND p.post_type = 'shop_subscription'
    AND p.post_status = 'wc-active'
-- 关联订单项类型，只取商品行
INNER JOIN wp_woocommerce_order_itemmeta oim_product
    ON oi.order_item_id = oim_product.order_item_id
    AND oim_product.meta_key = '_product_id'
-- 关联购买数量
INNER JOIN wp_woocommerce_order_itemmeta oim_qty
    ON oi.order_item_id = oim_qty.order_item_id
    AND oim_qty.meta_key = '_qty'
-- 关联对应用户ID
INNER JOIN wp_postmeta pm_customer
    ON p.ID = pm_customer.post_id
    AND pm_customer.meta_key = '_customer_user'
-- 关联产品SKU
INNER JOIN wp_postmeta pm_sku
    ON oim_product.meta_value = pm_sku.post_id
    AND pm_sku.meta_key = '_sku'
WHERE oi.order_item_type = 'line_item'
GROUP BY oi.order_item_id;

字段说明 #

• user_id：对应订阅用户的ID
• product_sku：订阅产品的真实SKU
• purchase_quantity：对应产品的购买数量
• order_id：关联的订阅订单ID，不需要可以在SELECT中删除

内容的提问来源于stack exchange，提问作者Ditscheridou