/*
* Woocommerce Filter by on sale
*/
function custom_woocommerce_filter_by_onsale($output) {
global $wp_query;
$selected = filter_input(INPUT_GET, ‘product_sale’, FILTER_VALIDATE_INT);
if ($selected == false) {
$selected = 0;
}
$output .= ‘
<select id=”dropdown_product_sale” name=”product_sale”>
<option value=””>Filter by sale</option>
<option selected=”selected” value=”1″>On sale</option>
<option selected=”selected” value=”2″>Not on sale</option>
</select>
‘;
return $output;
}
add_action(‘woocommerce_product_filters’, ‘custom_woocommerce_filter_by_onsale’);
/*
* Woocommerce Filter by on sale where statement
*/
function custom_woocommerce_filter_by_onsale_where_statement($where) {
global $wp_query, $wpdb;
// Get selected value
$selected = filter_input(INPUT_GET, ‘product_sale’, FILTER_VALIDATE_INT);
// Only trigger if required
if (!is_admin() || $_GET[‘post_type’] != “product” || !$selected) {
return $where;
}
$productsIDs = [];
if ($selected == 1) {
$querystr = ‘
SELECT p.ID, p.post_parent
FROM ‘ . $wpdb–>posts . ‘ p
WHERE p.ID IN (
SELECT post_id FROM ‘ . $wpdb–>postmeta . ‘ pm WHERE pm.meta_key = “_sale_price” AND pm.meta_value > \’\’
)
‘;
$pageposts = $wpdb–>get_results($querystr, OBJECT);
$productsIDs = array_map(function($n){
return $n–>post_parent > 0 ? $n–>post_parent : $n–>ID;
}, $pageposts);
} elseif ($selected == 2) {
$querystr = ‘
SELECT p.ID, p.post_parent
FROM ‘ . $wpdb–>posts . ‘ p
WHERE p.ID NOT IN (
SELECT post_id FROM ‘ . $wpdb–>postmeta . ‘ pm WHERE pm.meta_key = “_sale_price” AND pm.meta_value > \’\’
)
‘;
$pageposts = $wpdb–>get_results($querystr, OBJECT);
$productsIDs = array_map(function($n){
return $n–>post_parent > 0 ? $n–>post_parent : $n–>ID;
}, $pageposts);
}
$where .= ‘ AND ‘ . $wpdb–>posts . ‘.ID IN (‘ . implode(“,”, $productsIDs) . ‘) ‘;
return $where;
}
add_filter(‘posts_where’ , ‘custom_woocommerce_filter_by_onsale_where_statement’);