How to display post from one custom post type in another custom post type with the same taxonomy?

Hi @Chris M:

Yes, what you are planning is one of the easier ways to accomplish a relationship between post where the user defines the relationship through a shared value.

There are several ways you could make this work but since WordPress doesn’t give you an easy single query way to do it easily I decided to subclass WP_Query named Place_Trip_Query for you so that you can accomplish this with one SQL query. Normally I like to avoid SQL queries but this is an area where I don’t think it can be helped.

The class uses the 'posts_where' and 'posts_join' hooks to let us modify the SQL query to associate the Place and Trip posts that have been tagged by the same Location term for a given place name (the query uses a “starts with” query so “Grand” will match “Grand Canyon”).

You can place this code in your theme’s functions.php file or in a PHP file of a plugin you might be writing:

class Place_Trip_Query extends WP_Query {
  var $place_name = false;
  function __construct($args=array()) {
    if (isset($args['place_name']))
      $this->place_name = $args['place_name'];
    parent::__construct($args);
  }
  function get_posts($args=array()) {
    add_filter('posts_where',array(__CLASS__,'posts_where'),10,2);
    add_filter('posts_join',array(__CLASS__,'posts_join'),10,2);
    parent::get_posts($args);
    remove_filter('posts_join',array(__CLASS__,'posts_join'));
    remove_filter('posts_where',array(__CLASS__,'posts_where'));
  }
  static function posts_join($join,$query) {
    global $wpdb;
    $join .= <<<SQL
INNER JOIN {$wpdb->term_relationships} 
  ON {$wpdb->term_relationships}.object_ID={$wpdb->posts}.ID
INNER JOIN {$wpdb->term_taxonomy} 
  ON {$wpdb->term_taxonomy}.term_taxonomy_id={$wpdb->term_relationships}.term_taxonomy_id 
 AND {$wpdb->term_taxonomy}.taxonomy='location'
INNER JOIN {$wpdb->term_relationships} place_relationships 
  ON place_relationships.term_taxonomy_id={$wpdb->term_relationships}.term_taxonomy_id
INNER JOIN {$wpdb->posts} place 
  ON place.ID=place_relationships.object_id
SQL;
    return $join;
  }
  static function posts_where($where,$query) {
    global $wpdb;
    $sql = <<<SQL
AND place.post_type="place"
AND place.ID<>{$wpdb->posts}.ID
AND place.post_title LIKE '%s'
SQL;
    // Search for something that starts with "{place_name}"...
    $where .= $wpdb->prepare($sql,$query->place_name.'%'); 
    return $where;
  }
}

You can use this class just like you might use the WP_Query with these args. Here’s a standalone file you could call test.php and drop in the root of your website to test the class and show you how it works:

<?php
include '../wp-load.php';
$query = new Place_Trip_Query(array(
  'post_type'=>'trip',
  'place_name'=>'Grand Canyon', // Here's where you put your place name
));
echo "<ul>";
foreach($query->posts as $trip) {
  echo "<li>{$trip->post_title}</li>";
}
echo "</ul>";

And for others reading this who would like to test this here’s some code to create the Places and Trips custom post types and the Location custom taxonomy. You can also place this in your theme’s functions.php file:

add_action('init','places_trips_init');
function places_trips_init() {
  register_post_type('place',
    array(
      'labels'          => make_post_type_labels('Place'),
      'public'          => true,
      'show_ui'         => true,
      'query_var'       => 'place',
      'rewrite'         => array('slug' => 'places'),
      'hierarchical'    => true,
      //'supports'        => array('title','editor','custom-fields'),
    )
  );
  register_post_type('trip',
    array(
      'labels'          => make_post_type_labels('Trip'),
      'public'          => true,
      'show_ui'         => true,
      'query_var'       => 'trip',
      'rewrite'         => array('slug' => 'trips'),
      'hierarchical'    => true,
      //'supports'        => array('title','editor','custom-fields'),
    )
  );
  register_taxonomy('location', array('place','trip'), array(
    'hierarchical'    => true,
    'labels'          => make_post_type_labels('Location'),
    'query_var'       => 'location',
    'rewrite'         => array('slug' => 'locations' ),
    )
  );
  global $wp_rewrite;
  $wp_rewrite->flush_rules(false);  // This should really be done in a plugin activation
}
function make_post_type_labels($singular,$plural=false,$args=array()) {
  if ($plural===false)
    $plural = $singular . 's';
  elseif ($plural===true)
    $plural = $singular;
  $defaults = array(
    'name'              =>_x($plural,'post type general name'),
    'singular_name'      =>_x($singular,'post type singular name'),
    'add_new'            =>_x('Add New',$singular),
    'add_new_item'      =>__("Add New $singular"),
    'edit_item'          =>__("Edit $singular"),
    'new_item'          =>__("New $singular"),
    'view_item'          =>__("View $singular"),
    'search_items'      =>__("Search $plural"),
    'not_found'          =>__("No $plural Found"),
    'not_found_in_trash'=>__("No $plural Found in Trash"),
    'parent_item_colon' =>'',
  );
  return wp_parse_args($args,$defaults);
}