Woocommerce: Grab total revenue of a product over all orders

Question

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? :)

Answer 1

Got it solved. Probably not the most efficient way but it works!

SELECT product_id, meta_value FROM
    (
        SELECT order_meta.order_item_id, order_meta.meta_value AS 'product_id'
        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
        AND order_meta.meta_key = '_product_id'
    ) AS product_ID
    JOIN
    (
        SELECT order_meta2.meta_value, order_meta2.order_item_id
        FROM wp_woocommerce_order_itemmeta AS order_meta2
        LEFT JOIN wp_woocommerce_order_items AS order_items
            ON order_meta2.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
        AND (
            order_meta2.meta_key = 'Item Details'
            OR order_meta2.meta_key = 'Product Name'
        )
    ) AS product_qty
    ON product_qty.order_item_id = product_ID.order_item_id
    WHERE product_id = " . $post->ID

Answer 2

List all the customer with product

//now selects all data in the table wp_postmeta where the word billing is within the value 
$result = mysql_query("SELECT distinct post_id FROM wp_postmeta WHERE meta_key LIKE '%billing%' ");

while($row = mysql_fetch_array($result)) 
{ 
//prints the results in a basic table for each row that was retrieved 


//SELECT distinct b.order_item_name, a._billing_email, a., a., a. , a. FROM wp_postmeta a , wp_woocommerce_order_items b WHERE a.post_id=b.order_id and a.meta_key like '%billing%' 

//now selects all data in the table wp_postmeta where the word billing is within the value 


$result3 = mysql_query("SELECT * FROM  `wp_postmeta` WHERE post_id = ".$row['post_id']  );
while($row3 = mysql_fetch_array($result3)) 
{ 
    if($row3['meta_key']=='_billing_email')
        $email = $row3['meta_value'];
    if($row3['meta_key']=='_billing_last_name')
        $name = $row3['meta_value'];
    if($row3['meta_key']=='_billing_first_name')
        $firstname = $row3['meta_value'];
    if($row3['meta_key']=='_billing_city')
        $city = $row3['meta_value'];
    if($row3['meta_key']=='_billing_postcode')
        $cp = $row3['meta_value'];
    if($row3['meta_key']=='_customer_user')
        $user = $row3['meta_value'];    
    if($user){
    $result4 = mysql_query("SELECT * FROM  wp_users WHERE ID = ".$user  );
    while($row4 = mysql_fetch_array($result4)) 
    {   
        $user_nicename = $row4['user_nicename'];
        $user_email = $row4['user_email'];

    }   
    }

}

$result2 = mysql_query("SELECT * FROM  `wp_woocommerce_order_items` WHERE order_id = ".$row['post_id']  );
while($row2 = mysql_fetch_array($result2)) 
{ 
    $cours = $row2['order_item_name'];
    echo $row['post_id'].";".$user_nicename.";".$user_email.";".$cours .";".$email.";".$name.";".$firstname.";".$city.";".$cp."<br/>";
}

}