0

I have an oddly specific challenge, displaying the total count of products in orders of a specific order status. So let's say there's 52 orders with the order status "Processing", I'm trying to retrieve a total count of line items in those orders to then display on the front end.

Example: "Customers like you are playing with 6,829 Fidget Spinners right now!"

Where the number would essentially be a total count of line items in orders with the "Completed" order status.

I think I understand how to achieve this from a logical standpoint — run a query to filter the database for orders of a specific order status, then retrieve the total line items in each order, and add those all together, but I'm struggling to understand how that translates in code, I'm a front end dev.

Any pointers would be greatly appreciated, thank you kindly.

1 Answer 1

0

@lukecav has posted a snippet previously that almost does the job:

https://gist.github.com/lukecav/05afef12feaf980c121da9afb9291ad5

If we change the column fetched to "order_item_id", it fetches the number of unique order items that match the criteria:

function get_order_items_by_product_id( $product_id, $order_status = array( 'wc-completed' ) ){
    global $wpdb;

    $results = $wpdb->get_col("
        SELECT order_items.order_item_id
        FROM {$wpdb->prefix}woocommerce_order_items as order_items
        LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta as order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id
        LEFT JOIN {$wpdb->posts} AS posts ON order_items.order_id = posts.ID
        WHERE posts.post_type = 'shop_order'
        AND posts.post_status IN ( '" . implode( "','", $order_status ) . "' )
        AND order_items.order_item_type = 'line_item'
        AND order_item_meta.meta_key = '_product_id'
        AND order_item_meta.meta_value = '$product_id'
    ");

    return $results;
}

Hope this helps :)

2
  • It fetches order IDs with the right filter but doesn't do the counting / aggregation I don't think.
    – Rup
    Mar 31, 2023 at 10:15
  • Edited with a slight change :)
    – Scott L
    Mar 31, 2023 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.