HPOS兼容：SQL查询WooCommerce指定状态订单数及金额异常排查

问题概述 #

开发自定义WordPress插件，计划通过SQL查询在后台展示WooCommerce订单统计（已完成、处理中订单的总数/总金额，已取消订单的数量/金额），但当前统计数据错误或完全不显示。

预期输出：

• 订单总数：[预期数值]
• 订单总金额：[预期金额]
• 已取消订单数：[预期数值]
• 已取消订单总金额：[预期金额]

原代码 #

<?php
/*
Plugin Name: WooCommerce Order Stats
Description: Displays order statistics for WooCommerce in the WordPress admin panel.
Version: 1.0
Author: Your Name
*/

// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

// Function to display WooCommerce order statistics
function display_woocommerce_order_stats_admin_notice() {
    global $wpdb;
    $table_prefix = $wpdb->prefix;
    $orders_table = "{$table_prefix}wc_orders"; // WooCommerce orders table name

    // Query total number of completed orders
    $total_completed_orders = $wpdb->get_var("
        SELECT COUNT(ID)
        FROM {$orders_table}
        WHERE post_status = 'wc-completed'
    ");

    // Query total amount for completed orders
    $total_completed_amount = $wpdb->get_var("
        SELECT SUM(meta.meta_value)
        FROM {$orders_table} AS orders
        INNER JOIN {$table_prefix}postmeta AS meta ON orders.ID = meta.post_id
        WHERE orders.post_status = 'wc-completed'
        AND meta.meta_key = '_order_total'
    ");

    // Query total number of processing orders
    $total_processing_orders = $wpdb->get_var("
        SELECT COUNT(ID)
        FROM {$orders_table}
        WHERE post_status = 'wc-processing'
    ");

    // Query total amount for processing orders
    $total_processing_amount = $wpdb->get_var("
        SELECT SUM(meta.meta_value)
        FROM {$orders_table} AS orders
        INNER JOIN {$table_prefix}postmeta AS meta ON orders.ID = meta.post_id
        WHERE orders.post_status = 'wc-processing'
        AND meta.meta_key = '_order_total'
    ");

    // Query total number of cancelled orders
    $total_cancelled_orders = $wpdb->get_var("
        SELECT COUNT(ID)
        FROM {$orders_table}
        WHERE post_status = 'wc-cancelled'
    ");

    // Query total amount for cancelled orders
    $total_cancelled_amount = $wpdb->get_var("
        SELECT SUM(meta.meta_value)
        FROM {$orders_table} AS orders
        INNER JOIN {$table_prefix}postmeta AS meta ON orders.ID = meta.post_id
        WHERE orders.post_status = 'wc-cancelled'
        AND meta.meta_key = '_order_total'
    ");

    // Calculate total orders and total amount
    $total_orders = $total_completed_orders + $total_processing_orders;
    $total_order_amount = $total_completed_amount + $total_processing_amount;

    // Output the statistics and table name as an admin notice
    echo '<div class="notice notice-info">';
    echo '<p><strong>WooCommerce Order Statistics (Table: ' . $orders_table . '):</strong></p>';
    echo '<ul>';
    echo '<li>Total Number of Orders: ' . $total_orders . '</li>';
    echo '<li>Total Amount for Total Orders: ' . wc_price($total_order_amount) . '</li>';
    echo '<li>Total Cancelled Orders: ' . $total_cancelled_orders . '</li>';
    echo '<li>Total Amount for Cancelled Orders: ' . wc_price($total_cancelled_amount) . '</li>';
    echo '</ul>';
    echo '</div>';
}

// Hook into the admin menu
add_action('admin_notices', 'display_woocommerce_order_stats_admin_notice');

问题根源 #

1. 数据表指向错误：WooCommerce 3.0+版本后，订单作为自定义文章类型shop_order存储在wp_posts表中，wp_wc_orders并非标准数据表，大部分场景下不存在。
2. 查询逻辑缺陷：直接关联postmeta求和可能出现重复计算（部分订单可能存在多个_order_total元数据记录）。
3. 空值未处理：当无对应状态订单时，get_var返回NULL，直接相加会触发PHP警告或导致统计结果错误。

修复后的完整代码 #

<?php
/*
Plugin Name: WooCommerce Order Stats
Description: 在WordPress后台展示WooCommerce订单统计数据
Version: 1.1
Author: Your Name
*/

// 禁止直接访问
if (!defined('ABSPATH')) {
    exit;
}

// 显示订单统计的函数
function display_woocommerce_order_stats_admin_notice() {
    global $wpdb;
    $table_prefix = $wpdb->prefix;
    
    // 定义订单状态映射，减少冗余代码
    $status_mapping = [
        'completed' => 'wc-completed',
        'processing' => 'wc-processing',
        'cancelled' => 'wc-cancelled'
    ];
    
    $stats = [];
    
    // 批量查询各状态订单的数量和金额
    foreach ($status_mapping as $key => $status) {
        // 查询订单数量（使用参数化查询防注入）
        $order_count = $wpdb->get_var($wpdb->prepare("
            SELECT COUNT(ID)
            FROM {$table_prefix}posts
            WHERE post_type = 'shop_order'
            AND post_status = %s
        ", $status));
        
        // 查询订单总金额（GROUP BY确保单订单只计算一次）
        $order_amount = $wpdb->get_var($wpdb->prepare("
            SELECT SUM(meta.meta_value)
            FROM {$table_prefix}posts AS orders
            INNER JOIN {$table_prefix}postmeta AS meta ON orders.ID = meta.post_id
            WHERE orders.post_type = 'shop_order'
            AND orders.post_status = %s
            AND meta.meta_key = '_order_total'
            GROUP BY orders.ID
        ", $status));
        
        // 处理空值，默认设为0
        $stats[$key]['count'] = $order_count ?: 0;
        $stats[$key]['amount'] = $order_amount ?: 0;
    }
    
    // 计算总订单数和总金额（已完成+处理中）
    $total_orders = $stats['completed']['count'] + $stats['processing']['count'];
    $total_order_amount = $stats['completed']['amount'] + $stats['processing']['amount'];
    
    // 输出后台通知
    echo '<div class="notice notice-info">';
    echo '<p><strong>WooCommerce订单统计：</strong></p>';
    echo '<ul>';
    echo '<li>订单总数：' . $total_orders . '</li>';
    echo '<li>订单总金额：' . wc_price($total_order_amount) . '</li>';
    echo '<li>已取消订单数：' . $stats['cancelled']['count'] . '</li>';
    echo '<li>已取消订单总金额：' . wc_price($stats['cancelled']['amount']) . '</li>';
    echo '</ul>';
    echo '</div>';
}

// 挂载到后台通知钩子
add_action('admin_notices', 'display_woocommerce_order_stats_admin_notice');

修复说明 #

1. 修正数据表指向：使用WooCommerce标准的wp_posts表（订单类型为shop_order），替代不存在的wp_wc_orders表。
2. 参数化查询：用$wpdb->prepare防止SQL注入，提升代码安全性。
3. 空值处理：通过?: 0将空值转为0，避免PHP警告和统计错误。
4. 优化求和逻辑：添加GROUP BY orders.ID确保每个订单只计算一次金额，避免重复求和。
5. 代码重构：通过数组批量处理不同状态的订单，减少冗余代码，提升可维护性。

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