在WooCommerce中通过SQL查询精确筛选多类别产品及其推荐方法

SELECT p.ID, p.post_title
FROM wp_posts p
INNER JOIN wp_term_relationships tr ON p.ID = tr.object_id
WHERE p.post_type = 'product'
AND p.post_status = 'publish'
AND tr.term_taxonomy_id IN (32, 25) -- 筛选出与这两个分类相关的产品
GROUP BY p.ID
HAVING COUNT(DISTINCT tr.term_taxonomy_id) = 2; -- 确保产品关联了这两个分类的所有指定项

<?php
global $wpdb;

$category_ids = array(32, 25); // 目标分类ID
$num_categories = count($category_ids); // 目标分类的数量

// 构建IN子句的占位符
$placeholders = implode(', ', array_fill(0, $num_categories, '%d'));

$query = $wpdb->prepare(
    "SELECT p.ID, p.post_title
    FROM {$wpdb->prefix}posts p
    INNER JOIN {$wpdb->prefix}term_relationships tr ON p.ID = tr.object_id
    WHERE p.post_type = 'product'
    AND p.post_status = 'publish'
    AND tr.term_taxonomy_id IN ({$placeholders})
    GROUP BY p.ID
    HAVING COUNT(DISTINCT tr.term_taxonomy_id) = %d",
    array_merge($category_ids, array($num_categories))
);

$sql_results = $wpdb->get_results($query, ARRAY_A);

if (!empty($sql_results)) {
    echo "<h2>同时属于指定分类的产品 (SQL方式):</h2>";
    foreach ($sql_results as $product) {
        echo "ID: " . $product['ID'] . ", Title: " . $product['post_title'] . "<br>";
    }
} else {
    echo "<p>未找到同时属于指定分类的产品。</p>";
}
?>

<?php
$category_ids = array(32, 25); // 目标分类ID

$args = array(
    'post_type'      => 'product',
    'post_status'    => 'publish',
    'posts_per_page' => -1, // 获取所有匹配的产品
    'tax_query'      => array(
        'relation' => 'AND', // 关键：指定分类之间的“AND”关系
        array(
            'taxonomy' => 'product_cat', // WooCommerce产品分类的内部名称
            'field'    => 'term_id',
            'terms'    => $category_ids[0], // 第一个分类ID
            'operator' => 'IN', // 可以是 'IN', 'NOT IN', 'AND' (对于单个term_id，'IN'即可)
        ),
        array(
            'taxonomy' => 'product_cat',
            'field'    => 'term_id',
            'terms'    => $category_ids[1], // 第二个分类ID
            'operator' => 'IN',
        ),
        // 如果有更多分类，继续添加数组项
    ),
);

// 动态生成tax_query数组，以支持任意数量的分类
$tax_query_items = array('relation' => 'AND');
foreach ($category_ids as $cat_id) {
    $tax_query_items[] = array(
        'taxonomy' => 'product_cat',
        'field'    => 'term_id',
        'terms'    => $cat_id,
        'operator' => 'IN',
    );
}
$args['tax_query'] = $tax_query_items;


$products_query = new WP_Query($args);

if ($products_query->have_posts()) {
    echo "<h2>同时属于指定分类的产品 (WP_Query方式):</h2>";
    while ($products_query->have_posts()) {
        $products_query->the_post();
        echo "ID: " . get_the_ID() . ", Title: " . get_the_title() . "<br>";
    }
    wp_reset_postdata(); // 恢复全局文章数据
} else {
    echo "<p>未找到同时属于指定分类的产品。</p>";
}
?>

<?php
$category_ids = array(32, 25); // 目标分类ID

$args = array(
    'status'   => 'publish',
    'limit'    => -1, // 获取所有匹配的产品
    'category' => $category_ids, // 默认是OR逻辑：产品属于32或25
);

$products = wc_get_products($args);

if (!empty($products)) {
    echo "<h2>属于指定分类的产品 (wc_get_products默认OR逻辑):</h2>";
    foreach ($products as $product) {
        echo "ID: " . $product->get_id() . ", Title: " . $product->get_name() . "<br>";
    }
} else {
    echo "<p>未找到属于指定分类的产品。</p>";
}
?>

<?php
$category_ids = array(32, 25); // 目标分类ID

// 动态生成tax_query数组
$tax_query_items = array('relation' => 'AND');
foreach ($category_ids as $cat_id) {
    $tax_query_items[] = array(
        'taxonomy' => 'product_cat',
        'field'    => 'term_id',
        'terms'    => $cat_id,
        'operator' => 'IN',
    );
}

$args = array(
    'status'   => 'publish',
    'limit'    => -1, // 获取所有匹配的产品
    'tax_query' => $tax_query_items, // 使用tax_query实现AND逻辑
);

$products = wc_get_products($args);

if (!empty($products)) {
    echo "<h2>同时属于指定分类的产品 (wc_get_products结合tax_query方式):</h2>";
    foreach ($products as $product) {
        echo "ID: " . $product->get_id() . ", Title: " . $product->get_name() . "<br>";
    }
} else {
    echo "<p>未找到同时属于指定分类的产品。</p>";
}
?>