Solving a custom field query quandry

How to filter for multiple custom field key / values

As I mentioned at the end of my last post on the Power of Custom Fields, I recently started working on a prototype site that required rideshare board functionality. Storing the details lends itself well to custom fields, especially when More Fields is used for data capture as you can see from the inset image. However, I struggled to find a solution within WordPress to solve a display challenge: How to display a set of posts based on multiple custom field values? In my case, how to loop through rideshares base on event and ride-givers or ride-wanters.

I explored looping through each post in my rideshare category to match the rideshare event to an array value and then using get_post_meta within to see if results belonged to the ride-giver or ride-wanter. While this might have worked, it certainly wouldn’t be an elegant – or Idealien solution. Dan Butcher did provide a functional solution in the WordPress Support Forums for a similar challenge, but it is not a solution a novice / intermediate WordPress developer would be comfortable customizing, nor would it scale well. Barry @ clearskys.net informed me that,

Whilst the JOINS work, and will work quite well with a few meta-data criteria passed in, I’ve had problems (particularly on shared hosting) in the past when creating an SQL query to search based on a series of tags which used a similar method.

Introducing get_post_meta_multiple

This function I created, with sql optomization support from Barry @ clearskys.net:

• accepts a variable length array of custom field key/value pairs
• returns a list of post IDs which you can loop through
• Could be implemented into your site in one of 3 ways: Added to a functions.php file as a part of your theme, included in a plugin related to enhanced custom field functionality or report a (feature request) bug for a more evolved version of it be included in a future release of the core of WordPress.

The demonstration code below has been prepared for the most common scenario as part of a functions.php file in a theme.

The Demonstration

A series of tables with columns for number of seats available, departure / return dates plus contact / additional info. I have kept the code below intentionally short so the example doesn’t overwhelm. I have posted a more complete version on pastebin that has the markup to match the image above.

The function

function get_post_meta_multiple($metaDataList) {
	global $wpdb;
	
	$querystr = "SELECT p.* FROM $wpdb->posts AS p WHERE p.ID IN ( ";
	
	$querystr .= "SELECT post_id FROM $wpdb->postmeta WHERE ";
	$innerqry = array();
	foreach($metaDataList as $key => $value) {
		$innerqry[] = $wpdb->prepare( "(meta_key = %s AND meta_value = %s)", $key, $value );	
	}
	
	$querystr .= implode(" OR ", $innerqry);
	
	$querystr .= " GROUP BY post_id ";
	$querystr .= "HAVING count(*) = " . count($metaDataList);
	
	$querystr .= ") AND p.post_status = 'publish' ";
	
	$metaResults = $wpdb->get_results($querystr, OBJECT);					
	return $metaResults;
}

Example Usage

Place this inside your category.php or whatever template file you are using to display the results from.

 'Ottawa Blues Fest',
	'rideshare_type'	=> 'Driver'); 
					
	$resultList = get_post_meta_multiple($metaDataList);

	if ($resultList):
	//Loop through each result post to display appropriate contents
	foreach ($resultList as $post):
		setup_postdata($post);
		
		// Define your custom field key
		$key="rideshare_spaces";
		
		// Display value of custom field		
		echo get_post_meta($post->ID, $key, true);

   	endforeach;
	else : ?>
		

	

The future of the function?

Once I have finished the prototype site that started this concept, I will attempt to package up the pieces in a more manageable method to share for wider use. In the interim, feel free to use and share in whatever ways are most convenience for you. Much like WordPress as a whole, I think this idea can continue to evolve in some great ways that will make it easier for designers, developers and clients to build / use sites based on it. Please link back to this post as you do so that I can keep tabs on how this idea evolves.