Caching Min and Max Prices for All Product Categories (Woocommerce)

Suppose we have a product filter for a section (category) that includes filtering by product price. For such a filter, it is convenient to know the minimum and maximum product prices in the viewed category of products...

You can retrieve this price each time the category page is generated, but this is a heavy request, and for performance reasons, it is more convenient to obtain prices for each category once and then simply use them in the filter.

There are many ways to cache min-max prices. This article demonstrates one of them. This is what I did when faced with this task.

The principle of operation is as follows

All minimum and maximum product prices are collected for each category (for all possible levels) and for the taxonomy as a whole. Then all this data is saved in the WP option. Furthermore, when a product is updated or added, all this data is updated, but not immediately, but after 60 seconds following the update of the entry (this is necessary to be able to update entries massively and not to perform this costly operation with each update), the time of 60 seconds can be increased to, for example, 3600 (hours).

<?php

/**
 * Gathers and caches the minimum and maximum value of the specified meta-field for the specified terms of the taxonomy.
 * Suitable for obtaining the minimum and maximum prices of products from categories.
 *
 * The code caches the minimum and maximum numerical values in the post meta-fields for each category.
 * It also collects the overall minimum and maximum values for the entire taxonomy.
 *
 * @changelog
 *   2.1 CHG: The `init` hook was moved to the `init()` method. Minor edits.
 *   2.0 CHG: The code has been rewritten to allow creating different instances of the class.
 *   1.1 IMP: Refactored the code. Changed data storage from options to transient options.
 *       CHG: The output has been changed. The min and max values are now numerical and are in the array [min, max], not in the form of a string 'min,max'.
 *
 * @ver 2.1
 */
class Kama_Minmax_Post_Meta_Values {

	/** @var string The meta key name where the product price is located. */
	private $meta_key;

	/** @var string The name of the taxonomy. */
	private $taxonomy;

	/** @var string The name of the post type. */
	private $post_type;

	/** @var string The cache transient option name. */
	private $cache_key;

	/** @var int Time for which the data will be refreshed. */
	private $cache_ttl;

	/** @var int Time in seconds after which the script will be triggered when updating a post (product). */
	private $update_timeout = 60;

	public function __construct( array $args ) {
		if( empty( $args['meta_key'] ) || empty( $args['taxonomy'] ) || empty( $args['post_type'] ) ){
			throw new \RuntimeException( 'Required `meta_key` OR `taxonomy` parameters not specified.' );
		}

		$this->meta_key  = $args['meta_key'];
		$this->taxonomy  = $args['taxonomy'];
		$this->post_type = $args['post_type'];
		$this->cache_ttl = (int) ( $args['cache_ttl'] ?? WEEK_IN_SECONDS );

		$this->cache_key = "minmax_{$args['taxonomy']}_{$args['meta_key']}_values";
	}

	public function init(): void {
		add_action( 'init', [ $this, 'check_update_data' ], 99 );
	}

	/**
	 * @return array Array of min-max prices for all taxonomy terms. For example:
	 *     [
	 *         [valid_until] => 1508719235
	 *         [all]  => [ 80, 68000 ]
	 *         [1083] => [ 950, 7300 ]
	 *         [1084] => [ 1990, 3970 ]
	 *         [1085] => [ 200, 3970 ]
	 *         [1086] => [ 2000, 3970 ]
	 *         [1089] => [ 1990, 1990 ]
	 *         [1090] => [ 190, 1990 ]
	 *         [1091] => [ 1590, 1990 ]
	 *     ]
	 */
	public function get_data(): array {
		return get_transient( $this->cache_key ) ?: [];
	}

	/**
	 * @param int|string $term_id_or_all Term id or `all` key to get minmax values for the whole taxonomy.
	 *
	 * @return int[] Min, max pair: `[ 1590, 1990 ]`. Empty array if no data.
	 */
	public function get_term_minmax( $term_id_or_all ): array {
		return $this->get_data()[ $term_id_or_all ] ?? [];
	}

	public function check_update_data(): void {
		add_action( "save_post_{$this->post_type}", [ $this, 'mark_data_for_update' ] );
		add_action( 'deleted_post', [ $this, 'mark_data_for_update' ] );

		if( time() > ( $this->get_data()['valid_until'] ?? 0 ) ){
			$this->update_data();
		}
	}

	/**
	 * Marks the data as outdated one minute after updating the record.
	 */
	public function mark_data_for_update(): void {
		$minmax_data = $this->get_data();
		$minmax_data['valid_until'] = time() + $this->update_timeout;

		set_transient( $this->cache_key, $minmax_data );
	}

	/**
	 * Updates all minmax data at once.
	 */
	public function update_data(): void {

		$minmax_data = [
			'valid_until' => time() + $this->cache_ttl
		];

		$this->add_all_minmax( $minmax_data );
		$this->add_terms_minmax( $minmax_data );

		set_transient( $this->cache_key, $minmax_data );
	}

	private function add_all_minmax( & $minmax_data ): void {
		global $wpdb;

		$sql = str_replace( '{AND_WHERE}', '', $this->minmax_base_sql() );

		$minmax = $wpdb->get_row( $sql, ARRAY_A );

		$minmax_data['all'] = [ (int) $minmax['min'], (int) $minmax['max'] ] + [ 0, 0 ];
	}

	private function add_terms_minmax( & $minmax_data ): void {
		global $wpdb;

		$base_sql = $this->minmax_base_sql();

		$terms_data = self::get_terms_post_ids_data( $this->taxonomy );
		foreach( $terms_data as $term_id => $post_ids ){
			if( empty( $post_ids ) ){
				continue;
			}

			$IN_post_ids = implode( ',', array_map( 'intval', $post_ids ) );

			$minmax = $wpdb->get_row( str_replace( '{AND_WHERE}', "AND post_id IN( $IN_post_ids )", $base_sql ), ARRAY_A );

			if( array_filter( $minmax ) ){
				$minmax_data[ $term_id ] = [ (int) ( $minmax['min'] ?? 0 ), (int) ( $minmax['max'] ?? 0 ) ];
			}
		}
	}

	private function minmax_base_sql(): string {
		global $wpdb;

		return $wpdb->prepare( "
			SELECT MIN( CAST(meta_value as UNSIGNED) ) as min, MAX(CAST(meta_value as UNSIGNED)) as max
			FROM $wpdb->postmeta
			WHERE meta_key = %s AND meta_value > 0
			{AND_WHERE}
			",
			$this->meta_key
		);
	}

	/**
	 * Collects the IDs of all records of all categories into an array with elements like:
	 *      [
	 *          term_id => [ post_id, post_id, ... ],
	 *          ...
	 *      ]
	 * The list of post IDs contains posts from the current category and from all nested subcategories.
	 *
	 * @return array[] Returns empty array if no data.
	 */
	private static function get_terms_post_ids_data( string $taxonomy ): array {
		global $wpdb;

		$cats_data_sql = $wpdb->prepare( "
			SELECT term_id, object_id, parent
				FROM $wpdb->term_taxonomy tax
				LEFT JOIN $wpdb->term_relationships rel ON (rel.term_taxonomy_id = tax.term_taxonomy_id)
				WHERE taxonomy = %s
			",
			$taxonomy
		);

		$terms_data = (array) $wpdb->get_results( $cats_data_sql );

		/**
		 * Reformat the data: where the key will be the category ID, and the value will be an object with data:
		 * parent and object_id - all post IDs (there can be several records in the category).
		 *
		 * Get all terms of the specified taxonomy in the format:
		 *     [
		 *         123 => object {
		 *             parent    => 124
		 *             object_id => [ 12, 13, ... ],
		 *             child     => [],
		 *         }
		 *         124 => ...
		 *     ]
		 */
		$new_terms_data = [];
		foreach( $terms_data as $data ){
			if( ! $new_terms_data[ $data->term_id ] ){
				$new_terms_data[ $data->term_id ] = (object) [
					'parent'    => $data->parent,
					'object_id' => [],
					'child'     => [],
				];
			}

			if( $data->object_id ){
				$new_terms_data[ $data->term_id ]->object_id[] = $data->object_id;
			}
		}
		$terms_data = $new_terms_data;

		/**
		 * Collect subcategories into parent categories (in the 'child' element).
		 * `child` will be a PHP reference to the current category element.
		 * This will allow recursively traversing the multi-level nesting.
		 */
		foreach( $terms_data as $term_id => $data ){
			if( $data->parent ){
				$terms_data[ $data->parent ]->child[] = & $terms_data[ $term_id ]; // reference
			}
		}

		/**
		 * Collect all record IDs of all categories into one array with elements like:
		 *      [
		 *          term_id => [ post_id, post_id, ... ],
		 *          ...
		 *      ]
		 * The list of post IDs contains posts from the current category and from all nested subcategories.
		 */
		$terms_post_ids = [];
		foreach( $terms_data as $term_id => $data ){
			$post_ids = [];

			self::collect_post_ids_recursively( $post_ids, $data );

			$terms_post_ids[ $term_id ] = array_unique( $post_ids );
		}

		return $terms_post_ids;
	}

	/**
	 * Recursively collects object_id into the specified collector $post_ids.
	 */
	private static function collect_post_ids_recursively( &$post_ids, $data ) {

		if( $data->object_id ){
			$post_ids = array_merge( $post_ids, (array) $data->object_id );
		}

		foreach( $data->child as $child_data ){
			self::collect_post_ids_recursively( $post_ids, $child_data );
		}
	}

}

Initialize the class in functions.php (on the init hook, so that all taxonomies are registered by this time):

global $kama_minmax;
$kama_minmax = new Kama_Minmax_Post_Meta_Values( [
	'meta_key'  => 'price',
	'taxonomy'  => 'product_cat',
	'post_type' => 'product',
	'cache_ttl' => DAY_IN_SECONDS, // default WEEK_IN_SECONDS
] );

$kama_minmax->init();

Retrieve data in the code:

global $kama_minmax;
$kama_minmax = $my_minmax->get_data();

In this case, $minmax_data will contain such data:

$minmax_data = [
	[uptime] => 1508719235
	[all]  => [ 80, 68000 ]
	[1083] => [ 950, 7300 ]
	[1084] => [ 1990, 3970 ]
	[1085] => [ 200, 3970 ]
	[1086] => [ 2000, 3970 ]
	[1089] => [ 1990, 1990 ]
	[1090] => [ 190, 1990 ]
	[1091] => [ 1590, 1990 ]
]

Where the array key is the category ID, and the value is 'min, max' price.
The 'all' key contains the min and max price values for the entire taxonomy.

Example: Get the min/max price for category 123:

global $kama_minmax;
$minmax_data = $kama_minmax->get_data();

[ $min, $max ] = $kama_minmax->get_term_minmax( 123 );

echo $min;
echo $max;

[ $min, $max ] = $kama_minmax->get_term_minmax( 'all' );

echo $min;
echo $max;

For code testing, it can be executed, for example, through GET parameters:

global $kama_minmax;

// for testing
# get and display data on the screen
if( isset( $_GET['get_minmax_prices_test'] ) ){
	die( print_r( $kama_minmax->get_data() ) );
}

# forcefully update and display data on the screen
if( isset( $_GET['update_minmax_prices_test'] ) ){
	$kama_minmax->update_data();
	die( print_r( $kama_minmax->get_data() ) );
}

Instead of a conclusion

This code is suitable not only for WooCommerce but also for any store on WP. The essence is to collect all minimum and maximum prices from the specified meta-field for terms of all levels of nesting.