WooCommerce admin products filter by on sale
I needed the ability to filter the WooCommerce products list within admin by products which where on sale. This was to allow the client to easily find products they have put on offer within certain categories. The below code achieves this.
It does need some work, as it currently ignores the on sale start and end dates, however you should be able to easily modify it for this purpose.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
/* * Woocommerce Filter by on sale */ function custom_woocommerce_filter_by_onsale($output) { global $wp_query; $selected = 0; if (isset($_GET['product_sale'])) { $selected = (int)$_GET['product_sale']; } $output .= ' <select name="product_sale" id="dropdown_product_sale"> <option value="">Filter by sale</option> <option value="1" ' . (($selected === 1) ? 'selected="selected"' : '') . '>On sale</option> <option value="2" ' . (($selected === 2) ? 'selected="selected"' : '') . '>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; if (!is_admin() || $_GET['post_type'] != "product" || !isset($_GET['product_sale']) || $_GET['product_sale'] < = 0) { return $where; } $productsIDs = []; if ($_GET['product_sale'] == 1) { $querystr = ' SELECT p.ID 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->ID; }, $pageposts); } elseif ($_GET['product_sale'] == 2) { $querystr = ' SELECT p.ID 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->ID; }, $pageposts); } $where .= ' AND ' . $wpdb->posts . '.ID IN (' . implode(",", $productsIDs) . ') '; return $where; } add_filter('posts_where' , 'custom_woocommerce_filter_by_onsale_where_statement'); |