WooCommerce 最近一个月销量排行(2021)

优质

小牛编辑

139浏览

2023-12-01

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>',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