Displaying Posts Related to Other Posts by a Taxonomy Term?

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!