Reply To: Reverse Relationship Query

• John Huebner

• July 12, 2017 at 10:50 am

Well, If I needed I’d go directly to the database, only because it would mean a lot less queries to do and would be a lot faster. This is quick to illustrate the queries. On the other hand, I don’t think that prepare can handle the ‘IN’ clause, at least I have never found anything that gives any adequate solution.

global $wpdb;
// 1. get all the post ID's of doctors
$query = 'SELECT ID
          FROM '.$wpdb->posts.'
          WHERE post_type = "doctor"
            AND post_status = "publish"';
$ids = $wpdb->get_col($query);

// 2. get the relationship field for all of the above posts
$query = 'SELECT meta_value
          FROM '.$wpdb->postmeta.'
          WHERE post_id IN ("'.implode('", "', $ids).'")
            AND meta_key = "my_relationship"'; // or whatever the field name is
$list = $wpdb->get_col($query);

// 3. get list of unique locations
$location_ids = array()
for ($i=0; $<count($list); $i++) {
  $value = maybe_unserialize($list[$i]);
  if (is_array($value)) {
    for ($j=0; $j<count($value); $j++) {
      if (!in_array($value[$j], $location_ids)) {
        $location_ids = intval($value[$j]);
      }
    } // end for j
  } // end if array
} // end for i

// 4. query locations
$args = array(
  'post_type' => 'location',
  'posts_per_page' => -1,
  'post__in' => $location_ids
);
$locations = new WP_Query($args);