WooCommerce的shortcode best_selling_products能显示产品销量排行,但这个排行是网站上线以来的总销量的排行,如果想显示最近一个月销量排行该怎么办呢?
WooCommerce如何记录销量
WooCommerce的销量记录在wp_postmeta表中,如下图所示,每次卖出产品就更新一下这个产品的的meta值,并没有记录销售时间。
用代码实现最近一个月销量排行
利用上次介绍的WooCommerce自定义产品列表带分页来实现,并做成一个shortcode,去掉排序的filter,因为我们固定要按照销量排序。
SQL查询的原理简单描述一下:
- 从wp_post表中找出所有post_status为wc-processing或wc-completed的订单。
- 根据订单(post_id)去
woocommerce_order_item
表里找到每个订单里的产品。 - 再去
woocommerce_order_itemmeta
表中找到每个产品的销量(_qty)。 - 在重新join 一次
wp_post
表,这次使用产品 ID去连接,目的是查询产品是否为publish状态。 - 最后就是约束条件,比如订单的时间是30天以内的等等,再按照销量排序一下,搞定。
class CP_Month_Best_Selling {
public function __construct() {
add_shortcode('month-best-selling', [$this, 'best_selling']);
add_filter('query_vars', [$this, 'add_product_pagination_query_vars']);
}
function best_selling($atts) {
$a = shortcode_atts(array(
'days' => 30,
'posts_per_page' => 12,
), $atts);
extract($a);
ob_start();
$current_page = (get_query_var('product-page')) ? absint(get_query_var('product-page')) : 1;
$data = $this->get_best_selling_products_from_cache(absint($days), absint($posts_per_page), absint($current_page));
$current_page = (int)$data['current_page'];
$total_products = (int)$data['total_products'];
$total_pages = (int)$data['total_pages'];
$products = $data['products'];
$this->the_best_sellers( $current_page, $total_products, $total_pages, $posts_per_page, $products );
return ob_get_clean();
}
function get_best_selling_products_from_cache( $days, $posts_per_page, $current_page ){
$cache_key ='best_seller-' . $days . '-' . $posts_per_page . '-' . $current_page;
$data = get_transient( $cache_key);
if( $data === false ){
$data = $this->get_best_selling_products( $days, $posts_per_page, $current_page );
set_transient( $cache_key, $data, DAY_IN_SECONDS );
}
return $data;
}
function the_best_sellers( $current_page, $total_products, $total_pages, $posts_per_page, $products ){
wc_set_loop_prop('current_page', $current_page);
wc_set_loop_prop('is_paginated', wc_string_to_bool(true));
wc_set_loop_prop('page_template', get_page_template_slug());
wc_set_loop_prop('per_page', $posts_per_page);
wc_set_loop_prop('total', $total_products);
wc_set_loop_prop('total_pages', $total_pages);
wc_set_loop_prop('is_shortcode', true);
// Loop start
if ($products) {
$original_post = $GLOBALS['post'];
echo '<div class="best-selling-count">',woocommerce_result_count(), '</div>';
woocommerce_product_loop_start();
foreach ($products as $product) {
$GLOBALS['post'] = get_post(absint($product['id']));
setup_postdata($GLOBALS['post']);
wc_get_template_part('content', 'product');
}
$GLOBALS['post'] = $original_post;
woocommerce_product_loop_end();
woocommerce_pagination();
wp_reset_postdata();
} else {
/**
* Hook: woocommerce_no_products_found.
*
* @hooked wc_no_products_found - 10
*/
do_action('woocommerce_no_products_found');
}
}
function get_best_selling_products($days = 30, $posts_per_page = 12, $paged ) {
global $wpdb;
$date = strtotime("-$days days");
$start_date = array(
'year' => date("Y", $date),
'month' => date("m", $date),
'day' => date("d", $date),
);
$start_date = implode('-', $start_date);
$limit = ($paged - 1) * $posts_per_page . "," . $posts_per_page;
// Get total rows and total pages for later use in pagination
// No need to count the sales at this phase
$total = $wpdb->get_var($wpdb->prepare("
SELECT count(DISTINCT p.ID) as count
FROM {$wpdb->prefix}posts o
INNER JOIN {$wpdb->prefix}woocommerce_order_items oi
ON (o.ID = oi.order_id AND o.post_status IN ('wc-processing','wc-completed'))
INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim
ON (oi.order_item_id = oim.order_item_id AND oim.meta_key = '_product_id')
INNER JOIN {$wpdb->prefix}posts p
ON (oim.meta_value = p.ID)
WHERE p.post_status = 'publish'
AND o.post_date >= '%s'", $start_date)
);
$total = absint($total);
$total_pages = ceil($total / $posts_per_page);
// Get paginated best selling products
$products = $wpdb->get_results($wpdb->prepare("
SELECT p.ID as id, SUM(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 >= '%s'
AND oim.meta_key = '_product_id'
AND oim2.meta_key = '_qty'
GROUP BY p.ID
ORDER BY SUM(oim2.meta_value) + 0 DESC
LIMIT $limit", $start_date), ARRAY_A);
return array(
'current_page' => $paged,
'total_products' => $total,
'total_pages' => $total_pages,
'products' => $products,
'posts_per_page' => $posts_per_page
);
}
/**
* Add product-page to query vars
*
*/
function add_product_pagination_query_vars($vars) {
$vars[] = "product-page";
return $vars;
}
}
new CP_Month_Best_Selling();
在编辑器中输入shortcode [sola_best_selling_products days=30 posts_per_page=3]
前台效果
参考文章:Get WooCommerce best selling products for the current month