I need to display the total revenue of all orders for my products depending on if the logged-in user is the author of the product or not.
I can't use the Quantity field as a multiplier either because of the Gravity Forms add-on for Woocommerce processes it in to a meta_key/value field, same goes for the product ID reference to the order. I'm using a
$wpdb query to grab my order data, but I just can't seem to write it correctly or know how to get it, my SQL skills are not exactly tip-top.
So far I've come up with this:
SELECT * FROM wp_woocommerce_order_itemmeta AS order_meta LEFT JOIN wp_woocommerce_order_items AS order_items ON order_meta.order_item_id = order_items.order_item_id LEFT JOIN wp_term_relationships AS term_rels ON term_rels.object_id = order_items.order_id WHERE term_taxonomy_id = 24 # 24 being the completed order status ID
This gives me the following:
As you can see the meta values for the Product ID and the Qty: 16/30 etc. are in the same column, I almost need them to be in the same row if possible.
After I get the query right, I can then multiply the Qty (gotten with regex) by a custom field value, and the display this on the web site.
Anyone able to point me in the right direction? :)