MySQL-Information Schema：排查WordPress Multisite的WooCommerce订单查询问题

Hey there! Let's figure out why your MySQL query for pulling orders from all Multisite sites isn't working, and fix it up. First, let's cover the common pitfalls with Multisite + WooCommerce data storage, then share a working solution.

Common Reasons Your Query Might Fail #

• Hardcoded table prefixes: WordPress Multisite uses dynamic table prefixes like wp_1_, wp_2_ for each sub-site—if you're using a static prefix like wp_, you're only querying the main site.
• Missing site context: You need to pull all site IDs from the core Multisite table (wp_blogs) to target every sub-site's tables.
• Incorrect table joins: WooCommerce orders live in wp_{site_id}_posts (as shop_order post types) and order metadata in wp_{site_id}_postmeta—joining the wrong meta table will break results.
• Permissions issues: Your MySQL user might not have access to all sub-site tables (make sure they have SELECT privileges on every wp_{site_id}_posts and wp_{site_id}_postmeta table).
• Unfiltered order statuses: If you're including draft/auto-draft posts, you'll get incomplete or invalid "orders".

Working MySQL Query (For MySQL Workbench) #

This query dynamically generates a union of results from every Multisite site that has WooCommerce orders:

-- Replace 'wp_' with your actual main table prefix if it's different
SET @main_prefix = 'wp_';
SET @sql = NULL;

-- Build a UNION ALL query for each site's orders
SELECT GROUP_CONCAT(
    CONCAT(
        'SELECT ',
        b.blog_id, ' AS site_id, ',
        'p.ID AS order_id, ',
        'p.post_date AS order_date, ',
        'p.post_status AS order_status, ',
        'pm_email.meta_value AS customer_email, ',
        'pm_total.meta_value AS order_total ',
        'FROM ', @main_prefix, b.blog_id, '_posts p ',
        'LEFT JOIN ', @main_prefix, b.blog_id, '_postmeta pm_email ON p.ID = pm_email.post_id AND pm_email.meta_key = "_billing_email" ',
        'LEFT JOIN ', @main_prefix, b.blog_id, '_postmeta pm_total ON p.ID = pm_total.post_id AND pm_total.meta_key = "_order_total" ',
        'WHERE p.post_type = "shop_order" ',
        'AND p.post_status NOT IN ("auto-draft", "draft", "trash")'
    ) SEPARATOR ' UNION ALL '
) INTO @sql
FROM wp_blogs b
WHERE b.public = 1; -- Optional: Filter out private sites

-- Execute the generated query
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

What This Does #

1. Grabs all public site IDs from the core wp_blogs table (you can remove the WHERE b.public = 1 line if you need private sites too).
2. Dynamically builds a query for each site, joining its posts and postmeta tables to pull key order details.
3. Uses UNION ALL to combine results from all sites into a single dataset.
4. Filters out trash/draft orders to only include valid ones.

Customization Tips #

• Add more metadata fields: Just add another LEFT JOIN to the postmeta table for keys like _billing_first_name, _shipping_address_1, etc.
• Filter by date: Add a AND p.post_date BETWEEN '2024-01-01' AND '2024-12-31' clause to the site-specific query.
• Exclude sites without WooCommerce: If you want to skip sites that don't have WooCommerce, you can add a check for the existence of the shop_order post type in each site's posts table (though the current query will just return no results for those sites anyway).

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