How to query Woocommerce orders on a page

Question

I want to create a page that displays a query from the database to show some order details.

How to display this query on a page?

Answer 1

There is multiple ways to get Woocommerce Orders:

1) Woocommerce has a dedicated function wc_get_orders() that will give you an array of WC_Order objects:

$orders = wc_get_orders( array('numberposts' => -1) );

// Loop through each WC_Order object
foreach( $orders as $order ){
    echo $order->get_id() . '<br>'; // The order ID
    echo $order->get_status() . '<br>'; // The order status
}

To get the order data see the links below

---

2) You can also use a Wordpress WP_Query:

$loop = new WP_Query( array(
    'post_type'         => 'shop_order',
    'post_status'       =>  array_keys( wc_get_order_statuses() ),
    'posts_per_page'    => -1,
) );

// The Wordpress post loop
if ( $loop->have_posts() ): 
while ( $loop->have_posts() ) : $loop->the_post();

// The order ID
$order_id = $loop->post->ID;

// Get an instance of the WC_Order Object
$order = wc_get_order($loop->post->ID);

endwhile;

wp_reset_postdata();

endif;

To get the order data see the links below

---

3) You can use a SQL query

global $wpdb;
$results = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}posts WHERE post_type LIKE 'shop_order'");

// Loop through each order post object
foreach( $results as $result ){
    $order_id = $result->ID; // The Order ID

    // Get an instance of the WC_Order Object
    $order    = wc_get_order( $result->ID );
}

---

You will be able to get all order details from the WC_Order object as explained in:

• How to get WooCommerce order details
• Get Order items and WC_Order_Item_Product in Woocommerce 3

---

Displaying the data as a list in a table

To display the orders in a list, you should have a look to the woocommerce deprecated template myaccount/my-orders.php or to myaccount/orders.php template…

It will give you a model…

Answer 2

As long as it's the last query executed, you can use $wpdb->last_query to grab the last query. You'll need to have define('SAVEQUERIES', true) in your wp-config.php for this