Extending WooCommerce search query to include custom fields

Woocommerce by default allows you to search through your product catalog by 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.

How to Add custom fields to Woocommerce product search

  1. Hook into the WooCommerce search query
  2. Amend the WooCommerce search query to look for the custom fields

Hook into the WooCommerce search query

Hooking into the WooCommerces search query can be done by accessing the WordPress pre_get_posts action. This action is used in all wordpress queries, since we only want to hook into the woocommerce search query we can use is_search to do part of this just now. is_search will make sure we only attach to search results, at this moment we cant filter out woocommerce searches only that it is a wordpress search, this will be filtered out when the query has been started.

Next we have to create a MYSQL join to search custom fields, and create our WHERE statement, to do this we use the filters posts_join and posts_where to alter the wordpress query.

// 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 fields to WooCommerce Search

The function jc_search_post_join checks to see if we are on a woocommerce search query, and if we are joins the wp_postmeta table into the query as jcmt1.

/**
 * 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;
}

The function jc_search_post_excerpt checks to see if we are on a woocommerce search query just like the function before, and if we are creates our mysql WHERE statement, querying the previously joined wp_postmeta table called jcmt1. To change the search query we use regex to search and replace the current WHERE statement for “post_title LIKE (‘%search_string%’)” and extend it to search in the _sku and other custom fields.

/**
 * 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;
}

Search Woocommerce Products by custom fields example:

Copy and past this code into your wordpress themes functions file:

<?php
/**
 * 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;
}
?>

Search WooCommerce products by description

The following function has been modified to search the product title, product description and sku to show how simple it is to customize woocommerce search queries.

/**
 * 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 (post_content LIKE $1)
                OR (jcmt1.meta_key = '_sku' AND CAST(jcmt1.meta_value AS CHAR) LIKE $1 ", $where);
 
    return $where;
}


Liked this article? help spread the word.