Sorting products by price ( regular + sale price )

Question

I'm using WooCommerce and I'm trying to filter my products by price. My problem is that the meta_key is only taking into account the regular price and not the sale price.

As a result if a product is on sale, the sale price is ignored by my filter and all of the products are ordered by the regular price.

Is there a way I can order my products by both the regular price and the sale price? Any help will be greatly appreciated!

$args = array(
'post_type'      => 'product',
'post_status'    => 'publish',
'posts_per_page' => $posts_per_page,
'taxonomy'       => 'product_cat',
's'              => $search_term,
'orderby'        => 'meta_value_num',
'order'          => 'DESC',
'meta_key'       => '_price',
);

Answer 1

When your are updating your product regular price is saved to _regular_price and sale price is saved to _sale_price meta_key. Also WooCommerce will figure out which is the current price sale or regular and will save it to _price meta_key. There is no need to sort products by two _regular_price and _sale_price meta_keys. Your current prices should be stored in _price meta_key and this key should be used to sort products by price.

Try to investigate your SQL $query->request and database records to figure this out.

Keep in mind that if your sale price is scheduled you have to make sure that WooCommerce cron jobs are working correctly to update _price meta_key.