meta_compare needs to allow "in" as well

[Otto42]

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.

[dd32]

I like this idea, See patch.

[dd32]

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' "

[dd32]

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

[pampfelimetten]

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.

[ryan]

Enhancements have to wait for 3.0.

[hakre]

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.

[dd32]

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.

[dd32]

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

[Denis-de-Bernardy]

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]

Updated to latest trunk

[wojtek.szkutnik]

Refreshed and tuned up

[aaroncampbell]

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

[aaroncampbell]

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

[aaroncampbell]

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.

[scribu]

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

[scribu]

s/patch/tag

[aaroncampbell]

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

[scribu]

Per IRC discussion:

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

[aaroncampbell]

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

[scribu]

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

[scribu]

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

[scribu]

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

[aaroncampbell]

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.

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

[scribu]

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

[hakre]

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.

[aaroncampbell]

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

[scribu]

I vote to concentrate on more important things.

[aaroncampbell]

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.

[aaroncampbell]

Fixed the inline docs to show all the new choices.

[aaroncampbell]

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

[nacin]

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

[scribu]

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