WordPress.org

Make WordPress Core

Opened 7 years ago

Closed 5 years ago

Last modified 5 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 6 years ago.
9124.2.diff (1.4 KB) - added by pampfelimetten 5 years ago.
Updated to latest trunk
9124.3.diff (1.3 KB) - added by wojtek.szkutnik 5 years ago.
9124.4.diff (1.6 KB) - added by aaroncampbell 5 years ago.
9124.5.diff (2.1 KB) - added by aaroncampbell 5 years ago.
9124.6.diff (2.2 KB) - added by aaroncampbell 5 years ago.
9124.7.diff (2.5 KB) - added by aaroncampbell 5 years ago.
9124.8.diff (2.1 KB) - added by aaroncampbell 5 years ago.
9124.9.diff (3.5 KB) - added by aaroncampbell 5 years ago.
9124.9.2.diff (3.6 KB) - added by aaroncampbell 5 years ago.

Download all attachments as: .zip

Change History (48)

comment:1 @FFEMTcJ7 years ago

  • Milestone changed from 2.8 to Future Release

comment:2 @dd326 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.

@dd326 years ago

comment:3 @dd326 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' "

comment:4 @dd326 years ago

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

comment:5 @ryan6 years ago

  • Milestone changed from 2.9 to 3.0

comment:6 @pampfelimetten6 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.

comment:7 @ryan6 years ago

  • Milestone changed from 2.9 to 3.0

Enhancements have to wait for 3.0.

comment:8 @Denis-de-Bernardy6 years ago

  • Cc Denis-de-Bernardy added

comment:9 @hakre6 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.

comment:10 @dd326 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.

comment:11 @dd326 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 }}}

comment:12 @Denis-de-Bernardy6 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)

@pampfelimetten5 years ago

Updated to latest trunk

comment:13 @wojtek.szkutnik5 years ago

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

Refreshed and tuned up

@wojtek.szkutnik5 years ago

comment:14 @aaroncampbell5 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') );

@aaroncampbell5 years ago

comment:15 @aaroncampbell5 years ago

  • Milestone changed from Future Release to 3.1

comment:16 @scribu5 years ago

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

comment:17 @aaroncampbell5 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
			),
		)
    ) );

@aaroncampbell5 years ago

comment:18 @aaroncampbell5 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.

comment:19 @scribu5 years ago

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

comment:20 @scribu5 years ago

s/patch/tag

@aaroncampbell5 years ago

comment:21 @aaroncampbell5 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.

comment:22 @scribu5 years ago

  • Keywords needs-refresh added; has-patch removed

Per IRC discussion:

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

@aaroncampbell5 years ago

comment:23 @aaroncampbell5 years ago

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

comment:24 @scribu5 years ago

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

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

comment:25 @scribu5 years ago

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

comment:26 @scribu5 years ago

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

comment:27 @aaroncampbell5 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.

@aaroncampbell5 years ago

comment:28 follow-up: @aaroncampbell5 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 )

comment:29 @scribu5 years ago

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

comment:30 @scribu5 years ago

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

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

comment:31 in reply to: ↑ 28 @hakre5 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.

comment:32 @aaroncampbell5 years ago

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

comment:33 @scribu5 years ago

I vote to concentrate on more important things.

comment:34 @aaroncampbell5 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.

@aaroncampbell5 years ago

comment:35 @aaroncampbell5 years ago

Fixed the inline docs to show all the new choices.

@aaroncampbell5 years ago

comment:36 @aaroncampbell5 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.

comment:37 @nacin5 years ago

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

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

comment:38 @scribu5 years ago

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

Note: See TracTickets for help on using tickets.