WooCommerce产品搜索支持SKU(2021)

最后更新于 2022 年 10 月 28 日

WooCommerce前台搜索只会从标题、内容、摘要里搜索，产品SKU有时比较重要，但它存储在custom field里，默认无法通过SKU搜索产品。本文介绍的方法可以让产品搜索支持SKU。

默认搜索

假设我要搜“SLK3423”这个SKU，默认搜索的SQL语句如下所示，可以看出只搜了标题、摘要和内容。

SELECT SQL_CALC_FOUND_ROWS wp_posts.id
FROM   wp_posts
WHERE  1 = 1
       AND ( wp_posts.id NOT IN (SELECT object_id
                                 FROM   wp_term_relationships
                                 WHERE  term_taxonomy_id IN ( 6 )) )
       AND (( ( wp_posts.post_title LIKE '%SLK3423%' )
               OR ( wp_posts.post_excerpt LIKE '%SLK3423%' )
               OR ( wp_posts.post_content LIKE '%SLK3423%' ) ))
       AND wp_posts.post_type = 'product'
       AND ( wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private' )
GROUP  BY wp_posts.id
ORDER  BY wp_posts.post_title LIKE '%SLK3423%' DESC,
          wp_posts.post_date DESC

让搜索支持custom field

从WordPress角度考虑，只要让搜索支持custom field，不就能搜SKU了吗。Adam J. Balée在Search WordPress by Custom Fields without a Plugin中介绍了让WordPress的搜索支持custom field的方法。代码如下：

<?php
/**
 * Extend WordPress search to include custom fields
 *
 * https://adambalee.com
 */

/**
 * Join posts and postmeta tables
 *
 * http://codex.wordpress.org/Plugin_API/Filter_Reference/posts_join
 */
function cf_search_join( $join ) {
    global $wpdb;

    if ( is_search() ) {    
        $join .=' LEFT JOIN '.$wpdb->postmeta. ' AS P1 ON '. $wpdb->posts . '.ID = P1.post_id ';
    }

    return $join;
}
add_filter('posts_join', 'cf_search_join' );

/**
 * Modify the search query with posts_where
 *
 * http://codex.wordpress.org/Plugin_API/Filter_Reference/posts_where
 */
function cf_search_where( $where ) {
    global $pagenow, $wpdb;

    if ( is_search() ) {
        $where = preg_replace(
            "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
            "(".$wpdb->posts.".post_title LIKE $1) OR (P1.meta_value LIKE $1)", $where );
    }

    return $where;
}
add_filter( 'posts_where', 'cf_search_where' );

/**
 * Prevent duplicates
 *
 * http://codex.wordpress.org/Plugin_API/Filter_Reference/posts_distinct
 */
function cf_search_distinct( $where ) {
    global $wpdb;

    if ( is_search() ) {
        return "DISTINCT";
    }

    return $where;
}
add_filter( 'posts_distinct', 'cf_search_distinct' );

将这段代码放进主题的functions.php中，搜索就支持custom field了，sku也属于custom field，自然就能通过sku查找产品。

只搜索SKU字段

产品其它custom field可能含有和sku类似的字符串，但并不是我们想要的，我们希望只匹配SKU而忽略其它custom field，这时我们可以这样修改一下 posts_where：

function cf_search_where( $where ) {
    global $pagenow, $wpdb;

    if ( is_search() ) {
        $where = preg_replace(
            "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
            "(".$wpdb->posts.".post_title LIKE $1) OR (".$wpdb->postmeta.".meta_key='_sku' AND ".$wpdb->postmeta.".meta_value LIKE $1)", $where );
    }

    return $where;
}
add_filter( 'posts_where', 'cf_search_where' );

这样修改后，查询产品的SQL语句变成了下面这样：

SELECT SQL_CALC_FOUND_ROWS distinct wp_posts.ID
FROM   wp_posts
       LEFT JOIN wp_postmeta
              ON wp_posts.id = wp_postmeta.post_id
WHERE  1 = 1
       AND ( wp_posts.id NOT IN (SELECT object_id
                                 FROM   wp_term_relationships
                                 WHERE  term_taxonomy_id IN ( 6 )) )
       AND (( ( wp_posts.post_title LIKE '%SLK3423%' )
               OR ( wp_postmeta.meta_key = '_sku' AND wp_postmeta.meta_value LIKE '%SLK3423%' )
               OR ( wp_posts.post_excerpt LIKE '%SLK3423%' )
               OR ( wp_posts.post_content LIKE '%SLK3423%' ) ))
       AND wp_posts.post_type = 'product'
       AND ( wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private' )
GROUP  BY wp_posts.id
ORDER  BY wp_posts.post_title LIKE '%SLK3423%' DESC,
          wp_posts.post_date DESC

注意：这种方法只会搜索post_type是product的产品，variable product的variation可以有自己的sku，variation的post_type是product_variation，所以这种方法是搜不到variation的sku的。

为什么不能用WooCommerce Meta Query

WooCommerce的product search有一个action叫woocommerce_product_query，可以修改Product Query参数，搜索SKU是meta query，通过这个action添加一个meta query不就好了吗？

add_action( 'woocommerce_product_query', 'sola_wc_meta_query', 10, 2 );
function sola_wc_meta_query( $query, $wc_query ){
    if( $query->is_search() ){
        $meta_query[] =  array(
            array(
                'key'=> '_sku',
                'value' => sanitize_text_field( $query->get('s') ),
                'compare'=> 'LIKE'
            )
        );
        $query->set( 'meta_query', $meta_query ); 
    }
}

这样加完后是搜不到sku的，为什么？看看它生成的SQL就明白了，如下所示，这段SQL要找post_title和meta_value中都含有搜索关键词的产品，除非你把SKU写到标题里，否则是找不到的。我们想要OR的关系，而不是AND。而前面的方法用正则表达式把AND替换成了OR。

SELECT SQL_CALC_FOUND_ROWS wp_posts.id
FROM   wp_posts
       INNER JOIN wp_postmeta
               ON ( wp_posts.id = wp_postmeta.post_id )
WHERE  1 = 1
       AND ( wp_posts.id NOT IN (SELECT object_id
                                 FROM   wp_term_relationships
                                 WHERE  term_taxonomy_id IN ( 6 )) )
       AND (( ( wp_posts.post_title LIKE '%SLK3423%' )
               OR ( wp_posts.post_excerpt LIKE '%SLK3423%' )
               OR ( wp_posts.post_content LIKE '%SLK3423%' ) ))
       AND ((( wp_postmeta.meta_key = '_sku'AND wp_postmeta.meta_value LIKE '%SLK3423%' )))
       AND wp_posts.post_type = 'product'
       AND ( wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private' )
GROUP  BY wp_posts.id