Woocommerce – Problem sorting and filtering products with different tax rates by price

The question:

Setup (wordpress and woo only; fresh install):
Prices entered with tax: No, I will enter prices exclusive of tax
Display prices in the shop: Including tax
Display prices during cart and checkout: Including tax
Tax Rates: Standard 23%, Reduced 5%

Product 1: price 100, tax rate standard (23%), front end price 123
Product 2: price 100, tax rate reduced (5%), front end price 105

Result:
When sorting products by price I get:
(low -> high): Product 1 (123), Product 2 (105)
(high -> low): Product 2 (105), Product 1 (123)
Tax rate is skipped during sorting?

Similar problem applies to filtering by price. If i set filter to: from 120 to 130. I get both products in the results. So I am assuming filtering doesn’t respect different tax rates. And applies standard tax rate to both products.

Is the only way around this to enter product prices including tax?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Wasn’t able to find any solution to my problem so I made a simple plugin:

<?php
/**
 * Plugin Name: calisia-sort-and-filter
 * Author: Tomasz Boroń
 */

//order by; add new options and remove defaults
add_filter( 'woocommerce_default_catalog_orderby_options', 'calisia_sort_and_filter::add_new_postmeta_orderby' );
add_filter( 'woocommerce_catalog_orderby', 'calisia_sort_and_filter::add_new_postmeta_orderby' );
//order by; arguments
add_filter( 'woocommerce_get_catalog_ordering_args', 'calisia_sort_and_filter::add_postmeta_ordering_args' );
//price filtering
add_action('woocommerce_product_query', 'calisia_sort_and_filter::min_max_price_handler');
//save meta _price_with_tax on every product update
add_action( 'woocommerce_update_product', 'calisia_sort_and_filter::product_save', 10, 1 );
//set meta _price_with_tax for all existing products
//to run: create new page with slug "calisia-add-price-meta" and visit it
add_action( 'get_header', 'calisia_sort_and_filter::add_meta_price_to_all_products' );

class calisia_sort_and_filter{

    //NEW ORDER BY OPTIONS, REMOVING DEFAULTS
    public static function add_new_postmeta_orderby( $sortby ) {
    
        // Adjust the text as desired
        $sortby['price_with_tax_asc'] = __( 'Sort by price: low to high', 'woocommerce' );
        $sortby['price_with_tax_desc'] = __( 'Sort by price: high to low', 'woocommerce' );
    
        //remove default price order by
        unset($sortby['price']);
        unset($sortby['price-desc']);
    
    
        return $sortby;
    }

    //ARGUMENTS FOR NEW ORDER BY OPTIONS
    public static function add_postmeta_ordering_args( $sort_args ) {
        
        $orderby_value = isset( $_GET['orderby'] ) ? wc_clean( $_GET['orderby'] ) : apply_filters( 'woocommerce_default_catalog_orderby', get_option( 'woocommerce_default_catalog_orderby' ) );
        switch( $orderby_value ) {
        
            // Name your sortby key whatever you'd like; must correspond to the $sortby in the next function
            case 'price_with_tax_asc':
                $sort_args['orderby']  = 'meta_value_num'; //meta_value for string
                // Sort by meta_value because we're using alphabetic sorting
                $sort_args['order']    = 'asc';
                $sort_args['meta_key'] = '_price_with_tax';
                // use the meta key you've set for your custom field, i.e., something like "location" or "_wholesale_price"
                break;
                
            case 'price_with_tax_desc':
                $sort_args['orderby']  = 'meta_value_num'; //meta_value for string
                // Sort by meta_value because we're using alphabetic sorting
                $sort_args['order']    = 'desc';
                $sort_args['meta_key'] = '_price_with_tax';
                // use the meta key you've set for your custom field, i.e., something like "location" or "_wholesale_price"
                break;
            
        }
        
        return $sort_args;
    }

    //PRICE FILTERING
    public static function min_max_price_handler($q) {
        global $wpdb, $wp_query;

        if (! $wp_query->is_main_query() || ( ! isset($_GET['max_price']) && ! isset($_GET['min_price']))) {
            return;
        }

        // Remove default Woocommerce min/max price request handler
        remove_filter('posts_clauses', [WC()->query, 'price_filter_post_clauses'], 10);

        $current_min_price = isset($_GET['min_price']) ? floatval(wp_unslash($_GET['min_price'])) : 0;
        $current_max_price = isset($_GET['max_price']) ? floatval(wp_unslash($_GET['max_price'])) : PHP_INT_MAX;

        $sql = "SELECT DISTINCT post_id FROM wp_postmeta WHERE meta_key = '_price_with_tax' AND meta_value >= %f AND meta_value <= %f";

        $results = $wpdb->get_results(
            $wpdb->prepare(
                $sql,
                $current_min_price,
                $current_max_price
            ),
            ARRAY_A
        );

        $results = array_column($results, 'post_id');

        if (is_array($results)) {
            $q->set('post__in', $results);
        }
    }

    public static function product_save( $product_id ) {
        $product = wc_get_product( $product_id );
        $price_incl_tax = wc_get_price_including_tax($product);
        update_post_meta( $product->get_id(), '_price_with_tax', $price_incl_tax );
    }

    public static function add_meta_price_to_all_products(){
        if (!is_page('calisia-add-price-meta'))
            return;
    
    
        if(!current_user_can('administrator'))
            return;
    
        global $post;
    
    
        // fetch all products
        $args = array(
            'post_type' => 'product',
            'posts_per_page' => -1
        );
        $products = new WP_Query( $args );
    
    
        $_pf = new WC_Product_Factory();
    
        if ( $products->have_posts() ){
            while ( $products->have_posts() ){
                $products->the_post();
    
    
               //SAVE
                $product = $_pf->get_product($post->ID);
                $price_incl_tax = wc_get_price_including_tax($product);
                update_post_meta( $post->ID, '_price_with_tax', $price_incl_tax );
                echo "<br>Product id:" . $post->ID . " total price:" . $price_incl_tax;
    
    
                //SHOW
                /*
                $product_attr = get_post_meta( $post->ID, '_price_with_tax' );
                // displaying the array of values (just to test and to see output)
                echo var_dump( $product_attr[0] );*/
            }
        }
    
        wp_die();
    }
}

How it works?
It adds extra meta value for every existing and new product, called “_price_with_tax”. Value of that meta field is used to filter and sort by price.

Usage:

  1. Install and activate
  2. Create page with slug “calisia-add-price-meta” and visit it as admin (this will create meta fields for all existing products)
  3. Filtering and sorting by price should work as expected now.

Sources:
https://www.skyverge.com/blog/sort-woocommerce-products-custom-fields/
https://stackoverflow.com/questions/65360345/override-standard-request-processing-for-min-and-max-price-in-woocommerce-filter


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment