Search Woocommerce Products by sku and custom fields

Woocommerce by default allows you to search through your product catalog using your products titles, by splitting your search query into individual words and if products matches all of these then those products are returned. This is useful until you start adding custom fields to your products, then for example if you have customers who know the SKU of a product they search for that if nothing comes up this may be a loss of a sale on your website.


/**
 * Add sku, author, publisher and format to product search
 */
 
// hook into wp pre_get_posts
add_action('pre_get_posts', 'jc_woo_search_pre_get_posts');
 
/**
 * Add custom join and where statements to product search query
 * @param  mixed $q query object
 * @return void
 */
function jc_woo_search_pre_get_posts($q){
 
    if ( is_search() ) {
        add_filter( 'posts_join', 'jc_search_post_join' );
        add_filter( 'posts_where', 'jc_search_post_excerpt' );
    }
}
 
/**
 * Add Custom Join Code for wp_mostmeta table
 * @param  string $join 
 * @return string
 */
function jc_search_post_join($join = ''){
 
    global $wp_the_query;
 
    // escape if not woocommerce searcg query
    if ( empty( $wp_the_query->query_vars['wc_query'] ) || empty( $wp_the_query->query_vars['s'] ) )
            return $join;
 
    $join .= "INNER JOIN wp_postmeta AS jcmt1 ON (wp_posts.ID = jcmt1.post_id)";
    return $join;
}
 
/**
 * Add custom where statement to product search query
 * @param  string $where
 * @return string
 */
function jc_search_post_excerpt($where = ''){
 
    global $wp_the_query;
 
    // escape if not woocommerce search query
    if ( empty( $wp_the_query->query_vars['wc_query'] ) || empty( $wp_the_query->query_vars['s'] ) )
            return $where;
 
    $where = preg_replace("/post_title LIKE ('%[^%]+%')/", "post_title LIKE $1) 
                OR (jcmt1.meta_key = '_sku' AND CAST(jcmt1.meta_value AS CHAR) LIKE $1)
                OR  (jcmt1.meta_key = '_author' AND CAST(jcmt1.meta_value AS CHAR) LIKE $1)
                OR  (jcmt1.meta_key = '_publisher' AND CAST(jcmt1.meta_value AS CHAR) LIKE $1)
                OR  (jcmt1.meta_key = '_format' AND CAST(jcmt1.meta_value AS CHAR) LIKE $1 ", $where);
 
    return $where;
}