How to Extend WooCommerce Search Results to Show Product Tags, Descriptions, SKU and Price

This article will guide you through on how to Extend WooCommerce search results to show Product Tags, Descriptions, SKU and Price, thereby improving the search experience for your customers.

Jump To Full Snippet

Code Breakdown: Extend WooCommerce search results

By utilising custom WordPress hooks and filters, we can modify the default search behavior to include WooCommerce product tags, descriptions, and prices. Here’s how the provided code achieves this:

Filter Hook: posts_search

add_filter('posts_search', 'ii_extend_woo_search', 10, 2);

This filter modifies the search SQL before it is executed. The ii_extend_woo_search function is hooked into posts_search to customize the search query.

Function: ii_extend_woo_search

function ii_extend_woo_search($searchSql, $query = false) {
    if (is_admin() || !is_a($query, 'WP_Query') || !$query->is_search) {
        return $searchSql;
    }
    if ($searchSql) {
        global $wpdb;
        // Include product tags in the search
        $searchSql = preg_replace(
            '/ (AND|OR) \\('.preg_quote($wpdb->posts).'\\.post_content (NOT )?LIKE \'(.+)\'\\)/U',
            '$0 $1 $2 EXISTS( SELECT 1 FROM '.$wpdb->term_relationships.' JOIN '.$wpdb->term_taxonomy.' USING (term_taxonomy_id) JOIN '.$wpdb->terms.' USING (term_id) WHERE object_id='.$wpdb->posts.'.ID AND taxonomy="product_tag" AND name LIKE \'$3\')',
            $searchSql
        );
        // Include product description, short description, and price in the search
        $searchSql .= " OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} WHERE {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID AND ";
        $searchSql .= "(meta_key = '_price' AND meta_value LIKE '%" . esc_sql($query->query_vars['s']) . "%' ";
        $searchSql .= "OR meta_key = '_regular_price' AND meta_value LIKE '%" . esc_sql($query->query_vars['s']) . "%' ";
        $searchSql .= "OR meta_key = '_sale_price' AND meta_value LIKE '%" . esc_sql($query->query_vars['s']) . "%' ";
        $searchSql .= "OR meta_key = '_sku' AND meta_value LIKE '%" . esc_sql($query->query_vars['s']) . "%' ";
        $searchSql .= "OR meta_key = '_description' AND meta_value LIKE '%" . esc_sql($query->query_vars['s']) . "%' ";
        $searchSql .= "OR meta_key = '_short_description' AND meta_value LIKE '%" . esc_sql($query->query_vars['s']) . "%'))";
    }
    return $searchSql;
}

This function modifies the search SQL to include conditions for WooCommerce product tags, descriptions, short descriptions, and prices. The preg_replace function is used to inject these conditions into the existing search query.

Action Hook: wp_loaded

add_action('wp_loaded', 'ii_remove_default_search');

This action hook ensures that the default search modifications (if any) are removed and our custom search modifications are applied.

Function: ii_remove_default_search

function ii_remove_default_search() {
    // Optionally remove any other default search modifications here if needed
    add_action('pre_get_posts', 'ii_custom_search');
}

This function adds another action to modify the search query before it is executed.

Function: ii_custom_search

function ii_custom_search($query = false) {
    if (is_admin() || !is_a($query, 'WP_Query') || !$query->is_search) {
        return;
    }
    // Apply to all searches
    $postTypes = array('post', 'page', 'product');
    $query->set('post_type', $postTypes);
}

This function ensures that the search applies to posts, pages, and products, enhancing the overall search scope.

Including Product Meta Fields in WooCommerce Search

The code snippet modifies the search SQL query to include WooCommerce product meta fields such as price, regular price, sale price, SKU, description, and short description. Here’s a detailed breakdown of a specific part of this code:

$searchSql .= " OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} WHERE {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID AND ";
$searchSql .= "(meta_key = '_price' AND meta_value LIKE '%" . esc_sql($query->query_vars['s']) . "%' ";

Breakdown

1. Appending to Search SQL:
   $searchSql .= " OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} WHERE {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID AND "; This line appends an additional condition to the existing search SQL query. The EXISTS keyword is used to check if at least one row matches the specified condition in the subquery.
2. Subquery Explanation:
   SELECT 1 FROM {$wpdb->postmeta} This part of the query selects a single column (represented by 1) from the postmeta table, which stores meta information for posts (including WooCommerce products).
3. Joining postmeta with posts:
   WHERE {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID This condition links the postmeta table with the posts table. It ensures that the meta data belongs to the same post (or product) being searched. The post_id in the postmeta table should match the ID in the posts table.
4. Filtering by Meta Key and Value:
   AND (meta_key = '_price' AND meta_value LIKE '%" . esc_sql($query->query_vars['s']) . "%' This part of the query adds another condition to filter the postmeta records:
   • meta_key = '_price': This specifies that we are interested in the _price meta key, which stores the product price.
   • meta_value LIKE '%" . esc_sql($query->query_vars['s']) . "%': This checks if the value of _price contains the search term entered by the user. The esc_sql function is used to escape the search term for safe use in the SQL query. The % wildcards are used for a partial match (similar to a “contains” search).

Example Scenario

Suppose a user searches for the term “50”. This code will modify the search query to include products where the _price meta value contains “50”. For instance, it would match products with prices like “50.00”, “150”, or “250.99”.

Extending the Concept

Similar conditions are added for other meta keys like _regular_price, _sale_price, _sku, _description, and _short_description, ensuring that the search functionality covers various important product attributes.

By implementing this code, you can significantly improve and extend WooCommerce search results. Customers will be able to find products based on tags, descriptions, and prices, leading to a better user experience and potentially higher sales.