WooCommerce HPOS: Updating Plugins for High-Performance Order Storage

WooCommerce High-Performance Order Storage (HPOS), previously codenamed “Solid Store”, migrates WooCommerce orders from the WordPress wp_posts and wp_postmeta tables into dedicated order-specific tables: {prefix}wc_orders, {prefix}wc_orders_meta, {prefix}wc_order_addresses, and {prefix}wc_order_operational_data. The traditional storage model put orders in wp_posts with post_type = ‘shop_order’ and stored all order metadata in wp_postmeta, sharing that table with post thumbnails, ACF fields, and every other meta value on the site. On stores with 100,000+ orders, wp_postmeta grew to tens of millions of rows and every admin order-list query required complex JOINs between wp_posts and wp_postmeta — no index on post_type helped because the table was not designed for this multi-purpose usage pattern. HPOS became opt-in in WooCommerce 7.1 (January 2023), the recommended default in WooCommerce 8.1 (September 2023), and the enforced default for new installs starting with WooCommerce 8.2 (October 2023). During the migration, HPOS’s compatibility mode keeps both storage systems in sync via double writes — plugins that still read from wp_posts continue working during the transition. The required compatibility declaration is a call to FeaturesUtil::declare_compatibility( 'custom_order_tables', __FILE__, true ) inside the before_woocommerce_init action — plugins without this declaration show an admin warning and can block stores from disabling compatibility mode. The OrderUtil class provides runtime helpers: OrderUtil::custom_orders_table_usage_is_enabled() returns true when HPOS is active, and OrderUtil::get_table_for_orders() returns the correct table name for raw SQL queries that must work against both backends. The WooCommerce REST API headless post covered read-only order access via the API; HPOS compatibility affects every plugin that reads or writes orders via $wpdb, get_posts(), or WP_Query with post_type = ‘shop_order’.

Problem: A custom WooCommerce reporting plugin queries orders with $wpdb->get_results( "SELECT ID, post_date FROM {$wpdb->posts} WHERE post_type='shop_order' AND post_status='wc-completed'" ). After the store enables HPOS and disables compatibility mode, the report returns 0 rows — completed orders are no longer stored in wp_posts.

Solution: Declare HPOS compatibility in the plugin, replace all get_posts() and direct $wpdb order queries with wc_get_orders(), and use OrderUtil for the one aggregate SQL query that wc_get_orders() cannot express — with a runtime branch that targets the correct table.

// ── Step 1: Declare HPOS compatibility in the plugin's main file ────────────
add_action( 'before_woocommerce_init', function(): void {
    if ( class_exists( 'Automattic\WooCommerce\Utilities\FeaturesUtil' ) ) {
        \Automattic\WooCommerce\Utilities\FeaturesUtil::declare_compatibility(
            'custom_order_tables',
            __FILE__,
            true   // true = compatible; false = not compatible (shows admin warning)
        );
    }
} );

// ── Step 2: Replace WP_Query / get_posts() with wc_get_orders() ──────────────
// BEFORE (breaks when HPOS + compatibility mode is disabled):
// $orders = get_posts( [
//     'post_type'   => 'shop_order',
//     'post_status' => 'wc-completed',
//     'numberposts' => -1,
// ] );

// AFTER: works with both legacy storage and HPOS
function myplugin_get_completed_orders_today(): array {
    return wc_get_orders( [
        'status'       => 'wc-completed',
        'limit'        => -1,
        'date_created' => '>' . ( time() - DAY_IN_SECONDS ),
        'return'       => 'ids',  // array of IDs, not WC_Order objects (faster)
    ] );
}

// ── Step 3: Use WC_Order API instead of get_post_meta() ───────────────────────
function myplugin_get_order_summary( int $order_id ): array {
    $order = wc_get_order( $order_id );
    if ( ! $order instanceof WC_Order ) return [];

    return [
        'id'           => $order->get_id(),
        'status'       => $order->get_status(),
        'total'        => $order->get_total(),
        'currency'     => $order->get_currency(),
        'date_created' => $order->get_date_created()?->getTimestamp(),
        'email'        => $order->get_billing_email(),
        // Plugin-added meta: use get_meta(), NOT get_post_meta()
        'erp_ref'      => $order->get_meta( '_erp_reference_id', true ),
    ];
}

// ── Step 4: OrderUtil for aggregate SQL that wc_get_orders() cannot express ───
function myplugin_get_revenue_by_payment_method(): array {
    global $wpdb;

    if ( \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ) {
        // HPOS path: query the dedicated wc_orders table
        $table = $wpdb->prefix . 'wc_orders';
        return (array) $wpdb->get_results( $wpdb->prepare(
            "SELECT payment_method,
                    SUM(total_amount) AS revenue,
                    COUNT(*)          AS order_count
             FROM {$table}
             WHERE status = %s
               AND DATE(date_created_gmt) = CURDATE()
             GROUP BY payment_method",
            'wc-completed'
        ), ARRAY_A );
    }

    // Legacy path: query wp_posts + wp_postmeta
    return (array) $wpdb->get_results(
        "SELECT pm.meta_value       AS payment_method,
                SUM(pm2.meta_value) AS revenue,
                COUNT(*)            AS order_count
         FROM {$wpdb->posts} p
         JOIN {$wpdb->postmeta} pm
              ON p.ID = pm.post_id AND pm.meta_key = '_payment_method'
         JOIN {$wpdb->postmeta} pm2
              ON p.ID = pm2.post_id AND pm2.meta_key = '_order_total'
         WHERE p.post_type   = 'shop_order'
           AND p.post_status = 'wc-completed'
           AND DATE(p.post_date_gmt) = CURDATE()
         GROUP BY pm.meta_value",
    ARRAY_A );
}

NOTE: wc_get_orders() is significantly more expressive than get_posts() for orders — it accepts customer_id, billing_email, date_paid, date_completed, payment_method, transaction_id, meta_query, and many other order-specific filters. Migrating every WP_Query or get_posts() call that queries post_type = ‘shop_order’ to wc_get_orders() makes a plugin HPOS-compatible for 90% of use cases without needing OrderUtil. The HPOS migration tool in WooCommerce → Settings → Advanced → Features shows all installed plugins with their HPOS compatibility status — check it before enabling HPOS to identify which plugins need updates before disabling compatibility mode.