如何修改Enovathemes插件SQL查询，按用户自定义字段匹配分类法过滤产品

代码修改方案 #

调整逻辑说明 #

1. 函数执行前置查询获取当前用户对应的product_country自定义分类术语ID，增加异常判断兜底逻辑，避免未登录/用户未设置国家时报错
2. 对两类搜索场景（指定商品分类搜索、全量商品搜索）的SQL语句新增分类筛选条件，仅返回对应用户国家的商品

完整修改后代码 #

function search_product() {
    global $wpdb, $woocommerce;

    // 新增：获取用户所属国家对应product_country分类ID
    $current_user = wp_get_current_user();
    $country = get_the_author_meta( 'country', $current_user->ID );
    $country_term_id = 0;
    if($country){
        $term = get_term_by('slug', $country, 'product_country');
        if($term) $country_term_id = $term->term_id;
    }
    // 兜底逻辑：未获取到有效国家ID时直接返回空结果，可根据业务需求自行调整
    if(!$country_term_id) {
        echo '';
        die();
    }

    if (isset($_POST['keyword']) && !empty($_POST['keyword'])) {
        $keyword = $_POST['keyword'];
        if (isset($_POST['category']) && !empty($_POST['category'])) {
            $category = $_POST['category'];
            // 新增了国家分类筛选条件
            $querystr = "SELECT DISTINCT * FROM $wpdb->posts AS p
            LEFT JOIN $wpdb->term_relationships AS r ON (p.ID = r.object_id)
            INNER JOIN $wpdb->term_taxonomy AS x ON (r.term_taxonomy_id = x.term_taxonomy_id)
            INNER JOIN $wpdb->terms AS t ON (r.term_taxonomy_id = t.term_id)
            WHERE p.post_type IN ('product')
            AND p.post_status = 'publish'
            AND x.taxonomy = 'product_cat'
            AND (
                (x.term_id = {$category})
                OR
                (x.parent = {$category})
            )
            -- 新增：国家分类筛选
            AND p.ID IN (
                SELECT r.object_id 
                FROM $wpdb->term_relationships r
                INNER JOIN $wpdb->term_taxonomy x ON r.term_taxonomy_id = x.term_taxonomy_id
                WHERE x.taxonomy = 'product_country' AND x.term_id = {$country_term_id}
            )
            AND (
                (p.ID IN (SELECT post_id FROM $wpdb->postmeta WHERE meta_key = '_sku' AND meta_value LIKE '%{$keyword}%'))
                OR
                (p.post_content LIKE '%{$keyword}%')
                OR
                (p.post_title LIKE '%{$keyword}%')
            )
            ORDER BY t.name ASC, p.post_date DESC;";
        } else {
            // 新增了国家分类筛选条件
            $querystr = "SELECT DISTINCT $wpdb->posts.*
            FROM $wpdb->posts, $wpdb->postmeta
            WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id
            AND (
                ($wpdb->postmeta.meta_key = '_sku' AND $wpdb->postmeta.meta_value LIKE '%{$keyword}%')
                OR
                ($wpdb->posts.post_content LIKE '%{$keyword}%')
                OR
                ($wpdb->posts.post_title LIKE '%{$keyword}%')
            )
            -- 新增：国家分类筛选
            AND $wpdb->posts.ID IN (
                SELECT r.object_id 
                FROM $wpdb->term_relationships r
                INNER JOIN $wpdb->term_taxonomy x ON r.term_taxonomy_id = x.term_taxonomy_id
                WHERE x.taxonomy = 'product_country' AND x.term_id = {$country_term_id}
            )
            AND $wpdb->posts.post_status = 'publish'
            AND $wpdb->posts.post_type = 'product'
            ORDER BY $wpdb->posts.post_date DESC";
        }
        $query_results = $wpdb->get_results($querystr);
        if (!empty($query_results)) {
            $output = '';
            foreach ($query_results as $result) {
                $price      = get_post_meta($result->ID,'_regular_price');
                $price_sale = get_post_meta($result->ID,'_sale_price');
                $currency   = get_woocommerce_currency_symbol();
                $sku   = get_post_meta($result->ID,'_sku');
                $output .= '<li>';
                    $output .= '<a href="'.get_post_permalink($result->ID).'">';
                        $output .= '<div class="product-image">';
                            $output .= '<img src="'.esc_url(get_the_post_thumbnail_url($result->ID,'thumbnail')).'">';
                        $output .= '</div>';
                        $output .= '<div class="product-data">';
                            $output .= '<div class="shado-12 product-title"><strong>Title:</strong> '.$result->post_title.'</div>';
                            if (!empty($sku)) {
                                $output .= '<div class="shado-12 product-sku"><strong>SKU:</strong> '.$sku[0].'</div>';
                            }
                            if (!empty($price)) {
                                $output .= '<div class="shado-12 product-price"><strong>Price:</strong> ';
                                    $output .= $currency;
                                    $output .= '<span class="regular-price">'.$price[0].'</span>';
                                    if (!empty($price_sale)) {
                                        $output .= '<span class="sale-price">'.$price_sale[0].'</span>';
                                    }
                                $output .= '</div>';
                            }
                        $output .= '</div>';
                        $output .= '</a>';
                $output .= '</li>';
            }
            if (!empty($output)) {
                echo $output;
            }
        }
    }
    die();
}
add_action( 'wp_ajax_search_product', 'search_product' );
add_action( 'wp_ajax_nopriv_search_product', 'search_product' );

安全优化建议 #

原有代码直接拼接前端传入的$_POST['keyword']、$_POST['category']参数存在SQL注入风险，建议使用$wpdb->prepare进行参数绑定处理，进一步提升安全性。

内容的提问来源于stack exchange，提问作者Shado