How to count orders of products that have pending status in Woocommerce?

Question

I'm trying to make stats script that makes clear usage for reservations of product for Woocommerce, diplaying available, reserved, and sold status. Status orders are available in backend admin panel, but I need to make count and visible in frontend.

Example:

ProductName

Items available: 10

Items already reserved (but not paid yet): X

Items sold: Y

Currently woocommerce has a function that shows sold quantity per product, but it counts both reserved (pending status) and sold (completed), so it's different kind of count.

I wrote a query that should find orders matching pending status (wp_term_relationships.term_taxonomy_id = 46) but I can't select only one specific product, as it counts all pending orders in database. The product ID is the same for wp_posts.ID and wp_woocommerce_order_itemmeta.meta_value = 250.

Note that product and orders are in the same table: wp_posts.

Here is my query:

SELECT COUNT(DISTINCT wp_posts.ID) 
FROM wp_posts, wp_postmeta, wp_term_relationships, wp_woocommerce_order_itemmeta oi 

WHERE wp_posts.ID = wp_postmeta.post_id
AND wp_posts.post_status = 'publish'
AND wp_posts.ID = wp_postmeta.post_id 
AND oi.meta_key = '_product_id'
AND oi.meta_value = 250
AND wp_posts.post_type = 'shop_order'
AND wp_term_relationships.object_id = wp_posts.ID
AND wp_term_relationships.term_taxonomy_id = 46

Any ideas how to select count for only this one product? I feel I need just one more filer but can't figure out what is missing. Thanks in advance.

Answer 1

Look at the following and give it a go. It might need a few adjustments UPDATED:

     SELECT COUNT(*) 
   FROM wp_posts, wp_postmeta, wp_term_relationships, wp_woocommerce_order_itemmeta oi 

  WHERE wp_posts.ID = wp_postmeta.post_id
  AND wp_posts.post_status = 'publish'
  AND wp_posts.ID = wp_postmeta.post_id 
  AND wp_posts.ID= oi.meta_id
 AND oi.metakey = '_product_id'
 AND oi.metavalue = 250
 AND wp_posts.post_type = 'shop_order'
 AND wp_term_relationships.object_id = wp_posts.ID
 AND wp_term_relationships.term_taxonomy_id = 46

Answer 2

I found the solution!

Just as I suspected I needed one more table to be filtered, and that did the whole trick!

So the line that was missing: ii.order_id = wp_posts.ID, and some other tweaks ;)

SELECT COUNT(DISTINCT wp_posts.ID) 

FROM wp_posts, wp_postmeta, wp_term_relationships, wp_woocommerce_order_itemmeta oi, wp_woocommerce_order_items ii 

WHERE wp_term_relationships.term_taxonomy_id = (xx - order status id) 
AND  wp_term_relationships.object_id = wp_posts.ID
AND oi.order_item_id = ii.order_item_id
AND oi.meta_key = '_product_id'
AND oi.meta_value = (yy - specific product id, the same id as in wp_posts)
AND ii.order_id = wp_posts.ID
AND wp_postmeta.post_id = wp_term_relationships.object_id
AND wp_posts.post_status = 'publish'

Make sure not to select 'shop_order" post type since it will give wrong result :) and note, that complete status may have few more values, since woocommerce has different state values for completed, removed, paid, and canceled order.

I hope this will be helpful to someone else :)

And many thanks to Kiril for guiding me to this solution!