Support for multiple meta_key => meta_value pairs in WP_Query

[joehoyle]

Currently you cant query for multiple meta_key -> meta_value pairs in WP_Query, it ouwl d good if I could say: get me posts with meta_key 'time' = 'today' AND meta_key 'event' = 'show'. Typically specifying meta_keys and values as arrays:

$queyr = new WP_Query( array( 'meta_key' = array( 'time', 'event' ), 'meta_value' = array( 'today', 'show' ), meta_compare = array( '=', '=' ) ) );

Alternatively it could use an associative array, but you would still need a second array to specify different meta_compare's.

I have implemented this via hooks in my function helper framwork - but thought it would be a solid addition to WP_Query. Thoughts?

[scribu]

The assumption is that custom field values will be mostly unique. Think street addresses. Thus, querying by multiple custom fields isn't a core requirement.

If you want to group posts by various criteria, you should use custom taxonomies instead. Sadly, querying multiple custom taxonomies isn't supported either... yet. See #9951.

PS: I too have a plugin for this: ​http://wordpress.org/extend/plugins/query-custom-fields/

[joehoyle]

Ahh yes, I know #9951 - I opened it, and wrote the patch :P

I agree that meta values will usually be unique - which is why I don't want to use a custom taxonomy - also, I can't do comparisons with terms - it's just a different concept. I have a meta_key "some_date" which is a timestamp, I want to get all dates after a certain timestamp. I am currently to that with meta_compare, but if I want to also restrict it by another meta_key I can't. I don't see why this would be simply considered a progression to the query class - my implementation is about 20 lines of code - if that were incorporated into query.php it would be much less.

The assumption is that custom field values will be mostly unique. Think street addresses. Thus, querying by multiple custom fields isn't a core requirement.

I don't understand why the above example means you wouldn't want to query by more than one meta_key. I.e. get all posts with a street address and a country (another meta_key). Especially with all the new added custom post type stuff.

Sorry if I wasn't supposed to re-open the ticket, I don't know if you will see the reply if it stays closed.

[scribu]

my implementation is about 20 lines of code - if that were incorporated into query.php it would be much less.

Prove it. :)

[joehoyle]

13 lines!

SQL Query generated for: 'meta_key=address&meta_value=London' Before patch:

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND wp_postmeta.meta_key = 'address' AND wp_postmeta.meta_value = 'London' GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Same as above (with patch):

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND wp_postmeta.meta_key = 'address' AND wp_postmeta.meta_value = 'London' GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

(they are the same :P)

SQL Query generated for: array( 'meta_key' => array( 'address', 'rating' ), 'meta_value' => array( 'London', '5'), 'meta_compare' => array( '=', '<' ) ) After patch:

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) JOIN wp_postmeta as wp_postmeta_1 ON (wp_posts.ID = wp_postmeta_1.post_id) WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND wp_postmeta.meta_key = 'address' AND wp_postmeta_1.meta_key = 'rating' AND wp_postmeta.meta_value = 'London' AND wp_postmeta_1.meta_value < '5' GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

[scribu]

You make a new JOIN for each pair; that doesn't scale well. In my plugin I do something like this:

WHERE CASE meta_key
  WHEN 'address' THEN meta_value = 'London'
  WHEN 'rating' THEN meta_value < 5
END
GROUP BY post_id HAVING COUNT(*) = 2

Anyway, before we continue, this patch should get a blessing (or a wontfix) from the core devs.

[joehoyle]

Ahh that's clever! If this get's blessed i'll take another stab

[vteixeira]

It seems like a normal enhancement for custom port types.

[aaroncampbell]

I have to agree that it would be nice to be able to query on multiple bits of meta data. For example, it would be nice to on one of my site (​http://shouldipourit.com) to find beers that are bitter and highly rated, so I'd like to compare

meta_key='_rating' && meta_value >= 4
meta_key='_ibu' && meta_value >= 50

Neither of those make sense as taxonomies since you need to be able to query them in ranges but still retain the exact value (I don't see the need to find a beer where the IBU is exactly 64, but if you're looking at a beer you want to know that it's IBU is 64)

I've run into this on other sites where we store course date, cost, and teacher as meta, etc. In my experience it's a fairly common need (although your mileage may vary).

[scribu]

(In [15581]) Use _wp_meta_sql() in WP_Query. See #14572. See #14645

[scribu]

Since we have _wp_meta_sql(), it really would be ashame to not be able to use it.

[scribu]

I'm thinking something like this:

query_posts( array(
  'meta_query' => array(
    'meta_key' => 'key1',
    'meta_value' => 'value1',
    'meta_compare' => '>=',
  ), array(
    'meta_key' => 'key2',
    'meta_value' => 'value2',
    'meta_compare' => '=',
  )
) ) );

[azizur]

Replying to scribu:

(In [15581]) Use _wp_meta_sql() in WP_Query. See #14572. See #14645

With define('WP_DEBUG', true); we get PHP Notice:

Notice: Uninitialized string offset: 1 in {path-to}\wp-includes\query.php on line 2221
Notice: Uninitialized string offset: 0 in {path-to}\wp-includes\query.php on line 2221

[scribu]

I have WP_DEBUG on, but I can't see the notice. Please provide steps to reproduce.

[scribu]

Note that line 2221 is this:

if ( !$q['suppress_filters'] ) {

which I don't think has anything to do with this ticket.

[azizur]

Replying to scribu:

I have WP_DEBUG on, but I can't see the notice. Please provide steps to reproduce.

I can confirm that at revision: 15599 this issue is no longer there.

[scribu]

(In [15624]) Allow plugins to manipulate the meta query args. See #14645

[scribu]

With [15624], you can manipulate the $meta_query var before the SQL is generated:

function manipulate_meta_query( $wp_query ) {
  $wp_query->meta_query[] = array(
    'meta_key' => 'foo',
    'meta_value' => 'bar',
    'meta_compare' => '>',
  );
}
add_action('pre_get_posts', 'manipulate_meta_query');

[aaroncampbell]

I can't seem to get this to work. It seems that at line 1392 of wp-include/query.php $this->meta_query is always empty (doesn't seem to get filled form $qv).

Also, wp_array_slice_assoc() uses isset instead of empty, and since meta_key and meta_value are always set you always get an empty set attached. It seems like the best solution would be to remove meta_key and meta_value from $keys in fill_query_vars(). I'll look at the other places it's used, but it might also be possible to use !empty instead of isset and this part would be fixed.

I fixed these, but it's currently all mixed up with my patch for #9124. I'll separate them and attach them to their respective tickets soon.

[aaroncampbell]

14645.002.diff fixes both issues I mentioned above.

Also, just to clarify, the proper usage is to pass an array of arrays as meta_query like this:

    query_posts( array(
		'meta_query'	=> array(
			array(
				'meta_key'		=> '_abv',
				'meta_compare'	=> '<',
				'meta_value'	=> 6
			),
			array(
				'meta_key'		=> '_ibu',
				'meta_compare'	=> '>',
				'meta_value'	=> 50
			),
		)
    ) );

[aaroncampbell]

Something else that needs to be addressed (and isn't addressed by my patch) is the ability to order by any of the meta keys used. The old way only allowed for one meta_key, and just added it to $allowed_keys and then added it to the switch statement.

[scribu]

Replying to aaroncampbell:

Also, wp_array_slice_assoc() uses isset instead of empty, and since meta_key and meta_value are always set you always get an empty set attached.

That really shouldn't affect the final output.

[aaroncampbell]

Replying to scribu:

It just seemed a little messy. I wouldn't have noticed except that I was debugging to find out why my query wasn't working. Still, it was really easy to fix by just removing meta_key and meta_value from the array in fill_query_vars().

The main thing that's still missing is the ability to order your query based on the meta keys. I'm guessing we need to adjust _wp_meta_sql() to return join, where, and order_by.

[scribu]

(In [15713]) Recognize meta_query as a query var. Props aaroncampbell. Fixes #14645

[scribu]

(In [15714]) Strip redundant 'meta_' from 'meta_query' keys. See #14645

[scribu]

(In [15729]) Revert part of [15713] to prevent notices. See #14645

[scribu]

(In [15766]) Get rid of redundant $this->meta_query. See #14645

[kawauso]

[15766] causes notice and warning on post.php

[scribu]

Replying to kawauso:

[15766] causes notice and warning on post.php

[15768]

[scribu]

I think we should apply maybe_serialize() to each meta value before passing it to the query, in order to be consistent with the behaviour of add_metadata().

[Denis-de-Bernardy]

Replying to scribu:

I think we should apply maybe_serialize() to each meta value before passing it to the query, in order to be consistent with the behaviour of add_metadata().

What if the meta value is stored using separate entries for each keys? It seems to me that if an author requests a meta value he means it. If he wants the meta value to match a key in a serialized value, he'll use a like statement instead.

[Denis-de-Bernardy]

For each values, even.

[scribu]

Leaving as is.

[scribu]

To clarify, here's the current syntax:

query_posts( array(
    'meta_key' => 'foo',
    'meta_value' => 'bar',
    'meta_compare' => '>=',
    'meta_type' => 'numeric'
) );

OR

query_posts( array(
  'meta_query' => array(
    array(
      'key' => 'foo',
      'value' => 123,
      'compare' => '>=',
      'type' => 'numeric'
    ),
    array(
      'key' => 'foo2',
      'value' => array( 'bar2', 'bar3' )
      'compare' => 'IN',
    ),
  )
) );

The arguments are documented in WP_Object_Query::get_meta_sql()

[scribu]

Update: The WP_Object_Query has been disolved. get_meta_sql() is now a standalone function in wp-includes/meta.php.