Query Post And Pages With Multiple Meta Values


This is an outdated post. You shouldn’t be using this function,
skip the hassle and learn about WP_Query.

Custom fields give WordPress immense flexibility, by using them correctly you can create very complex applications with the same functionality you could only find in custom CMS. Now for us developers, one of the “problems” we might face using multiple custom fields is that there is no easy ( built in ) solution to query posts or pages based on these multiple values. If you want to do so you have to create a custom ( somewhat complex ) sql query which is clearly not an easy task for most people, and most important it ain’t a flexible solution you could just include from one project to another.

So in the look out for a nice and solid solution I came across this post by Jamie Oastler a fellow designer who was kind enough to publish this amazing function: get_post_meta_multiple. I really encourage you to go ahead and read his post as he makes an excellent and clear explanation of how the function works and how to implement it in your project.

As good as it is, I found that I still needed a bit more flexibility to use it in my projects so I went ahead and made a few small improvements that others might find useful. These give you the option to make your query even more specific, you can:

  1. Select whether you want to retrieve only posts or pages.
  2. Pass a number ( or many numbers ) to select posts only from specific categories.
  3. Limit the number of posts you want to retrieve.
Get Post Meta Multiple Modified Function:Download

Usage Example

The usage of the function is really simple, as stated before Jamie made an excellent explanation on his blog, so no need to repeat all of that here again. The three new values I added can be assigned easily when calling the function as you can find in the example bellow:

/*
Function variables:
$aMetaDataList(array) example: array('meta_key' => 'meta_value','meta_key_2' => 'meta_value_2 )
$szType(string) = "post" or "page"
$szCategory(string) = example: '1' or '1,2,3'
$iLimit(integer)
*/
// USAGE EXAMPLE
$aMetaDataList = array(
       'custom_field_name_1' => 'custom_field_value_1',
       'custom_field_name_2' => 'custom_field_value_2',
       // etc…
);
$szCategory = '1,3'; // posts only from category 1 or 3
$iLimit = 6; // get only 6 posts
$my_posts = get_post_meta_multiple( $aMetaDataList, 'post', $szCategory, $iLimit );
// Once you have the posts just loops trough them as you would do usually
if ($my_posts):
foreach( $my_posts as $post ):
 	setup_postdata($post);
        the_title();
endforeach;
endif;

As you can see its very easy to use and it gives us developers huge flexibility to create amazing stuff. Props to Jamie for his great work. Hope you found this useful, if you have any suggestions please feel free to leave it on the comments, thank you!

The Function

function get_post_meta_multiple( $aMetaDataList = array(), $szType = 'post', $szCategory = NULL, $iLimit = NULL )
{
	global $wpdb;
	$szQuerystr = "SELECT p.* FROM $wpdb->posts AS p";
	if ( $szCategory != NULL AND is_string($szCategory) )
	{
		$szQuerystr .= " LEFT JOIN $wpdb->term_relationships ON (p.ID = $wpdb->term_relationships.object_id) ";
		$szQuerystr .= " LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) ";
	}
	$szQuerystr .=  "WHERE p.ID IN ( ";
	$szQuerystr .= "SELECT post_id FROM $wpdb->postmeta WHERE ";
	$aInnerqry = array();
	foreach($aMetaDataList as $szKey => $szValue)
	{
		$aInnerqry[] = $wpdb->prepare( "(meta_key = %s AND meta_value = %s)", $szKey, $szValue );
	}
	$szQuerystr .= implode(" OR ", $aInnerqry);
	$szQuerystr .= " GROUP BY post_id ";
	$szQuerystr .= "HAVING count(*) = " . count($aMetaDataList);
	$szQuerystr .= ") AND p.post_status = 'publish' AND p.post_type = '".$szType."'";
	if ( $szCategory != NULL AND is_string($szCategory) )
  	$szQuerystr .= " AND $wpdb->term_taxonomy.term_id IN(".$szCategory.")";
	$szQuerystr .= " ORDER BY p.post_title ASC";
	if ( $iLimit != NULL AND is_int($iLimit) )
	$szQuerystr .= " LIMIT ".$iLimit;
	$aMetaResults = $wpdb->get_results($szQuerystr, OBJECT);
	return $aMetaResults;
}

Reactions (17)

  1. JamieO

    Thanks for the props Matt – and running with the concept. I should point out that while this does make it possible to query based on multiple custom fields (good) it does have an inherrent performance risk (bad) by virtue of the number of joins that the query to the database would be doing. For most sites / uses that won’t be too much of a red flag, but definitely something to be aware of.

    With 3.0 having custom post types and meta boxes, a lot of the areas where I would previously have used custom fields will be going towards that and Verve meta boxes plugin (wordpress.org/extend/plugins/verve-meta-boxes/) until that too finds its’ way into core.

  2. Great post! thanks Jamie and Matt for sharing this, its just what i was looking for!! Maybe it would be a good idea to use it with WP Super Cache or any other similar plugin, just a thought tho. thanks again!

  3. @Jamie you are welcome, thanks for sharing the function and for the heads up, can’t wait to get my hands on 3.0 :)

    @Allan glad you found it useful, I agree about using a cache plugin and for more than this reason alone. tx for the comment!

  4. Hello,

    Thanks for solutions. I was looking for the same.
    But I want one more thing. I have a custom field ‘price’. I want to sort by price.

    That means.. meta_key is ‘price’ and meta_value = ‘$xxx’

    Is that possible ? How ?

  5. Rubira

    Can I have an ORDER BY meta key and meta value?

  6. Homem Robô

    Hi there, nice query, but is missing a white space after that ‘p’ if you choose to ignore “category” or “limit” stuff..

  7. Just wondering is this can handle comparisons or ranges, like if you have a key of price and you want to find all prices between 3 and 8.

    price >=3 and price <=8

    ?

  8. Pingback Wordpress get_post_meta_multiple, Pagination and Ordering. | Deniz Porsuk

  9. Doesn’t work with wp3? I tried to use with custom fields but the result array was everytime empty :(

  10. I also get an empty query . . . any other resources on this topic? I am trying to filter on multiple values on the same field. Any help would be greatly appreciated.

  11. answer to Dr. Pollo & the next :
    check your sql query with an echo on $szQuerystr.
    i m’ sure the request is not “good formatting” :)
    now you can add a space before the WHERE on line14
    sqfd

  12. I would really like it if someone could make a plugin that would allow you to display a form with radio buttons of several meta keys, and let the user select multiple keys to do searches. Such as, I want to find all the posts tagged ‘books’ and ‘bestsellers’ and ‘fiction’ where those are three custom fields.

  13. i need to do the same but for users ! any ideas?

  14. Is there any option to make a search using meta keys and meta key values?

    I have tryed multiple solutions but it doesn`t work in wp 3.1 or higher:(

    I am happy, when someboy makes a simple sample query to get pages/posts by using multiple meta keys and meta key values

    If i have only one metakey=metakeyvalue then it works…

  15. Muzahir Ahmad

    I am having the same issue as ‘Ivo’. There should be solution for searching multiple metakeys and metavalues instead of multiple queries.