WooCommerce

WooCommerce产品搜索支持SKU(2021)

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. ' ON '. $wpdb->posts . '.ID = ' . $wpdb->postmeta . '.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 (".$wpdb->postmeta.".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

留言

您的电子邮箱地址不会被公开。 必填项已用*标注