Extract the list of brands associated with a category in WooCommerce

Question

In woocommerce, I have products which are classified in one (or more) categories (product_cat), and which have a brand attribute (pa_marque), and are only in one brand.

I'm trying to extract the list of brands associated with a category ...

By doing a SQL JOIN (this may not be the best solution) on the term_relationships, term_taxonomy, terms and posts tables I get something like this:

{"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"}

(i.e. product 23391 has 2 product_cat and 1 pa_marque ... but that's where I don't really know how to continue ...)

Would you have a clue?

Richnou Richnou · Accepted Answer · 2020-10-01T07:44:10

I found Get WooCommerce product categories that contain a specific product brand answer code and I got it working making some little changes to the code as follow:

// 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));

Extract the list of brands associated with a category in WooCommerce

1 Answers