Get WooCommerce Order Details for Thousands of Orders: Complete Code Example

Here’s the refined and optimized code to fetch all WooCommerce Order Details at once:

Code to Fetch More Than 1000 WooCommerce Orders at Once

<?php
/**
 * Fetch all WooCommerce orders in bulk without pagination.
 * This method bypasses limits to fetch all orders at once.
 */



function get_all_orders_details() {
    global $wpdb;

    // Query to fetch all order IDs directly from the database
    $order_ids = $wpdb->get_col("
        SELECT ID 
        FROM {$wpdb->posts}
        WHERE post_type = 'shop_order' 
        AND post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold') 
    ");


    $all_orders = [];

    // Process each order ID to fetch details
    foreach ($order_ids as $order_id) {
        $order = wc_get_order($order_id);
        $order_details = [
            'ID'            => $order->get_id(),
            'Total'         => $order->get_total(),
            'Customer Name' => $order->get_billing_first_name() . ' ' . $order->get_billing_last_name(),
            'Email'         => $order->get_billing_email(),
            'Status'        => $order->get_status(),
            'Date'          => $order->get_date_created()->date('Y-m-d H:i:s'),
        ];


        $all_orders[] = $order_details;

        // Display order details for debugging or logging
        echo "Order ID: {$order_details['ID']}\n";
        echo "Total: {$order_details['Total']}\n";
        echo "Customer: {$order_details['Customer Name']}\n";
        echo "Email: {$order_details['Email']}\n";
        echo "Status: {$order_details['Status']}\n";
        echo "Date: {$order_details['Date']}\n";
        echo "-------------------------\n";
    }




    return $all_orders; // Return all order details
}




// Execute the function to fetch all orders
get_all_orders_details();

How It Works

1. Direct Database Query:
   • The get_col function retrieves all shop_order post IDs in a single query.
   • You can filter by specific statuses (e.g., wc-completed, wc-processing).
2. Order Details:
   • For each order ID, wc_get_order the WooCommerce order object is retrieved.
   • Details such as ID, total, customer name, email, status, and creation date are extracted.
3. Output and Storage:
   • Order details are displayed using echo and stored in the $all_orders array for further processing.

Use Cases

• Exporting orders for analysis.
• Syncing orders with external systems.
• Custom reporting.

Considerations

• Performance:
  • This method directly interacts with the database, which might be resource-intensive for huge datasets. Consider running this during off-peak hours.
• Scalability:
  • For hundreds of thousands of orders, splitting into smaller chunks or using pagination is recommended.

Final Note

This code fetches all orders at once, which is ideal for controlled environments. If you plan to implement this on a live site, add safeguards like limiting the maximum number of orders fetched or implementing a timeout to prevent server overload.