WordPress.org

Make WordPress Core

Opened 13 years ago

Closed 11 years ago

Last modified 11 years ago

#9124 closed enhancement (fixed)

meta_compare needs to allow "in" as well

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

Description

In wp-includes/query.php:

if ( ! empty($q['meta_value']) ) {
if ( ! isset($q['meta_compare']) || empty($q['meta_compare']) || ! in_array($q['meta_compare'], array('=', '!=', '>', '>=', '<', '<=')) )
$q['meta_compare'] = '=';

This bit of code basically allows one to specify a meta_compare, in order to allow the meta_value to be something other than equal only. Greater than, less than, etc. However, it could be nice if it would also allow the word "in" to be used as it is used in SQL, thus resulting in a statement like this:

query(...meta_compare=in&meta_value=1,2,3...)

... AND meta_value in (1,2,3) ...

The "in" case would need to be added to that array, and special handling would be needed to add the parenths around the values in that case.

Attachments (10)

9124.diff (1.3 KB) - added by dd32 12 years ago.
9124.2.diff (1.4 KB) - added by pampfelimetten 11 years ago.
Updated to latest trunk
9124.3.diff (1.3 KB) - added by wojtek.szkutnik 11 years ago.
9124.4.diff (1.6 KB) - added by aaroncampbell 11 years ago.
9124.5.diff (2.1 KB) - added by aaroncampbell 11 years ago.
9124.6.diff (2.2 KB) - added by aaroncampbell 11 years ago.
9124.7.diff (2.5 KB) - added by aaroncampbell 11 years ago.
9124.8.diff (2.1 KB) - added by aaroncampbell 11 years ago.
9124.9.diff (3.5 KB) - added by aaroncampbell 11 years ago.
9124.9.2.diff (3.6 KB) - added by aaroncampbell 11 years ago.

Download all attachments as: .zip

Change History (48)

#1 @FFEMTcJ
13 years ago

  • Milestone changed from 2.8 to Future Release

#2 @dd32
12 years ago

  • Component changed from General to Query
  • Keywords has-patch added
  • Milestone changed from Future Release to 2.9

I like this idea, See patch.

@dd32
12 years ago

#3 @dd32
12 years ago

  • Keywords needs-testing added

Testings:

get_posts( array( 'meta_key' => '_test', 'meta_value' => range(1,4), 'meta_compare' => 'in') );

string(158) " AND wp3_posts.post_type = 'post' AND (wp3_posts.post_status = 'publish') AND wp3_postmeta.meta_key = '_test' AND wp3_postmeta.meta_value IN('1','2','3','4') "

get_posts( array( 'meta_key' => '_test', 'meta_value' => 'test, test2, test3', 'meta_compare' => 'in') );

string(165) " AND wp3_posts.post_type = 'post' AND (wp3_posts.post_status = 'publish') AND wp3_postmeta.meta_key = '_test' AND wp3_postmeta.meta_value IN('test','test2','test3') "


get_posts( array( 'meta_key' => '_test', 'meta_value' => 'test, test2, test3') );

string(161) " AND wp3_posts.post_type = 'post' AND (wp3_posts.post_status = 'publish') AND wp3_postmeta.meta_key = '_test' AND wp3_postmeta.meta_value = 'test, test2, test3' "

#4 @dd32
12 years ago

that rtrim/str_repeat could also be replaced with implode(',', array_fill(0, count($q['meta_value']), '%s'))

#5 @ryan
12 years ago

  • Milestone changed from 2.9 to 3.0

#6 @pampfelimetten
12 years ago

  • Milestone changed from 3.0 to 2.9

Just tested the patch, works very fine for me.

query_posts (array('meta_key'=>"series_id",'meta_value'=>$new,'meta_compare'=>'in'));

works perfect.

It would be cool to see it 2.9, I can do more testing, if its needed.

#7 @ryan
12 years ago

  • Milestone changed from 2.9 to 3.0

Enhancements have to wait for 3.0.

#8 @Denis-de-Bernardy
12 years ago

  • Cc Denis-de-Bernardy added

#9 @hakre
12 years ago

just reviewed the patch, instead of

rtrim(str_repeat('%s,', count($q['meta_value'])), ',');

it might be less intensive using substr instead of rtrim:

substr(str_repeat(',%s', count($q['meta_value'])), 1);

just an idea.

#10 @dd32
12 years ago

it might be less intensive using substr instead of rtrim:

You're right. On my desktop on a million runs:

$s = implode(',', array_fill(0, count($c), '%s') );
4.15226
$s = rtrim(str_repeat('%s,', count($c)), ',');
2.81069
$s = substr(str_repeat(',%s', count($c)), 1);
2.60225

Commitor: feel free to change upon commit.

#11 @dd32
12 years ago

.. That again a bit prettier:

$s = implode(',', array_fill(0, count($c), '%s') );
 4.15226 seconds

$s = rtrim(str_repeat('%s,', count($c)), ',');
 2.81069 seconds

$s = substr(str_repeat(',%s', count($c)), 1);
 2.60225 seconds }}}

#12 @Denis-de-Bernardy
12 years ago

  • Keywords needs-patch added; has-patch needs-testing removed
  • Milestone changed from 3.0 to Future Release

preg_split() used like that isn't utf8-safe:

http://core.trac.wordpress.org/ticket/11669#comment:17

also, we may want to consider the in argument passed as foo=bar1,bar2,bar3 (without a space)

@pampfelimetten
11 years ago

Updated to latest trunk

#13 @wojtek.szkutnik
11 years ago

  • Cc wojtek.szkutnik@… added
  • Keywords has-patch needs-testing gsoc added; needs-patch removed

Refreshed and tuned up

#14 @aaroncampbell
11 years ago

I'd love to see this allow for IN as well as BETWEEN. The diff I'm attaching does just that. It also allows in or between to be passed in any case (between, BETWEEN, BeTwEeN, etc) and uses the WordPress standard preg_split('/[,\s]+/', ...); to make sure it works as expected.

Now it also works like this:

get_posts( array( 'meta_key' => '_test', 'meta_value' => '5,8', 'meta_compare' => 'between') );
get_posts( array( 'meta_key' => '_test', 'meta_value' => array(5,8), 'meta_compare' => 'BeTwEeN') );

#15 @aaroncampbell
11 years ago

  • Milestone changed from Future Release to 3.1

#16 @scribu
11 years ago

  • Keywords needs-refresh added; has-patch needs-testing removed

#17 @aaroncampbell
11 years ago

  • Keywords has-patch added; needs-refresh removed

I refreshed the patch. Please also see #14645. You can now use 'like', 'in', or 'between' as well as all the previously allowed operators.

I don't think this really needs the gsoc tag.

I tested it like this, and it works:

    query_posts( array(
        'post_type'		=> 'beer', // Show posts from the beer post type
        'paged'			=> $paged,
		'meta_query'	=> array(
			array(
				'meta_key'		=> '_abv',
				'meta_compare'	=> 'BeTwEeN',
				'meta_value'	=> array(6,8)
			),
			array(
				'meta_key'		=> '_ibu',
				'meta_compare'	=> '>',
				'meta_value'	=> 50
			),
		)
    ) );

#18 @aaroncampbell
11 years ago

I seem to have an issue still with this patch. I'll have to dig into it some more to see what the problem is, but when I set _ibu to 100, the record isn't included in the above query. However, when I set it to 99 it is.

#19 @scribu
11 years ago

The 'gsoc' patch is to indicate that a student (wojtek.szkutnik) worked on it.

#20 @scribu
11 years ago

s/patch/tag

#21 @aaroncampbell
11 years ago

Patch refreshed, and you can now you can pass in 'numeric' => true in order to force your meta_value to be treated as numeric.

#22 @scribu
11 years ago

  • Keywords needs-refresh added; has-patch removed

Per IRC discussion:

  • convert 'numeric' => true into 'type' => 'numeric'
  • use CAST() instead of 0+ hack

#23 @aaroncampbell
11 years ago

Scribu: refreshed as discussed. Now uses CAST(value AS ), and you pass in 'type'

#24 @scribu
11 years ago

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

(In [15724]) Enhance get_meta_sql. Props aaroncampbell. Fixes #9124

#25 @scribu
11 years ago

(In [15725]) Use elseif in get_meta_sql(). Props Viper007Bond. See #9124

#26 @scribu
11 years ago

(In [15726]) Don't revalidate $meta_type in get_meta_sql(). Props Viper007Bond. See #9124

#27 @aaroncampbell
11 years ago

  • Resolution fixed deleted
  • Status changed from closed to reopened

This latest patch further optimizes the if/elseif (props Viper007Bond) and then allows you to pass meta_type along with meta_value and meta_key.

#28 follow-up: @aaroncampbell
11 years ago

  • Keywords has-patch added; needs-refresh removed

I refreshed my latest patch to also fix some coding standards. According to @nacin we're supposed to be using ( ! $foo ) instead of ( !$foo )

#29 @scribu
11 years ago

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

#30 @scribu
11 years ago

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

(In [15730]) further meta query optimization. props aaroncampbell. fixes #9124

#31 in reply to: ↑ 28 @hakre
11 years ago

Replying to aaroncampbell:

I refreshed my latest patch to also fix some coding standards. According to @nacin we're supposed to be using ( ! $foo ) instead of ( !$foo )

Then this should be probably added to the coding standard page in codex: http://codex.wordpress.org/WordPress_Coding_Standards AFAIK it is not.

#32 @aaroncampbell
11 years ago

It's not specifically mentioned there but the examples on that page *are* using it.

#33 @scribu
11 years ago

I vote to concentrate on more important things.

#34 @aaroncampbell
11 years ago

  • Resolution fixed deleted
  • Status changed from closed to reopened

I already ran across a limitation with this. Luckily it's easily remedied. We added support for IN, LIKE, and BETWEEN. Do you see any reason not to also support NOT IN, NOT LIKE, and NOT BETWEEN? The latest patch (9124.9.diff) adds support for these.

#35 @aaroncampbell
11 years ago

Fixed the inline docs to show all the new choices.

#36 @aaroncampbell
11 years ago

Sorry, I didn't check the overwrite checkbox. Please use 9124.9.2.diff, as it has the adjustments to the inline docs.

#37 @nacin
11 years ago

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

(In [15755]) Add NOT LIKE/BETWEEN/IN. props AaronCampbell, fixes #9124.

#38 @scribu
11 years ago

(In [16410]) Minor get_meta_sql() cleanup. See #9124

Note: See TracTickets for help on using tickets.