Recently a client requested that the sku be added to the woocommerce product search on his site. He wanted his customers to easily find a product by typing in the sku in the product search bar.
I found most of the code in the codex here but it didn’t work out of the box. So I had to make an adjustment. Here is the finished version. Put it in your functions.php file
if(!function_exists('la_tip_search_join')){
function la_tip_search_join( $join ){
global $wpdb;
$join .= " LEFT JOIN $wpdb->postmeta gm ON (" .
$wpdb->posts . ".ID = gm.post_id AND gm.meta_key='_sku')"; // change to your meta key if not woo
return $join;
}
}
if(!function_exists('la_tip_search_where')){
function la_tip_search_where( $where ){
global $wpdb;
$where = preg_replace(
"/\(\s*{$wpdb->posts}.post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
"({$wpdb->posts}.post_title LIKE $1) OR (gm.meta_value LIKE $1)", $where );
return $where;
}
}
if(!function_exists('la_tip_search_groupby')){
/* grouping by id to make sure no dupes */
function la_tip_search_groupby( $groupby ){
global $wpdb;
$mygroupby = "{$wpdb->posts}.ID";
if( preg_match( "/$mygroupby/", $groupby )) {
// grouping we need is already there
return $groupby;
}
if( !strlen(trim($groupby))) {
// groupby was empty, use ours
return $mygroupby;
}
// wasn't empty, append ours
return $groupby . ", " . $mygroupby;
}
}
if(!function_exists('la_tip_pre_get_posts')){
/* Add sku to product search */
function la_tip_pre_get_posts( $query ) {
// conditions - change the post type clause if you're not searching woocommerce or 'product' post type
if ( is_admin() || ! $query->is_main_query() || ! $query->is_search() || ! get_query_var('post_type')=='product' ){
return;
}
add_filter('posts_join', 'la_tip_search_join' );
add_filter('posts_where', 'la_tip_search_where' );
add_filter('posts_groupby', 'la_tip_search_groupby' );
}
}
add_action( 'pre_get_posts', 'la_tip_pre_get_posts' );I hope this helps you to create a better product search for Woocommerce. As you can see, this code may be adjusted as needed to include other metadata as well. Feel free to Contact Us if you have questions.
