WooCommerce: Get the total sales for product variations in a SQL query

Question

I'm creating a plugin extension for a reporting plugin. The current plugin creates various report including a report on the top selling products on a per product basis.

I need to recreate this setup to display top products based on variation. The fields needed are Product Name, SKU, Qty Sold, Total Amount in sales.

How do I get the data for product variations?

The following is my current code.

SQL query and data treatment:

$phoen_product_query = "
        SELECT  
            posts.post_title AS product_name,
            meta.meta_key as mkey, 
            meta.meta_value as product_value, 
            posts.ID AS ID
            FROM  {$wpdb->posts} AS posts
            LEFT JOIN {$wpdb->postmeta} AS meta 
                ON posts.ID = meta.post_id 
        WHERE 
            posts.post_status IN ( 'publish','private' ) 
            AND posts.post_type IN ( 'product' ) 
            AND meta.meta_key IN ( 'total_sales' ,'_price' ,'post_views_count', '_sku') 
        ORDER BY
            posts.ID ASC, 
            meta.meta_key ASC
    ";
    $phoen_product_data = $wpdb->get_results(  $phoen_product_query,ARRAY_A);
    
    foreach($phoen_product_data as $key1 => $valuee){
        
        if(!isset( $phoen_top_products[$valuee['ID']])){
            
            $phoen_top_products[$valuee['ID']] = Array();
            
            $phoen_top_products[$valuee['ID']] = Array(
            
                "produc_total" => 0,
                
                "product_price" => 0,
                
                "product_count" => 0,
                
                "product_views" => 0
                
            );
        
        }
        
        switch ($valuee['mkey']) {

            case "_sku":

                $phoen_top_products[$valuee['ID']]["product_sku"] = $valuee['product_value'];
                
                break;
            
            case "_price":
                
                $phoen_top_products[$valuee['ID']]["product_price"] = $valuee['product_value'];
                
                break;
                
            case "post_views_count":
            
                $phoen_top_products[$valuee['ID']]["product_views"] = $valuee['product_value'];
                
                break;
                
            case "total_sales":
            
                $phoen_top_products[$valuee['ID']]["product_count"] = $valuee['product_value'];
                
                $phoen_top_products[$valuee['ID']]["produc_total"] = $valuee['product_value'] * $phoen_top_products[$valuee['ID']]["product_price"];
                
                $phoen_top_products[$valuee['ID']]["product_name"] = $valuee['product_name'];
                
                $phoen_top_products[$valuee['ID']]["ID"] = $valuee['ID'];
                
                break;
                
            default:
            
                break;
                
        }

    }

Admin module:

<table class="table table-striped table-bordered" id="phoen_top_product_table">
<thead>
  <tr>
    <th><?php _e( 'Product Name', 'advanced-reporting-for-woocommerce' ); ?></th>
    <th><?php _e( 'SKU', 'advanced-reporting-for-woocommerce' ); ?></th>
    <th><?php _e( 'Qty', 'advanced-reporting-for-woocommerce' ); ?></th>
    <th><?php _e( 'Amount', 'advanced-reporting-for-woocommerce' ); ?></th>
  </tr>
</thead>
<tbody>
<?php
$phoen_repot_product=array();
$phoen_product_count=0;

for($i=0; $i<count($phoen_top_products); $i++)
{
    if($phoen_get_product_val!='View All')
    {
        if($phoen_product_count<$phoen_get_product_val)
        {
            
            ?>
            <tr class="phoen_top_product_tr">
                <td>
                
                    <?php $phoen_product_title=isset($phoen_top_products[$i]['product_name'])?$phoen_top_products[$i]['product_name']:'';
                    
                    echo $phoen_product_title; ?> 
                    
                </td>

                <td>
                
                    <?php $phoen_product_sku=isset($phoen_top_products[$i]['product_sku'])?$phoen_top_products[$i]['product_sku']:'';     
                    
                        echo $phoen_product_sku; ?> 
                    
                </td>
                
                <td>
                
                    <?php 
                    
                        $phoen_product_quentity=isset($phoen_top_products[$i]['product_count'])?$phoen_top_products[$i]['product_count']:'';     
                    
                        echo $phoen_product_quentity; 
                
                    ?>
                
                </td>
                
                <td>
                    <?php 
                    
                    $phoen_total_product_price=isset($phoen_top_products[$i]['produc_total'])?$phoen_top_products[$i]['produc_total']:'';
                    
                        echo get_woocommerce_currency_symbol().($phoen_total_product_price);
                    
                    ?>
                </td>
                
            
            </tr>   
            
            <?php
            
            $phoen_repot_product[$i]= array(
            
                'name'=>$phoen_product_title,
                'sku'=>$phoen_product_sku,
                'product_count'=>$phoen_product_quentity,
                'amount'=>$phoen_total_product_price
            
            );
        }
            $phoen_product_count++;
    }else{
        ?>
            <tr class="phoen_top_product_tr">
                <td>
                
                    <?php $phoen_product_title=isset($phoen_top_products[$i]['product_name'])?$phoen_top_products[$i]['product_name']:'';
                    
                    echo $phoen_product_title; ?> 
                    
                </td>

                <td>
                
                    <?php $phoen_product_sku=isset($phoen_top_products[$i]['product_sku'])?$phoen_top_products[$i]['product_sku']:'';
                    
                    echo $phoen_product_sku; ?> 
                    
                </td>
                
                <td>
                
                    <?php 
                    
                        $phoen_product_quentity=isset($phoen_top_products[$i]['product_count'])?$phoen_top_products[$i]['product_count']:'';     
                    
                        echo $phoen_product_quentity; 
                
                    ?>
                
                </td>
                
                <td>
                    <?php 
                    
                    $phoen_total_product_price=isset($phoen_top_products[$i]['produc_total'])?$phoen_top_products[$i]['produc_total']:'';
                    
                        echo get_woocommerce_currency_symbol().($phoen_total_product_price);
                    
                    ?>
                </td>
                
            
            </tr>   
            
            <?php
            
            $phoen_repot_product[$i]= array(
            
                'name'=>$phoen_product_title,
                'sku'=>$phoen_product_sku,
                'product_count'=>$phoen_product_quentity,
                'amount'=>$phoen_total_product_price
            
            );
    }
    
        
}

$phoen_product_file = fopen('phoen-repot-product.csv', 'w');
                                        
fputcsv($phoen_product_file, array('Product Name', 'SKU', 'Qty', 'Amount'));
 
foreach ($phoen_repot_product as $phoen_product_row)
{
    fputcsv($phoen_product_file, $phoen_product_row);
}

fclose($phoen_product_file);    

 ?>

</tbody>

Answer 1

In your database query, you just forgot to call also the post_type like 'product_variation'.

You can replace this line:

AND posts.post_type IN ( 'product' )

By this:

AND posts.post_type IN ( 'product','product_variation' ) 

Or this (shorter):

AND posts.post_type LIKE 'product%'

And you will get also all product variation data. I have tested your code and it works.
(I got only 'post_views_count' empty as I think is a custom field).