sql - 提取与 WooCommerce 中的类别相关的品牌列表

Question

在 woocommerce 中，我的产品分为一个（或多个）类别（product_cat），并且具有品牌属性（pa_marque），并且仅属于一个品牌。

我正在尝试提取与类别相关的品牌列表...

通过对 term_relationships、term_taxonomy、terms 和 posts 表执行 SQL JOIN（这可能不是最佳解决方案），我得到如下信息：

{"post_id":"23391","term_taxonomy_id":"1217","taxonomy":"product_cat","taxonomy_parent":"0"},
{"post_id":"23391","term_taxonomy_id":"1219","taxonomy":"product_cat","taxonomy_parent":"1217"},
{"post_id":"23391","term_taxonomy_id":"1943","taxonomy":"pa_marque","taxonomy_parent":"0"}

（即产品 23391 有 2 个 product_cat 和 1 个 pa_marque ......但这就是我真的不知道如何继续......）

你有线索吗？

标签： sqlwordpresswoocommerce

Answer 1

我找到了包含特定产品品牌答案代码的 Get WooCommerce 产品类别，并且我对代码进行了一些小的更改，如下所示：

// Inspired by https://stackoverflow.com/a/61624358/1256770
function get_taxovalues_in_another_taxo_from_a_product_categories($taxonomy_tofind, $taxonomy_known, $cat_term_slug)
{
    global $wpdb;
    $results = $wpdb->get_results("
    SELECT DISTINCT
        t1.*
     FROM    {$wpdb->prefix}terms t1
        INNER JOIN {$wpdb->prefix}term_taxonomy tt1
            ON  t1.term_id = tt1.term_id 
        INNER JOIN {$wpdb->prefix}term_relationships tr1
            ON  tt1.term_taxonomy_id = tr1.term_taxonomy_id
        INNER JOIN {$wpdb->prefix}term_relationships tr2
            ON  tr1.object_id = tr2.object_id
        INNER JOIN {$wpdb->prefix}term_taxonomy tt2
            ON  tr2.term_taxonomy_id = tt2.term_taxonomy_id         
        INNER JOIN {$wpdb->prefix}terms t2
            ON  tt2.term_id = t2.term_id
    WHERE
        tt1.taxonomy = '$taxonomy_tofind'
        AND tt2.taxonomy = '$taxonomy_known'
        AND  t2.slug = '$cat_term_slug'
    ORDER BY t1.name
    ");

    $return = [];
    if (!empty($results)) {
        $term_names = [];
        foreach ($results as $result) {
            $term_link = get_term_link(get_term($result->term_id, $taxonomy_tofind), $taxonomy_tofind);
            $term_names[] = '<a class="' . $result->slug . '" href="' . $term_link . '">'
                . $result->name . '</a>';
        }
        $return = $term_names;
    }
    return $return;
}
// searching 'pa_marque' associated to product_cat "aiguilles-et-crochets"
$brands = get_taxovalues_in_another_taxo_from_a_product_categories('pa_marque', 'product_cat', 'aiguilles-et-crochets');
print(implode(" - ", $brands));

// searching 'pa_epaisseur-laine' associated to product_cat "laines"
$brands = get_taxovalues_in_another_taxo_from_a_product_categories('pa_epaisseur-laine', 'product_cat', 'laines');
print(implode(" - ", $brands));