WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 3 years ago

#11482 closed enhancement (fixed)

Add LIKE to meta_compare

Reported by: joehoyle Owned by: ryan
Milestone: 3.1 Priority: normal
Severity: normal Version: 2.9
Component: Query Keywords: has-patch needs-testing
Focuses: Cc:

Description

Currently, you can not query for posts with a meta_key LIKE "%star". To easily allow things such as search posts by meta key I have created a patch which adds an SQL LIKE to meta_compare.

to use, query for:

meta_key=url&meta_value=http://www%&meta_compare=LIKE

Notice the % is included in the meta_value, so you can also do:

meta_value=%www%

To get anything with a www in the meta_key. Patch attached

Attachments (2)

meta_compare_like.diff (834 bytes) - added by joehoyle 4 years ago.
Add LIKE to meta_compare
11482.diff (888 bytes) - added by Denis-de-Bernardy 4 years ago.

Download all attachments as: .zip

Change History (15)

joehoyle4 years ago

Add LIKE to meta_compare

comment:1 dd324 years ago

  • Keywords meta query meta_compare like removed
  • Milestone changed from 2.9.1 to 3.0

Due to 2.9's impending release, Setting to 3.0 based on the fact this has a patch.

See also:[BR]
#9124: enhancement: meta_compare needs to allow "in" as well (new)[BR]
#10800: enhancement: Allow LIKE comparison for $qmeta_compare? (new)[BR]

#10800 is a duplicate of this, However this has a patch, so not closing.

comment:2 joehoyle4 years ago

From #10800 :

NOT LIKE and IN would be handy too

anyone in favour of adding in a "NOT LIKE" to the allowed params?

comment:3 Denis-de-Bernardy4 years ago

If we add LIKE, we might as well add NOT LIKE.

The like_escape() function is buggy, however, so I'm a bit suspicious of the whole idea. A plugin dev who actually needs this kind of stuff should probably be using the posts_join filter instead.

comment:4 follow-up: joehoyle4 years ago

If it used like_escape, there would need to be a different way of passing the "%"s instead of meta_value, as if it does like_escape( $meta_value ) the "%" will be escaped.

In terms of not doing it in favour of using posts_join, I feel is a bit of a workaround, hooking into posts_join, checking if the join has been added, then hooking into posts_where and inserting the LIKE statement seems like a lot of work compared to a proper implementation of using LIKE as a meta_compare. Especially as currently the posts_join and posts_where filters don't give you access to the WP_Query object it is a pain to check for query vars etc (global $wp_query not included).

comment:5 in reply to: ↑ 4 Denis-de-Bernardy4 years ago

Replying to joehoyle:

In terms of not doing it in favour of using posts_join, I feel is a bit of a workaround, hooking into posts_join, checking if the join has been added, then hooking into posts_where and inserting the LIKE statement seems like a lot of work compared to a proper implementation of using LIKE as a meta_compare. Especially as currently the posts_join and posts_where filters don't give you access to the WP_Query object it is a pain to check for query vars etc (global $wp_query not included).

Just in case you're not aware that you can put AND clauses in a JOIN statement, your example would be:

JOIN $wpdb->postmeta as my_plugin_meta
ON my_plugin_meta.post_id = $wpdb->posts.ID
AND my_plugin_meta.meta_key = 'url'
AND my_plugin_meta.meta_value LIKE '$my_sanitized_like_statement'

you can check the WP query in your plugin's function:

function posts_join($join) {
  global $wp_query;
  global $wp_the_query;
  # ignore if it's not the main loop
  if ( $wp_query !== $wp_the_query )
    return $join;

  // do stuff...
}

comment:6 follow-up: joehoyle4 years ago

Just in case you're not aware that you can put AND clauses in a JOIN statement, your example would be:

Ahh I was not aware you could do that, thanks!

you can check the WP query in your plugin's function:

Again, learned another thing here, didn't know the current query was stored in $wp_the_query (as I presume from the example it is)

Even so, not included LIKE in meta_compare on the grounds that there is currently no function to easily sanitize the string seems wrong. Surely WP_Query should be as powerful as possible (within reason, and not getting in the way of ease of use) as WordPress is being used for more abstract uses, especially when it comes to post_meta. Adding less than 20 characters to the query class to allow this, potentially very useful enhancement (escape_like aside, as it seems that needs attention regardless).

Just thinking out loud, but it just seems like a natural progression for the class.

comment:7 in reply to: ↑ 6 Denis-de-Bernardy4 years ago

Replying to joehoyle:

you're welcome.

Even so, not included LIKE in meta_compare on the grounds that there is currently no function to easily sanitize the string seems wrong.

sure. basically, what's needed is to make sure than LIKE and NOT LIKE properly handle comparisons that include \_ or \% in the url. these would catch literal _ and % in strings without being treated as wildcards. Since we're using single quotes, the \ might need to be double-escaped to work (i.e.

).

comment:8 westi4 years ago

like_escape should be fixed by #10041 which will go into early 3.0 unless there are any extra issues which are not detailed on that ticket.

Denis-de-Bernardy4 years ago

comment:9 Denis-de-Bernardy4 years ago

  • Keywords needs-testing added

I've uploaded a patch that also adds <> and NOT LIKE to the list.

comment:10 hakre4 years ago

It should be documented wether or not escaping does apply for the wpdb::prepare() function. Just in case.

comment:12 Denis-de-Bernardy4 years ago

  • Milestone changed from 3.0 to Future Release

punting pending #10041 getting fixed, else we'll be introducing potential for SQL injections.

comment:13 scribu3 years ago

  • Milestone changed from Future Release to 3.1
  • Resolution set to fixed
  • Status changed from new to closed

This was fixed in #9124

Note: See TracTickets for help on using tickets.