Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Basically I have a custom post type of 'products' which has two taxonomies attached to it...the normal 'category' and a custom taxonomy called 'brands'.

I have a page which is 'brand' specific. On this page I'd like to list all the 'categories' that have a 'product' in them with a term of the 'brand' whos page I'm on attached.

Eg. say I'm on the "Nike" page. I want it to list all categories that have a 'product' in them with the 'brand' of "Nike" attached to them.

My initial thoughts are to use get_categories but theres now way to define a specific taxonomy or 'brand'?

$categories = get_categories('orderby=name&depth=1&hide_empty=0&child_of='.$cat);

Anyone done this before or knows a way to query the database directly to get the required results?

Any help is much appreicated, Thanks

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Hi @daveaspi:

What you want to do is common but not well handled in WordPress core. There are probably ways to do it without custom SQL but I don't think they would scale for a large number of posts. Below is a function I wrote called get_cross_referenced_terms() that will get what you want, complete with an example how to use it.

This following code can be placed in the root of your WordPress site in a test.php file to see it work. You can then copy the function get_cross_referenced_terms() into your theme's functions.php file or into a .php file of a plugin you might be working on:

<?php 

  include('wp-load.php');

  $nike = get_term_by('slug','nike','brand'); // This here just to illustrate

  $terms = get_cross_referenced_terms(array(
    'post_type'        => 'product',
    'related_taxonomy' => 'brand',
    'term_id'          => $nike->term_id,
  ));
  foreach($terms as $term) {
    echo "<p>{$term->name}</p>";
  }

function get_cross_referenced_terms($args) {
  global $wpdb;
  $args = wp_parse_args($args,array(
    'post_type'        => 'post',
    'taxonomy'         => 'category',
    'related_taxonomy' => 'post_tag',
    'term_id'          => 0,
  ));
  extract($args);
  $sql = <<<SQL
SELECT DISTINCT
  {$wpdb->terms}.*,
  COUNT(*) AS post_count
FROM
  {$wpdb->terms}
  INNER JOIN {$wpdb->term_taxonomy} ON {$wpdb->terms}.term_id={$wpdb->term_taxonomy}.term_id
  INNER JOIN {$wpdb->term_relationships} ON {$wpdb->term_taxonomy}.term_taxonomy_id={$wpdb->term_relationships}.term_taxonomy_id
  INNER JOIN {$wpdb->posts} ON {$wpdb->term_relationships}.object_id={$wpdb->posts}.ID
  INNER JOIN {$wpdb->term_relationships} related_relationship ON {$wpdb->posts}.ID=related_relationship.object_id
  INNER JOIN {$wpdb->term_taxonomy} related_term_taxonomy ON related_relationship.term_taxonomy_id=related_term_taxonomy.term_taxonomy_id
  INNER JOIN {$wpdb->terms} related_terms ON related_term_taxonomy.term_id=related_terms.term_id
WHERE 1=1
  AND (related_term_taxonomy.taxonomy<>{$wpdb->term_taxonomy}.taxonomy OR related_terms.term_id<>{$wpdb->terms}.term_id)
  AND {$wpdb->posts}.post_type='%s'
  AND {$wpdb->term_taxonomy}.taxonomy='%s'
  AND related_term_taxonomy.taxonomy='%s'
  AND related_terms.term_id=%d
GROUP BY
  {$wpdb->terms}.term_id
SQL;
  $sql = $wpdb->prepare($sql,$post_type,$taxonomy,$related_taxonomy,$term_id);
  $terms = $wpdb->get_results($sql);
  return $terms;
}
share|improve this answer
    
yeah that's the SQL query i was talking about :) –  Bainternet Mar 3 '11 at 10:59
    
Hi @MikeSchinkel, this is exactly what I needed! Works a treat. Thank you very much indeed! –  daveaspinall Mar 3 '11 at 11:22
    
@Bainternet thankyou very much for your input also :-) –  daveaspinall Mar 3 '11 at 11:23
    
One small question, any way to limit the 'depth' to 1? So I only get top level categories? :-) Thanks guys –  daveaspinall Mar 3 '11 at 11:29
    
@daveaspi: Add AND {$wpdb->term_taxonomy}.parent=0 to the where clause. –  MikeSchinkel Mar 3 '11 at 11:38

You can either code a custom sql query or you can query your post type for that "brand" and collect the categories and then display them, something like:

//get all your post of that type fo that spesific brand
$my_query = new WP_Query();
$my_query->query(array(
    'post_type' => 'products',
    'posts_per_page' => -1,
    'tax_query' => array(
        array(
            'taxonomy' => 'brand',
            'field' => 'slug',
            'terms' => $wp_query->query_vars['brand']
        )
    )
    ));
$my_cats = array(); 
if ($my_query->have_posts()){
//loop over all post and collect the categories in to an array
    while ($my_query->have_posts()){
        $my_query->the_post();
        foreach((get_the_category($post->ID)) as $category) {
            if (!in_array($category->cat_ID ,$my_cats)){
                $my_cats[] = $category->cat_ID;
            }
        } 
    }
}

and here you have the list of all categories ID in an array $my_cats. which you can get all the info you need from

share|improve this answer
1  
Yeah, much cleaner and great for a small site, but for a large number of posts it could be really very slow. –  MikeSchinkel Mar 3 '11 at 11:09
    
@MikeSchinkel - yep this would take a while on a site that each brand has a load of products, but i guess my "kong SQL fu" is not as strong as yours master! –  Bainternet Mar 3 '11 at 11:11
    
Ha! Not like I learned it overnight! It's been 20+ years of banging on SQL to get there. :) –  MikeSchinkel Mar 3 '11 at 11:13
    
20+ you say, so only 15 more for me to get there. –  Bainternet Mar 3 '11 at 11:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.