2

I have a custom post type for musician's profiles, and a taxonomy for their location (categorised hierachically into Countries and then Cities). On the musician's individual profile pages I need to display a list of musicians in the same city (i.e. the child taxonomy). It'd also be good to display posts in the same country seperately (the parent taxonomy).

The problem seems to be retrieving the taxonomy dynamically, seperating the parent and child taxonomies and using these in a new loop.

I can display a list of the taxonomies, and strip out the links...

$terms = get_the_term_list( $post->ID, 'locations', '', ', ', '' ) ;
echo strip_tags($terms);

... which returns a result like this: New York, USA

And I can easily loop through posts by a fixed taxonomy value...

<?php $my_query = new WP_Query( array( 'locations' => 'new-york', 'showposts' => 10 ) ); ?>
<?php while ($my_query->have_posts()) : $my_query->the_post(); ?>
<li><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></li>
<?php endwhile; ?>

But I am having problems combining the two: as far as I can tell 'get_the_term_list' doesn't have the ability to return only one taxonomy, and there doesn't seem to be a taxonomy equivalent of 'get_the_category'.

Any help much appreciated!!!

2 Answers 2

9

Hi @George:

Depending on how many musicians you expect to have in your database you could either go the full API route (which is generally preferred, whenever it is vialable) if you are going to have a large number of them (thousands?) you could go with some custom SQL to pull exactly the records you need.

Using the WordPress API approach

The basics of the WordPress API approach are to first call wp_get_object_terms() with $post->ID of your profile musician and your 'locations' taxonomy which will return a list of "term" objects. (Unfortunately you need it to be only one location or this solution falls apart. But ignoring that...)

Next you grab the term ID of the first term and pass it plus your 'locations' taxonomy to get_objects_in_term() which returns an array of post IDs that have the terms associated for the desired taxonomy. (Note this will include ALL posts that have ANY term associated from your 'locations' taxonomy, which is why this approach is only good for a smaller number of records.)

Then you create a WP_Query object passing it a query that uses post__in to filter by the list of musician IDs you received above as well as by your 'locations' taxonomy and its specific city term captured for your profiled musician. Of course you must also remember to filter out the profiled musician using the 'post__not_in' argument.

Taken all together I've packaged that logic into a function called get_posts_related_by_taxonomy() whose source code you can see here (I left out a few details when I explained but hopefully they are reasonably obvious):

function get_posts_related_by_taxonomy($post_id,$taxonomy,$args=array()) {
  $query = new WP_Query();
  $terms = wp_get_object_terms($post_id,$taxonomy);
  if (count($terms)) {
    // Assumes only one term for per post in this taxonomy
    $post_ids = get_objects_in_term($terms[0]->term_id,$taxonomy);
    $post = get_post($post_id);
    $args = wp_parse_args($args,array(
      'post_type' => $post->post_type, // The assumes the post types match
      'post__in' => $post_ids,
      'post__not_in' => $post->ID,
      'taxonomy' => $taxonomy,
      'term' => $terms[0]->slug,
    ));
    $query = new WP_Query($args);
  }
  return $query;
}

Now you might use this function in your own loop like so:

// This assumes that your musician profile record is in the $post variable
$musicians = get_posts_related_by_taxonomy($post->ID,'locations');?>
<ul>
<?php while ($musicians->have_posts()): $musicians->the_post(); ?>
  <li><?php the_id(); ?> -- <?php the_title(); ?></li>
<?php endwhile; ?>
</ul>

Using the Direct SQL approach

As I already said, I prefer the WordPress API when it's viable. Unfortunately I think you'll run into performance or memory problems if you have even a reasonably large number of musicians and you use the approach shown above.

As a performant and memory efficient alternate you can use direct SQL to replace everything up to and including the call to get_objects_in_terms() in the prior example. The good news is the SQL is straightforward and unlikely to run into a future compatibility issues with new versions of WordPress since the SQL used only references primary and foreign keys and that won't change unless they completely revamp the taxonomy system, which I think is unlikely.

Using SQL I've created a different version of the get_posts_related_by_taxonomy() function which you will call it exactly the same as the first version is called. The SQL recognizes that the table wp_term_relationships simply needs to be joined together by their common term_taxonomy_id field which will relate all the musician records for a given location/(taxonomy) + city/(term) pair. Then we join it to the wp_term_taxonomy table so we can filter on the taxonomy, and we also filter by the profiled musician on one end of the relationship and we filter to make sure none of the related musicians are the profiled musician on the other end of the relationship. We finally make sure the related musicians post types are equal to our profiled musician's post type so you can avoid yet one more lookup, in this case we avoid calling get_post().

Our results from SQL are a nice simple array of post IDs which we can again use with the post__in argument of WP_Query but this time we don't need to include the post__not_in, taxonomy or term filter $args and we can tell post_type to use 'any' since our SQL query already handled all of those.

For the second version, here's the code you need:

function get_posts_related_by_taxonomy($post_id,$taxonomy,$args=array()) {
  global $wpdb;
  $sql =<<<SQL
SELECT
  related.object_id
FROM
  {$wpdb->term_relationships} post
  INNER JOIN {$wpdb->term_taxonomy} link ON post.term_taxonomy_id = link.term_taxonomy_id
  INNER JOIN {$wpdb->term_relationships} related ON post.term_taxonomy_id = related.term_taxonomy_id
WHERE 1=1
  AND link.taxonomy='%s'
  AND post.object_id=%d
  AND post.object_id<>related.object_id
  AND post.post_type==related.post_type
SQL;
  $post_ids = $wpdb->get_col($wpdb->prepare($sql,$taxonomy,$post_id));
  $args = wp_parse_args($args,array(
    'post_type' => 'any',
    'post__in' => $post_ids,
  ));
  return new WP_Query($args);
}

And again, you'll use this second version of get_posts_related_by_taxonomy() in exactly the same way that you used the first version.

Hope this helps!

6
  • thanks for your detailed post. It saved me tons of time and I learned a whole bunch in the process. Thanks. In my version of the functions, I added the WP3.1 way to query taxonomies: 'tax_query' => array( array( 'taxonomy' => $taxonomy, 'field' => 'slug', 'terms' => $terms[0]->slug //$branchterms->slug ) )
    – user3927
    Mar 14, 2011 at 5:35
  • @Juan - Glad I could help, and thanks for your additions. Mar 15, 2011 at 19:19
  • hi, im using you script and is working great, but the 'post__not_in' => $post->ID, is not working as i expect, because i'am seeng the title for the current post in that loop. what could be wrong? thank you
    – user5112
    May 4, 2011 at 18:05
  • @Joe - Best for your to ask another question with your specific details, including your specific code. If you provide a fully testable set of code in your question that we can drop into an existing WordPress site it will make it easier for someone to help you. Reference this question if you need to. May 4, 2011 at 20:40
  • @user5112 According to WP codex you can't combine "post__in" and "post__not_in" in the same query!
    – Dameer
    Oct 12, 2012 at 15:35
2

Check out wp_get_object_terms(), it's more general function and what get_the_category() uses internally.

8
  • @Rarst: wp_get_object_terms() doesn't give him what he is looking for. It returns the terms in a taxonomy of a list of posts but he needs the list of posts(musicians) that have the same term(location) as a given post(musician.) Oct 7, 2010 at 7:56
  • @MikeSchinkel Well, one step at a time. Request for get_the_category() analogue is part of the question. As I understand the question George needs to get terms of current musician first so that he can use them to query for related musicians.
    – Rarst
    Oct 7, 2010 at 8:10
  • @Rarst: You are correct that it is a first step. But may I say, it's much like telling someone who asked directions from England to Italy just that they should "Cross the English Channel"; it would get them started, but where would they go from there? Just sayin... :-) Oct 7, 2010 at 10:02
  • @Mike: Of course your answer is great, but we don't always have the time to give full answers, and sometimes people can find things out on their own once you give them the first hint.
    – Jan Fabry
    Oct 7, 2010 at 10:26
  • @MikeSchinkel I have very little clue about custom taxonomies (will study up on them in nearest future, but not so far), which kinda prevented me from writing perfect answer. :) So I answered part that I did know.
    – Rarst
    Oct 7, 2010 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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