Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Exclude WC categories from widget that only contain out of stock products Raw
<?php
// Inspired by this WPSE question: http://wordpress.stackexchange.com/questions/74896/woocommerce-product-category-widget-hide-categories-that-have-no-products-in-s
add_filter( 'woocommerce_product_categories_widget_args', 'wpse_74896' );
function wpse_74896( $args ) {
global $wpdb;
if ( ! empty( $args['current_category'] ) ) {
$current = $args['current_category'];
$current_clause = "( term_id = '{$current}' OR parent = '{$current}' ) AND ";
} else {
$current_clause = "";
}
$query = "SELECT term_id
FROM $wpdb->term_taxonomy
WHERE {$current_clause} term_taxonomy_id IN (
SELECT DISTINCT term_taxonomy_id
FROM $wpdb->term_relationships
WHERE object_id IN (
SELECT DISTINCT post_id
FROM $wpdb->postmeta
WHERE post_id IN (
SELECT id FROM $wpdb->posts WHERE post_type LIKE 'product' AND post_status LIKE 'publish'
)
AND post_id NOT IN (
SELECT DISTINCT post_id
FROM $wpdb->postmeta
WHERE (
( meta_key = '_stock_status' AND meta_value = 'outofstock' )
OR ( meta_key = '_stock' AND meta_value != '' AND meta_value < 1 )
)
)
)
)
AND taxonomy LIKE 'product_cat'";
$result = $wpdb->get_col( $query );
if ( is_array( $result ) && ! empty( $result ) ) {
$args['include'] = $result;
}
return $args;
}
@karpstrucking
Copy link
Author

karpstrucking commented Jan 16, 2016

Ideas for potential improvement, in no particular order:

  • optimize SQL query
  • store results in a transient (and invalidate transient whenever a product's inventory level is changed?)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment