时间戳

首页 > 解决方案 > Woocommerce 循环中每个订单项目的总计数

php - Woocommerce 循环中每个订单项目的总计数

问题描述

嗨，我正在同时学习 PHP 和 Woocommerce！我正在尝试获取所有具有状态处理的订单以及每个订单的总数并将其显示在页面上。

到目前为止，我可以遍历所有订单并获取每个订单的名称和数量。

但由于我不知道哪个产品将出现在列表中，我不确定如何比较名称然后添加数量。

我当前的输出是这样的：

我想要的是：

目前的代码是：

    <?php
/*
Template Name: Print Supplier Order
*/
if (!is_user_logged_in() || !current_user_can('manage_options')) wp_die('This page is private.');
?>
<!DOCTYPE HTML>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <title><?php _e('Processing Orders'); ?></title>
    <style>
        body { background:white; color:black; width: 95%; margin: 0 auto; }
    </style>
</head>
<body>
    <header>
        <?php if (have_posts()) : while (have_posts()) : the_post(); ?>

            <h1 class="title"><?php the_title(); ?></h1>

            <?php the_content(); ?>

        <?php endwhile; endif; ?>
    </header>
    <section>
    <?php 

    global $woocommerce;

$args = array( 'post_type' => 'shop_order', 'post_status' => 'wc-processing', 'posts_per_page' => -1 );


    $loop = new WP_Query( $args );

    while ( $loop->have_posts() ) : $loop->the_post();

        $order_id = $loop->post->ID;

        $order = new WC_Order($order_id);

        $product_details = array();
        $order_items = $order->get_items();
        foreach( $order_items as $product ) {
                echo $product['name']." x ".$product['qty'];
                echo '<br>';

            }
        ?>
    <?php endwhile; ?>
    </section>
</body>
</html>

标签： phpsqlwordpresswoocommerceorders

解决方案

更新 （现在 SQL 查询也给出了产品名称，使它更轻）

与其使用 aWP_Query和一些繁重的代码来进行计算，不如使用 Class （SQL 查询）使用这个更轻量级和有效的版本代码：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>';
}

测试和工作。

推荐阅读