Woocomerce-获得所有用户的sku和数量订阅

Question

im当前需要一个sql，它为我获取用户的当前订阅。为了识别它，我只需要产品的userid、数量和sku。我试了几天，但数据库结构一团糟，我似乎找不到合适的解决方案。

我目前的方法是，有两种观点，一种是他们购买的订单，另一种是订单的数量。这些观点是一致的，但似乎缺少一些结果。

这是我的采购订单sql，有人能告诉我正确的方向吗？

Create view bought_orders as SELECT distinct products.sku,
postmeta.meta_value        as user_id,
#order_item_meta.meta_key, #as quantity,
order_items.order_id
FROM wp_woocommerce_order_items as order_items
LEFT JOIN
wp_woocommerce_order_itemmeta as order_item_meta
ON order_items.order_item_id = order_item_meta.order_item_id
LEFT JOIN
wp_posts AS posts
ON order_items.order_id = posts.ID
LEFT JOIN
wp_postmeta AS postmeta
ON posts.id = postmeta.post_id
LEFT JOIN (
SELECT id        AS product_id,
post_name AS sku
FROM wp_posts
WHERE post_type = 'product'
) AS products ON products.product_id = order_item_meta.meta_value
WHERE posts.post_type = 'shop_subscription'
AND posts.post_status = 'wc-active'
AND order_items.order_item_type = 'line_item'
AND order_item_meta.meta_key = '_product_id'
AND postmeta.meta_key = '_customer_user';

Answer 1

坦率地说，我是根据其他地方的代码将其破解的，我相信它可以配对，效率更高，但它现在对我有效。。。

global $wpdb;
$total = $wpdb->get_var( "SELECT sum(subscription_line_items.qty)

FROM {$wpdb->prefix}posts AS product 

LEFT JOIN ( 
SELECT wcoitems.order_id AS subscription_id, wcoimeta.meta_value AS product_id,
wcoimeta.order_item_id, wcoimeta3.meta_value AS qty 
FROM {$wpdb->prefix}woocommerce_order_items AS wcoitems 
INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS wcoimeta 
ON wcoimeta.order_item_id = wcoitems.order_item_id 
INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS wcoimeta3 
ON wcoimeta3.order_item_id = wcoitems.order_item_id 
WHERE wcoitems.order_item_type = 'line_item' 
AND wcoimeta.meta_key = '_product_id' 
AND wcoimeta3.meta_key = '_qty' 
) AS subscription_line_items ON product.id = subscription_line_items.product_id 

LEFT JOIN (
SELECT DISTINCT({$wpdb->prefix}postmeta.post_id) AS wppm_id
FROM {$wpdb->prefix}postmeta

) AS subscription_meta ON subscription_meta.wppm_id = subscription_line_items.subscription_id

LEFT JOIN {$wpdb->prefix}posts AS subscriptions 
ON subscriptions.ID = subscription_line_items.subscription_id 

WHERE product.post_type = 'product' 
AND subscriptions.post_type = 'shop_subscription' 
AND subscriptions.post_status = 'wc-active'");