OK, we as we can see, WooCommerce widgets don’t take into account our SKU database query. To fix this you will need to edit a few files:
1. First one – wp-content/plugins/woocommerce/includes/widgets/class-wc-widget-price-filter.php
change this code
if ( $search = WC_Query::get_main_search_query_sql() ) {
$sql .= ' AND ' . $search;
}
to this one
if ( $search = WC_Query::get_main_search_query_sql() ) {
$search_ids = array();
// search for variations with a matching sku and return the parent.
$sku_to_parent_id = $wpdb->get_col( $wpdb->prepare( "SELECT p.post_parent as post_id FROM {$wpdb->posts} as p join {$wpdb->postmeta} pm on p.ID = pm.post_id and pm.meta_key='_sku' and pm.meta_value LIKE '%%%s%%' where p.post_parent <> 0 group by p.post_parent", wc_clean( $_GET['s'] ) ) );
//Search for a regular product that matches the sku.
$sku_to_id = $wpdb->get_col( $wpdb->prepare( "SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key='_sku' AND meta_value LIKE '%%%s%%';", wc_clean( $_GET['s'] ) ) );
$search_ids = array_merge( $search_ids, $sku_to_id, $sku_to_parent_id );
$search_ids = array_filter( array_map( 'absint', $search_ids ) );
$sql .= " AND " . $search;
if ( sizeof( $search_ids ) > 0 ) {
$sql = str_replace( '))', ") OR ({$wpdb->posts}.ID IN (" . implode( ',', $search_ids ) . ")))", $sql );
}
}
2. In this file woodmart/inc/widgets/class-widget-layered-nav.php
replace this
if ( $search = WC_Query::get_main_search_query_sql() ) {
$query['where'] .= ' AND ' . $search;
}
with this part
if ( $search = WC_Query::get_main_search_query_sql() ) {
$search_ids = array();
// search for variations with a matching sku and return the parent.
$sku_to_parent_id = $wpdb->get_col( $wpdb->prepare( "SELECT p.post_parent as post_id FROM {$wpdb->posts} as p join {$wpdb->postmeta} pm on p.ID = pm.post_id and pm.meta_key='_sku' and pm.meta_value LIKE '%%%s%%' where p.post_parent <> 0 group by p.post_parent", wc_clean( $_GET['s'] ) ) );
//Search for a regular product that matches the sku.
$sku_to_id = $wpdb->get_col( $wpdb->prepare( "SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key='_sku' AND meta_value LIKE '%%%s%%';", wc_clean( $_GET['s'] ) ) );
$search_ids = array_merge( $search_ids, $sku_to_id, $sku_to_parent_id );
$search_ids = array_filter( array_map( 'absint', $search_ids ) );
$query['where'] .= ' AND ' . $search;
if ( sizeof( $search_ids ) > 0 ) {
$query['where'] = str_replace( '))', ") OR ({$wpdb->posts}.ID IN (" . implode( ',', $search_ids ) . ")))", $query['where'] );
}
}
Kind Regards
.
