Reverse Relationship Query

• jaybuys

• July 11, 2017 at 8:23 am

Using the Querying Relationship Fields example the documentation shows how to get..
A – All locations for a doctor
B – All doctors for a location

In this example, is it possible to have code that would return “All locations that have doctors”? I don’t care who the doctor is. I just want a list of all locations that are selected for ANY doctor.

Thanks in advance.

• John Huebner

• July 12, 2017 at 10:14 am

No, not using a single WP_Query. This would not be possible even if the relationship field was on locations instead of doctors.

Using a standard WP_Query you would need to first get ALL doctors and loop through the posts and collect a list of all of the locations from them and then do another query to get the locations.

Even using standard WP custom fields this would be difficult due to what it is you’re looking to get.

There might be some ways to reduce the number of queries, or speed things up by using $wpdb https://codex.wordpress.org/Class_Reference/wpdb to directly query the database.

• jaybuys

• July 12, 2017 at 10:22 am

Thanks John. That’s kinda what I thought. I looked into doing it directly via the database but since the relationship field is serialized data it’s a bit tricky (though I’m sure not impossible).

For anyone stumbling upon this, I was able to solve it by looping through and building an array of the info I need.

I’m using “posts” and “authors” in place of “doctors” and “locations” but the idea is the same.

$posts = get_posts(array(
	'post_type' => 'post',
	'posts_per_page' => -1,
	'meta_key' => 'author',
	'meta_value' => ' ',
	'meta_compare' => '!='
));

$authors_list = array();
foreach ($posts as $post) {
	$related_authors = get_field('author', $post->ID);
	foreach ($related_authors as $author_id) {
		if (!in_array($author_id, $authors_list)) {
			$authors_list[] = $author_id;
		}
	}
}

function author_sort($a, $b) {
	return strcmp($a->post_title, $b->post_title);
}
usort($authors_list, "author_sort");

foreach ($authors_list as $author) {
	echo '<a href="' . get_the_permalink($author_id) . '">' . get_the_title($author->ID) . '</a><br />';
}

• 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);

• jaybuys

• July 14, 2017 at 2:06 am

Thanks again John. That’s a good approach. I’ll give that a shot!