What is the optimal way to achieve this query between two taxonomies in WordPress?

Question

I was trying to perform a query between two WordPress Taxonomies. The taxonomies I would be using are brand and product_cat.

The expected result is to display in a Dropdown all existing Brands as Fathers and all the existing product categories of each brand has as children*

What I tried at first was to iterate over all products asking for their categories and their brands, forming an associative array; but this ended on PHP Fatal error: Allowed memory size of 268435456 bytes exhausted as they are 10 000 products in the database. This was the code I used:

    <?php
$args = array(
    'posts_per_page'    => 25,
    'post_type'         => 'product',
);
$relations = array();
$loop = new WP_Query($args);
if ($loop->have_posts()) {
    while ($loop->have_posts()) {
        $loop->the_post();
        $productBrandTerm = get_field('brand');
        $productCategory = wp_get_post_terms(get_the_ID(), 'product_cat', $catArgs);
        if ($productBrandTerm) {
            if ($relations[$productBrandTerm->slug]) {
                array_push($relations[$productBrandTerm->slug], $productCategory[0]->name);
                $relations[$productBrandTerm->slug] = array_unique($relations[$productBrandTerm->slug]);
            } else {
                $relations[$productBrandTerm->slug] = array($productCategory[0]->name);
            }
        }
    }
} else {
    echo "____";
}

wp_reset_postdata();
            foreach ($relations as $key => $value) {
                echo '<p class="menu_marcas" id="' . $key . '">';
                echo  $key;
                echo '</p>';

                echo '<div class="menu_categorias">';
                foreach ($value as $k) {
                    echo '<a href="#">';
                    echo $k;
                    echo '</a>';
                }
                echo '</div><!-- menu_categorias -->    ';
            }
?>

When I say "existing brand" I mean the Categories that at least have one product.

I don't know if there is an optimal way to achieve this with WP_Query, or with raw SQL, in any case, which would it be the optimal way to do it?

EDIT: I forgot to mention this productBrandTerm is an Advanced Custom Field ACF field working as Product Taxonomy, which displays as an attribute of WooCommerce products. That is why I call it via get_field and ask for its slug.

Need to understand better what you are asking for. I'm a bit worried by the fact that $productBrandTerm appears to be a WP_Term (since your accessing the slug property), but then you retrieve the brand term using get_field(), meaning, you're storing the associated brand term in the post meta table and not in any of the taxonomy tables. Is the brand taxonomy not assigned to the product post type? Also, what is $catArgs? — Joel M
Please don't call array_unique inside of a loop. It doesn't have to be this way. You should be able to call it once, after the loop. Or you could check the value doesn't exist before adding it. — Joel M
Sorry, forgot to mention this productBrandTerm is an Advanced Custom Field ACF field working as Product Taxonomy, which displays as an attribute of WooCommerce products. That is why I call it via get_field and ask for its slug. Thanks for the array_unique tip. — Rayner Fernández
Yes, they are basic product categories (product_cat), they could have subcategories, but I'm not interested in getting child categories, I just need the categories without children. — Rayner Fernández

Rayner Fernández Rayner Fernández · Accepted Answer · 2019-11-28T16:41:12

I answer myself since I work it out somehow. I still think it's not the optimal way.

Custom-page.php:

$allBrands = get_terms(array(
    'taxonomy' => 'pa_marca',
    'hide_empty' => false,
));
foreach ($allBrands as $singleBrand) {
    echo '<span>' . $singleBrand->slug . '</span>';
    echo '<div>';
    $theResult = getCategoriesFilteringByBrand($singleBrand->slug);
    foreach ($theResult as $result) {
        echo '<a>';
        echo $result->slug;
        echo '</a>';
    } 
    echo '</div>';
}

functions.php:

function getCategoriesFilteringByBrand($brand){
global $wpdb;
$query = "
SELECT DISTINCT wp_terms.slug, wp_terms.name
FROM wp_term_relationships 
     LEFT JOIN wp_posts  
          ON wp_term_relationships.object_id = wp_posts.ID
     LEFT JOIN wp_term_taxonomy 
          ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
     LEFT JOIN wp_terms 
          ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id
WHERE taxonomy = 'product_cat' AND ID IN (
    SELECT ID
    FROM wp_term_relationships 
        LEFT JOIN wp_posts  
            ON wp_term_relationships.object_id = wp_posts.ID
        LEFT JOIN wp_term_taxonomy 
            ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
        LEFT JOIN wp_terms 
            ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id
    WHERE post_type = 'product' 
    AND taxonomy = 'pa_marca' 
    AND slug = '" . $brand . "'
)
";

$theCategoriesResult = $wpdb->get_results($query);
return $theCategoriesResult;
}

It still needs some improvements and it needs to make the query secure, but its something and I hope could be useful to others. The most important thing is that it works.

PS: get_terms without the hide_empty argument, it's not working properly, it's just returning a single brand result, which is wrong

What is the optimal way to achieve this query between two taxonomies in WordPress?

1 Answers