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.

this is bad code, you should use worpress api to access the DB, the code will probably break when ordpress will change the PHP api it uses to access the DB. Probably the whole idea to bypass woocomerce api and directly access the table is bad as well, but at least that was the question :(
–
Mark KaplunFeb 26 '14 at 14:42