Woocommerce循环中每个订单商品的总计数

已更新 （现在，SQL查询还提供了产品名称，使它更轻巧）

与其使用

WP_Query

WPDB

global $wpdb;$results = $wpdb->get_results( "    SELECt DISTINCT woim2.meta_value as id, SUM(woim.meta_value) as count, woi.order_item_name as name    FROM {$wpdb->prefix}woocommerce_order_itemmeta as woim    INNER JOIN {$wpdb->prefix}woocommerce_order_items as woi ON woi.order_item_id = woim.order_item_id    INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta as woim2 ON woi.order_item_id = woim2.order_item_id    INNER JOIN {$wpdb->prefix}posts as p ON p.ID = woi.order_id    WHERe p.post_status IN ('wc-processing','wc-on-hold')    AND woim.meta_key LIKE '_qty'    AND woim2.meta_key LIKE '_product_id'    GROUP BY woim2.meta_value" );foreach( $results as $result ){    echo $result->name . ' (' . $result->id . ') x ' . $result->count . '<br>';}

产品数量将仅基于加工产品状态而不是产品总销售额。

经过测试和工作。

而是获取产​​品变体 （如您的评论中所述）

根据您的评论中的要求以获取订单中的产品变型，您将替换该行：

 AND woim2.meta_key LIKE '_product_id'

通过以下行：

 AND woim2.meta_key LIKE '_variation_id'

获取所有产品和版本（不包括可变产品）

要获得所有产品（包括产品变体， 但不包括可变产品），请 使用：

global $wpdb;$results = $wpdb->get_results( "    SELECt DISTINCT woim2.meta_value as id, SUM(woim.meta_value) as count, woi.order_item_name as name    FROM {$wpdb->prefix}woocommerce_order_itemmeta as woim    INNER JOIN {$wpdb->prefix}woocommerce_order_items as woi ON woi.order_item_id = woim.order_item_id    INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta as woim2 ON woi.order_item_id = woim2.order_item_id    INNER JOIN {$wpdb->prefix}posts as p ON p.ID = woi.order_id    WHERe p.post_status IN ('wc-processing','wc-on-hold')     AND woim.meta_key LIKE '_qty'    AND ((woim2.meta_key LIKE '_variation_id' AND woim2.meta_value > 0)    OR (woim2.meta_key LIKE '_product_id'    AND woim2.meta_value NOT IN (SELECt DISTINCT post_parent FROM {$wpdb->prefix}posts WHERe post_type LIKE 'product_variation')))    GROUP BY woim2.meta_value" );foreach( $results as $result ){    echo $result->name . ' (' . $result->id . ') x ' . $result->count . '<br>';}

经过测试和工作。

要获得它们的全部信息 （甚至包括产品变体和可变产品） ：

global $wpdb;$results = $wpdb->get_results( "    SELECt DISTINCT woim2.meta_value as id, SUM(woim.meta_value) as count, woi.order_item_name as name    FROM {$wpdb->prefix}woocommerce_order_itemmeta as woim    INNER JOIN {$wpdb->prefix}woocommerce_order_items as woi ON woi.order_item_id = woim.order_item_id    INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta as woim2 ON woi.order_item_id = woim2.order_item_id    INNER JOIN {$wpdb->prefix}posts as p ON p.ID = woi.order_id    WHERe p.post_status IN ('wc-processing','wc-on-hold')    AND woim.meta_key LIKE '_qty'    AND ((woim2.meta_key LIKE '_variation_id' AND woim2.meta_value > 0)    OR woim2.meta_key LIKE '_product_id' )    GROUP BY woim2.meta_value" );foreach( $results as $result ){    echo $result->name . ' (' . $result->id . ') x ' . $result->count . '<br>';}

经过测试和工作。