Menu Multiple Taxonomies

Question

I have a post_type "products" which has both "Category" and "Brand" taxonomies. Each product has exactly 1 Category, and exactly 1 Brand.

I would like to display a menu which first lists the Categories as the top-level menu, and then lists the Brands contained in each as a Sub-Menu.

Each Sub-Menu should only display the Brands which are associated with Products in that specific top-level Category.

The links in the Sub-Menu should then go to a page that only shows Products in the selected Brand and Category... but I think I can figure this part out with wp_query. Mostly I am confused on the Menu issue.

Any help is greatly appreciated!

Answer 1

First of all you need a way to get terms form a taxonomy (brand) related to posts associated to another taxonomy (category).

The fastest "core" way to do it is:

1. get a list of all categories
2. for each category get related products
3. loop products and retrieve related brands

This workflow require a nested loop, and also a + b + 1 queries, where a is the number of categories, and b is the number of products.

So if you have, e. g. 200 posts and 20 categories, this function runs 221 database queries and a nested loop over all categories and all posts.

Note that we can't use pagination, because we need to loop through all products, so if you have several thousands of products this function can destroy your site performance.

What we can do to improve this situation? 2 things:

• Using a more perfomant SQL query method to reduce database queries
• Using cache

For first part we can write a custom SQL query, that using proper JOIN and WHERE clause get the related terms from the 2 taxonomies without involving posts.

Look at this function:

function term_related_terms( $term, $term_tax, $target_tax, $onlyparent = FALSE ) {
  if ( ! taxonomy_exists( $term_tax ) || ! taxonomy_exists( $target_tax ) ) {
    return FALSE;
  }
  $term_tax_obj = $term_tax_id = FALSE;
  if ( is_numeric( $term ) ) {
    $term_tax_obj = get_term( $term, $term_tax );
  } elseif ( is_string( $term ) ) {
    $term_tax_obj = get_term_by( 'slug', $term, $term_tax );
  } elseif ( is_object( $term ) ) {
    $term_tax_obj = $term;
  }
  if ( is_object( $term_tax_obj ) && isset(  $term_tax_obj->term_taxonomy_id ) ) {
    $term_tax_id = $term_tax_obj->term_taxonomy_id;
  }
  if ( ! $term_tax_id ) return FALSE;
  global $wpdb;
  $query = $wpdb->prepare(
    "SELECT t.term_id, t.name, t.slug, tt.* FROM {$wpdb->terms} t
    INNER JOIN {$wpdb->term_taxonomy} tt ON tt.term_id = t.term_id
    INNER JOIN {$wpdb->term_relationships} tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
    INNER JOIN {$wpdb->term_relationships} tr2 ON tr2.object_id = tr.object_id
    INNER JOIN {$wpdb->term_taxonomy} tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
    WHERE tt.taxonomy = %s AND tt2.taxonomy = %s AND tr2.term_taxonomy_id = %s",
    $target_tax, $term_tax, $term_tax_id
  );
  if ( $onlyparent ) $query .= " AND tt.parent = 0";
  return $wpdb->get_results( $query .= " GROUP BY tt.term_taxonomy_id");
}

This function get 3 mandatory arguments:

• a term (can be a term id, slug or object)
• the taxonomy the term belong to
• another taxonomy name

And return all the terms from second taxonomy associated to posts having the given term from first taxonomy.

The optional $onlyparent argument, as guessable, if set to true makes function returns only the top-level terms from the second taxonomy.

That means that calling this function like so:

term_related_terms( $cadID, 'category', 'brand' );

we can get all the brands associated to products having the given $cadID, running only one db query.

And running this function for all categories, we can get all data we need, running n + 1 queries, where n is the number of categories and the additional query is to get all categories.

Using previous example (200 products and 20 categories) we need to run 21 queries instead of 221 and also we run a simple cycle instead of a nested one.

Improvement is remarkable, but 21 db queries are not a easy task, we need to cache results.

Let's write a function that on first run call the previous function, cache the result in a transient, and on subsequent calls returns the cached results:

function category_brand_menu_data() {
  // be sure following slugs match your setup
  $category_slug = 'category';
  $brand_slug = 'brand';
  $cache = get_transient( 'category_brand_menu_data' );  // try to get from cache
  if ( ! empty( $cache ) ) {
    return $cache;
  }
  // firts get the categories
  $cats = get_terms( $category_slug );
  $data = array();
  if ( ! empty( $cats ) ) {
    // get brands related to each category using term_related_terms()
    foreach ( $cats as $cat ) {
      $brands = term_related_terms( $cat, $category_slug, $brand_slug );
      if ( ! empty( $brands ) ) {
        $data[$cat->term_id] = array(
          'name' => $cat->name,
          'slug' => $cat->slug,
          'brands' => $brands
        );
      }
    }
    if ( ! empty( $brands ) ) {
      set_transient( 'category_brand_menu_data', $data ); // cache data for next call
    }
  }
  return $data;
}

Now that we implemented cache, performance greatly improve, however we need a mechanism to invalidate cache when something relevant happen.

To be more precise, something relevant is:

• a product is associated to a category
• a product is associated to a brand
• an association between product and category is removed
• an association between product and brand is removed
• a category term is deleted
• a brand term is deleted

First 4 events can be targeted using 'set_object_terms' action, that pass taxonomy to hooking functions as 4th argument.

The remaining 2 event can be targeted using 'delete_term' action, that pass taxonomy to hooking functions as 3rd argument.

So let's write a function that invalidate the cache (i.e. delete the transient) and add it to both hooks:

function category_brand_menu_clean( $a, $b, $tax_del, $tax_upd = NULL ) {
  // be sure following slugs match your setup
  $category_slug = 'category';
  $brand_slug = 'brand';
  // 'delete_term' pass taxonomy as 3rd argument, 'set_object_terms' as 4th
  $tax = ( current_filter() === 'delete_term' ) ? $tax_del : $tax_upd;
  if ( in_array( $tax, array( $category_slug, $brand_slug ) ) ) {
    delete_transient( 'category_brand_menu_data' );
    category_brand_menu_data();
  }
}

add_action( 'set_object_terms', 'category_brand_menu_clean', 20, 4 );
add_action( 'delete_term', 'category_brand_menu_clean', 20, 3 );

The function, after having deleted the cache, calls category_brand_menu_data() so cache is built again, and when function will be called from frontend, output will be taken from cache being well performant.

Now we have a performant function that retrieve terms associations, and we only need a function that use retrieved data to display the menu:

function category_brand_menu() {
  // be sure following slugs match your setup
  $category_slug = 'category';
  $brand_slug = 'brand';
  // get data (can be cached or not)
  $data = category_brand_menu_data();
  if ( empty( $data ) ) return;
  // set html format according to your needs
  $format = '<nav><ul class="menu">%s</ul></nav>';
  $parentformat = '<li><a href="%s">%s</a><ul class="submenu">%s</ul></li>';
  $itemformat = '<li><a href="%s">%s</a></li>';
  $menu = '';
  $tax_obj = get_taxonomy( $category_slug );
  $query_var = $tax_obj->query_var;
  // loop through data retrieved to build menu html string
  foreach ( $data as $catid => $cat_data ) {
    $cat_link = get_term_link( $catid, $category_slug );
    $items = '';
    foreach( $cat_data['brands'] as $brand ) {
      $t_link = get_term_link( $brand, $brand_slug );
      // add category query arg to brand link, so when link is clicked
      // will show archives having both terms: the category and the brand
      $link = add_query_arg( array( $query_var => $cat_data['slug'] ), $t_link );
      $items .= sprintf( $itemformat, $link, $brand->name );
    }
    $menu .= sprintf( $parentformat, $cat_link, $cat_data['name'], $items );
  }
  // print the menu
  printf( $format, $menu );
}

In your templates, where you want to output the menu, just use the brand new template tag like so:

<?php category_brand_menu(); ?>

and you're done.

---

Note that in all functions above I've set 2 variables:

 $category_slug = 'category';
 $brand_slug = 'brand';

because I'm not sure which are the correct slugs for your taxonomies, before test my code be sure to set proper slugs in all the functions.

Answer 2

I figured out how to pull all the Category / Brand combinations with a single MySQL query.

Then, I loop through the combinations to build an array of all Categories and their associated brands. Here's the query:

$brands_categories = $wpdb->get_results("
    SELECT te2.term_id AS category_id, te2.slug AS category_slug, te2.name AS category_name, t2.parent AS category_parent_id, te1.slug AS brand_slug, te1.name AS brand_name
    FROM wp_posts p

    LEFT JOIN wp_term_relationships r1 ON p.ID = r1.object_id
    CROSS JOIN wp_term_taxonomy t1 ON r1.term_taxonomy_id = t1.term_taxonomy_id AND t1.taxonomy = 'brands'
    LEFT JOIN wp_terms te1 ON t1.term_id = te1.term_id

    LEFT JOIN wp_term_relationships r2 ON p.ID = r2.object_id
    CROSS JOIN wp_term_taxonomy t2 ON r2.term_taxonomy_id = t2.term_taxonomy_id AND t2.taxonomy = 'product_cat'
    LEFT JOIN wp_terms te2 ON t2.term_id = te2.term_id

    WHERE p.post_status = 'publish' and p.post_type = 'product'

    GROUP BY te2.slug, te1.slug

    ORDER BY te2.slug ASC
", ARRAY_A);

So that basically pulls

category_id
category_slug
category_name
brand_slug
brand_name

for all combinations.

Next I loop through and build a clean array of categories, with Brands listed as sub-array.

foreach ($brands_categories as $brand_category) {
    if (!is_array($categories[$brand_category['category_id']])) {
        $categories[$brand_category['category_id']] = array(
            "slug" => $brand_category['category_slug'],
            "name" => $brand_category['category_name'],
            "parent" => $brand_category['category_parent_id'],
            "brands" => array()
        );
    }
    $categories[$brand_category['category_id']]['brands'][] = array(
        "slug" => $brand_category['brand_slug'],
        "name" => $brand_category['brand_name']
    );
}

Thanks for the help! This was definitely a lot more complicated than it should be. This would be a very straightforward problem in a custom php/mysql setup.