Relationship query help

• 

  • sccr410

  • December 17, 2013 at 11:13 am

  We are building a site for a doctor office, just like the repeater example – but much more complex. We have a zip code search feature as well. The zip code search will take the input zip code and radius, find all offices that belong within there, then try to find doctors belonging to those offices.

  So I can’t do a “LIKE” on multiple location IDs in an array of values.

  How could I do this query?
• 

  

  • Elliot

  • December 17, 2013 at 2:14 pm

  Hi @sccr410

  Perhaps you shoudl reconsider using the repeater field for each office. Instead you should use a custom post type called ‘office’. You can then create a field group and attach it to ‘office’.

  That way, you will have far better SQL query ability such as radius!

  Thanks
  E
• 

  • sccr410

  • December 18, 2013 at 3:31 am

  I have custom post types for each doctor and each office. I have a field group assigned to doctors that lets you choose which locations they work at using a Relationship field.

  How would using the repeater with I assume a single Relationship field be any different than just allowing multiple relationships in terms of making the query better?
• 

  

  • Elliot

  • December 18, 2013 at 11:00 am

  Hi @sccr410

  Sorry about that, I read your above comment and though you were using a repeater field for each doctor!

  Please don’t use a repeater field for this. Continue to use the 2 post types related via a relationship field.

  Okay, so to achieve the original question, you will need to perform 2 seperate queries on the DB.

  The first query is to find the locations within the radius. There are a few tutorials on stack overflow which talk about SQL radius calculations which I have done once before on a complicated search directory and it did work correctly.

  Next, once you have all your locations, you can build up the meta_query args for each doctor.

  Taking straight from the docs, to find a doctor which is connected to location 2, you would have:

  
  <?php 
  
  $doctors = get_posts(array(
  	'post_type' => 'doctor',
  	'meta_query' => array(
  		array(
  			'key' => 'location',
  			'value' => '"' . 2 . '"', 
  			'compare' => 'LIKE'
  		)
  	)
  ));
  
   ?>
  

  So to find any doctors working in location2, 3, or 4, you would have:

  
  <?php 
  
  $doctors = get_posts(array(
  	'post_type' => 'doctor',
  	'meta_query' => array(
  		'relation' => 'OR',
  		array(
  			'key' => 'location',
  			'value' => '"' . 2 . '"', 
  			'compare' => 'LIKE'
  		),
  		array(
  			'key' => 'location',
  			'value' => '"' . 2 . '"', 
  			'compare' => 'LIKE'
  		),
  		array(
  			'key' => 'location',
  			'value' => '"' . 3 . '"', 
  			'compare' => 'LIKE'
  		)
  	)
  ));
  
   ?>
  

  To produce this meta_query args, you can simply loop over the results from DB query #1.

  Hope that helps.

  Thanks
  E
• 

  • sccr410

  • December 20, 2013 at 7:59 am

  While that makes perfect sense, it works if I am only doing a zipcode search. Unfortunately my search is actually using multiple custom fields beyond just zipcode (and thus which location IDs). Users can do a search for a Dentist in a zipcode, so I also have to match a custom field where a doctor has that specialty assigned to them as well. In doing so, I have to make “relation” an “AND” statement, your code requires it be an “OR” statement.

  Looks like I will have to do a completely custom SQL statement for this.
• 

  • sccr410

  • December 20, 2013 at 8:00 am

  This reply has been marked as private.
• 

  • sccr410

  • December 20, 2013 at 9:22 am

  So I ended up having to do a lot of filtering on the JOIN & WHERE statements instead of using just the $args array.