按分类查询WooCommerce产品的SQL语句无返回结果，缺失什么配置？

问题核心原因 #

1. 当前SQL用INNER JOIN关联单位属性pa_j-m的分类表，只要产品没有设置该属性，就会直接被过滤，大概率是空结果的核心诱因
2. 现有代码没有预留产品分类的动态过滤条件，无法对接左侧分类树的筛选参数
3. 如果需要支持父分类筛选包含子分类下的产品，现有子查询逻辑没有覆盖层级关联场景

优化后的SQL代码 #

SELECT
    wp_posts.ID,
    CONCAT( '<a href="', wp_posts.guid, '">', wp_posts.post_title, '</a>' ) AS nazwa,
    product_taxonomy_product_cat_tbl.NAME AS kategoria,
    product_taxonomy_pa_j_m_tbl.NAME AS jednostka,
    product_quant.meta_value AS ilość,
    product_price.meta_value AS cena,
    product_weight.meta_value AS waga,
    product_sku.meta_value AS sku 
FROM
    wp_posts
    LEFT JOIN wp_postmeta product_quant ON product_quant.post_id = wp_posts.ID 
    AND product_quant.meta_key = "_stock"
    LEFT JOIN wp_postmeta product_price ON product_price.post_id = wp_posts.ID 
    AND product_price.meta_key = "_price"
    LEFT JOIN wp_postmeta product_weight ON product_weight.post_id = wp_posts.ID 
    AND product_weight.meta_key = "_weight"
    LEFT JOIN wp_postmeta product_sku ON product_sku.post_id = wp_posts.ID 
    AND product_sku.meta_key = "_sku"
    -- 产品分类关联，要求必须有分类可改回INNER JOIN
    LEFT JOIN (
    SELECT 
        product_taxonomy_product_cat_tbl_terms.name,
        rel_product_taxonomy_product_cat_tbl.object_id AS id,
        product_taxonomy_product_cat_tbl_termtaxonomy.term_id AS cat_id,
        product_taxonomy_product_cat_tbl_termtaxonomy.parent AS parent_cat_id
    FROM
        wp_terms AS product_taxonomy_product_cat_tbl_terms
        INNER JOIN wp_term_taxonomy AS product_taxonomy_product_cat_tbl_termtaxonomy ON product_taxonomy_product_cat_tbl_termtaxonomy.term_id = product_taxonomy_product_cat_tbl_terms.term_id 
        AND product_taxonomy_product_cat_tbl_termtaxonomy.taxonomy = 'product_cat'
        INNER JOIN wp_term_relationships AS rel_product_taxonomy_product_cat_tbl ON product_taxonomy_product_cat_tbl_termtaxonomy.term_taxonomy_id = rel_product_taxonomy_product_cat_tbl.term_taxonomy_id 
    ) AS product_taxonomy_product_cat_tbl ON product_taxonomy_product_cat_tbl.ID = wp_posts.id
    -- 单位属性关联改为LEFT JOIN，避免无属性产品被过滤
    LEFT JOIN (
    SELECT NAME
        ,
        object_id AS id 
    FROM
        wp_terms AS product_taxonomy_pa_j_m_tbl_terms
        INNER JOIN wp_term_taxonomy AS product_taxonomy_pa_j_m_tbl_termtaxonomy ON product_taxonomy_pa_j_m_tbl_termtaxonomy.term_id = product_taxonomy_pa_j_m_tbl_terms.term_id 
        AND product_taxonomy_pa_j_m_tbl_termtaxonomy.taxonomy = 'pa_j-m'
        INNER JOIN wp_term_relationships AS rel_product_taxonomy_pa_j_m_tbl ON product_taxonomy_pa_j_m_tbl_termtaxonomy.term_taxonomy_id = rel_product_taxonomy_pa_j_m_tbl.term_taxonomy_id 
    ) AS product_taxonomy_pa_j_m_tbl ON product_taxonomy_pa_j_m_tbl.ID = wp_posts.id 
WHERE
    1 = 1 
    AND wp_posts.post_type = 'product'
    AND wp_posts.post_status = 'publish' -- 仅查询已上架的有效产品
    -- 动态替换参数：把{$current_cat_id}替换为左侧分类树选中的分类ID即可实现筛选
    AND (product_taxonomy_product_cat_tbl.cat_id = {$current_cat_id} OR product_taxonomy_product_cat_tbl.parent_cat_id = {$current_cat_id})

分类筛选功能适配说明 #

• 如果分类层级超过2级，先查询选中分类的所有下级子分类ID，把筛选条件改为product_taxonomy_product_cat_tbl.cat_id IN (选中分类ID, 所有子分类ID)即可适配多级分类筛选
• 若业务要求产品必须绑定分类才展示，把产品分类关联的LEFT JOIN改回INNER JOIN即可
• 无单位属性展示需求时可直接删除pa_j-m的关联逻辑，进一步提升查询性能

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