mysql - Woocommerce - 扩展搜索不会返回多字段搜索，可能存在 SQL 问题。为什么？

Question

我正在使用 Woocommerce 为客户编写自定义 WordPress 网站。该平台对于一切都很完美，只有一个问题:客户的项目具有自定义属性，并且他希望能够在其中进行搜索。

属性问题本身是 woocommerce 允许的。为了增强搜索，我在 functions.php 中添加了一段代码，为每个帖子添加了额外的元，目的是稍后搜索元值，松散地基于中给出的值this question 。执行此操作的代码如下:

function wcproduct_set_attributes($id) {

    $materials = get_the_terms( $id, 'pa_material');

    $i = 0;
    $mat_list = '';

    foreach($materials as $material){
        if($i == 0)
            $mat_list = $mat_list . $material->name;
        else
            $mat_list = $mat_list . ", " . $material->name;
        $i++;
    }

    $sizeozs = get_the_terms( $id, 'pa_sizeoz');

    $i = 0;
    $sizeoz_list = '';

    foreach($sizeozs as $sizeoz){
        if($i == 0)
            $sizeoz_list = $sizeoz_list . $sizeoz->name;
        else
            $sizeoz_list = $sizeoz_list . ", " . $sizeoz->name;
        $i++;
    }


    // Now update the post with its new attributes
    update_post_meta($id, '_material', $mat_list);
    update_post_meta($id, '_sizeoz', $sizeoz_list);

}

// After inserting post
add_action( 'save_post_product', 'wcproduct_set_attributes', 10);

之后，我添加了通过网络找到的另一个功能 ( here )，该功能允许 woocommerce 搜索在自定义元信息中进行搜索。根据我的需要进行定制后，我最终得到了这样的结果:

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



    return $where;
}

现在系统可以完美地搜索标题、内容和属性中的单个参数。当我尝试同时搜索两个属性时，问题就出现了。例如，我有一种名为 Titanite 的 Material ，尺寸以盎司为单位设置为 5 盎司。如果我搜索 Titanite，我会得到两种产品。如果我搜索 5oz，我会得到一个带有它的单一产品，这也是我搜索 Titanite 时出现的两个产品之一。到目前为止，一切顺利。

如果我搜索 Titanite 5oz，就会出现问题，因为在这种情况下，由于某种原因，即使有一个项目的 Material 设置为 Titanite，尺寸设置为 5oz，我也会得到空的搜索结果。我盯着代码看了两天也没找到原因。我已经将 SQL 代码注入(inject)到第二个函数中的搜索中，如下所示:

AND (((wp_posts.post_title LIKE '%titanite%') OR (jcmt1.meta_key = '_sizeoz' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%titanite%') OR (jcmt1.meta_key = '_material' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%titanite%') OR (post_excerpt LIKE '%titanite%') OR (wp_posts.post_content LIKE '%titanite%')) AND ((wp_posts.post_title LIKE '%5oz%') OR (jcmt1.meta_key = '_sizeoz' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%5oz%') OR (jcmt1.meta_key = '_material' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%5oz%') OR (post_excerpt LIKE '%5oz%') OR (wp_posts.post_content LIKE '%5oz%'))) AND ( ( wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','search') ) ) AND wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')

但也没有运气。为了便于阅读而格式化后，我最终得到:

AND
 (
((wp_posts.post_title LIKE '%titanite%')
OR (jcmt1.meta_key = '_sizeoz' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%titanite%') 
OR (jcmt1.meta_key = '_material' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%titanite%')
OR (post_excerpt LIKE '%titanite%')
OR (wp_posts.post_content LIKE '%titanite%')) 
    AND 
((wp_posts.post_title LIKE '%5oz%')
OR (jcmt1.meta_key = '_sizeoz' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%5oz%') 
OR (jcmt1.meta_key = '_material' AND CAST(jcmt1.meta_value AS CHAR) LIKE '%5oz%') 
OR (post_excerpt LIKE '%5oz%') 
OR (wp_posts.post_content LIKE '%5oz%'))

) 
AND 
( ( wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','search') ) )
 AND wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')

...我根本找不到任何问题。据我所知，它的编写方式应该给我结果，其中元包含 Titanite 和 5oz，即使在单独的实例上，如所使用的 OR 值给出的那样。当您搜索 Titanite 和 5oz 时返回的任何项目都应该是搜索的一部分，那么为什么它不存在呢？我是否在这里遗漏了一些非常明显的东西，或者该过程是否有某种问题？

Answer 1

在您的jc_search_post_excerpt上尝试一下

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 = '_color' AND CAST(jcmt1.meta_value AS CHAR) LIKE $1)
                OR  (jcmt1.meta_key = '_size' AND CAST(jcmt1.meta_value AS CHAR) LIKE $1", $where);
    $where = str_replace(') AND ((wp_posts.post_title',' OR (wp_posts.post_title',$where);

    return $where;
}