WordPress.org

Make WordPress Core

Opened 7 years ago

Closed 6 years ago

Last modified 6 years ago

#14645 closed enhancement (fixed)

Support for multiple meta_key => meta_value pairs in WP_Query

Reported by: joehoyle Owned by: scribu
Milestone: 3.1 Priority: normal
Severity: normal Version:
Component: Query Keywords: has-patch
Focuses: Cc:

Description

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?

Attachments (3)

query.diff (2.6 KB) - added by joehoyle 7 years ago.
14645.002.diff (905 bytes) - added by aaroncampbell 6 years ago.
maybe_serialize.14645.diff (607 bytes) - added by scribu 6 years ago.

Download all attachments as: .zip

Change History (38)

#1 @scribu
7 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to wontfix
  • Status changed from new to closed

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/

#2 @joehoyle
7 years ago

  • Resolution wontfix deleted
  • Status changed from closed to reopened

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.

#3 @scribu
7 years ago

  • Milestone set to Awaiting Review

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

Prove it. :)

@joehoyle
7 years ago

#4 @joehoyle
7 years ago

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

#5 @scribu
7 years ago

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.

#6 @joehoyle
7 years ago

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

#7 @vteixeira
7 years ago

  • Cc vteixeira added

It seems like a normal enhancement for custom port types.

#8 @aaroncampbell
7 years ago

  • Cc aaroncampbell added

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).

#9 follow-up: @scribu
7 years ago

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

#10 @scribu
7 years ago

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

#11 @scribu
7 years ago

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' => '=',
  )
) ) );

#12 in reply to: ↑ 9 @azizur
7 years ago

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

#13 follow-up: @scribu
7 years ago

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

#14 @scribu
7 years ago

Note that line 2221 is this:

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

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

#15 in reply to: ↑ 13 @azizur
7 years ago

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.

#16 @scribu
7 years ago

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

#17 @scribu
7 years ago

  • Milestone changed from Awaiting Review to 3.1

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

#18 follow-up: @aaroncampbell
6 years ago

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.

#19 @aaroncampbell
6 years ago

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

#20 @aaroncampbell
6 years ago

  • Keywords has-patch added

#21 @aaroncampbell
6 years ago

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.

#22 in reply to: ↑ 18 ; follow-up: @scribu
6 years ago

  • Owner set to scribu
  • Status changed from reopened to accepted

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.

#23 in reply to: ↑ 22 @aaroncampbell
6 years ago

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.

#24 @scribu
6 years ago

  • Resolution set to fixed
  • Status changed from accepted to closed

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

#25 @scribu
6 years ago

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

#26 @scribu
6 years ago

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

#27 @scribu
6 years ago

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

#28 follow-up: @kawauso
6 years ago

[15766] causes notice and warning on post.php

#29 in reply to: ↑ 28 @scribu
6 years ago

Replying to kawauso:

[15766] causes notice and warning on post.php

[15768]

#30 follow-up: @scribu
6 years ago

  • Resolution fixed deleted
  • Status changed from closed to reopened

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().

#31 in reply to: ↑ 30 @Denis-de-Bernardy
6 years ago

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.

#32 @Denis-de-Bernardy
6 years ago

For each values, even.

#33 @scribu
6 years ago

  • Resolution set to fixed
  • Status changed from reopened to closed

Leaving as is.

#34 @scribu
6 years ago

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()

#35 @scribu
6 years ago

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

Note: See TracTickets for help on using tickets.