1

I'm trying to get WooCommerce best selling products for the current month.

The following code works fine to get best selling products all time:

$query_args = array(
    'posts_per_page' => $number,
    'post_status'    => 'publish',
    'post_type'      => 'product',
    'no_found_rows'  => 1,
    'meta_key'       => 'total_sales',
    'orderby'        => 'meta_value_num',
    'order'          => 'desc',
    'meta_query'     => array(),
    'tax_query'      => array(
        'relation' => 'AND',
    ),
); // WPCS: slow query ok.

Now I'm trying to get the best selling products for the current month, but it does not work correctly:

$query_args = array(
    'posts_per_page' => $number,
    'post_status'    => 'publish',
    'post_type'      => 'product',
    'no_found_rows'  => 1,
    'meta_key'       => 'total_sales',
    'orderby'        => 'meta_value_num',
    'order'          => 'desc',
    'date_query' => array(
        array(
            'year' => date( 'Y' ),
            'month' => date( 'm' ),
        ),
    ),
    'meta_query'     => array(),
    'tax_query'      => array(
        'relation' => 'AND',
    ),
); // WPCS: slow query ok.
2

It's not possible to use a date on best selling products, as product total_sales can't be related to any date.

To make that possible it should require to query products checking in each paid orders from current month using WPDB class and a SQL query like in this custom function (with an optional argument to limit the number of products):

function get_best_selling_products( $limit = '-1' ){
    global $wpdb;

    $limit_clause = intval($limit) <= 0 ? '' : 'LIMIT '. intval($limit);
    $curent_month = date('Y-m-01 00:00:00');

    return (array) $wpdb->get_results("
        SELECT p.ID as id, COUNT(oim2.meta_value) as count
        FROM {$wpdb->prefix}posts p
        INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim
            ON p.ID = oim.meta_value
        INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim2
            ON oim.order_item_id = oim2.order_item_id
        INNER JOIN {$wpdb->prefix}woocommerce_order_items oi
            ON oim.order_item_id = oi.order_item_id
        INNER JOIN {$wpdb->prefix}posts as o
            ON o.ID = oi.order_id
        WHERE p.post_type = 'product'
        AND p.post_status = 'publish'
        AND o.post_status IN ('wc-processing','wc-completed')
        AND o.post_date >= '$curent_month'
        AND oim.meta_key = '_product_id'
        AND oim2.meta_key = '_qty'
        GROUP BY p.ID
        ORDER BY COUNT(oim2.meta_value) + 0 DESC
        $limit_clause
    ");
}

It will output an array of objects sorted by product count DESC, each object containing the product ID and the count.

USAGE Example (limited to 5 bestselling products in the current month):

$best_selling_products = get_best_selling_products( 5 );

// Loop through best selling products stdClass Objects
foreach( $best_selling_products as $values ) {
    $product_id    = $values->id; // Get the product ID
    $product_count = $values->count; // Get the count for the current month
}
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.